Create a report showing Requested Items, including variables and associated Approval records

[Robbie]

Hi all, can anyone help with what sounds like a relatively easy ask, but is proving harder than it should be to implement.

Has anyone created a report that allows the ability to display Requested Items with associated Approval records including the display of Requested Item variables? 

Sounds easy right!? I thought the same until I tried numerous options without any success. Am I missing a trick? I/we can't be the first people to attempt this....

'Base line' - its possible to report on all Requested Items (sc_req_item) and associated variables. This is fine as a first step.

Its also very easy to crate a report by creating a database view linking the Approvals (sysapproval_approver) and Request Item table. This pretty much gets to the heart of what we want to report. This is getting closer to what we require.

However, when attempting to additionally display the variables on this second step via the database view, I'm coming a little unstuck. Can anyone provide any advice. The 'Variables' via the report slush bucket or 'Choose columns' option is not visible or 'dot walkable'. I've tried enforcing left joins, specially displaying the chosen fields/columns within the view. I've implemented additional joins from the sc_req_item table to the to the sc_item_option and sc_item_option_mtom tables - still no luck.

I've thought about other options such as creating a query business rule etc, but figured I'd see if anyone else has built this report before. 

Please also note, no PA (Performance Analytics) enabled on the instance. (PA should not be required for what seems like a simply ask)

Any help much appreciated.

Thanks,

R

[sachin_namjoshi]

- Create a new Database View

   - Name: Approvals_With_Detail

   - Label: Approvals With Detail

   - Plural: Approvals With Details

[Insert & Stay]

- Click "New" On View Tables related list which now appears at the bottom

   1) TABLE: sc_req_item, ORDER: 100; VARIABLE PREFIX: ritm; WHERE CLAUSE: [leave blank]

   2) TABLE: sc_cat_item, ORDER: 200; VARIABLE PREFIX: cat; WHERE CLAUSE: ritm_cat_item=cat_sys_id

   3) TABLE: sysapproval_approver, ORDER: 300; VARIABLE PREFIX: appr; WHERE CLAUSE: appr_sysapproval=ritm_sys_id

Use this view in a report and you should now have available to you all the fields from the request item, the catalog item, the approval, and fields from the Request (Using "Parent+" option on your fields) should be available to you.

Regards,

Sachin

[Kim5]

Thank you for this answer, @sachin_namjoshi. It was very helpful for me. I replicated this, but added a few things because realized I couldn't see my catalog item variable questions and answers. I think I figured it out because the output works and looks good, but I wanted to see if you were able to take a look at the attached and see what you think. Is it correct or are some of these unnecessary and it should be modified? Thank you for your help.