Report on approvers for REQ and all RITMs attached

[Daniel Peel]

I'm looking for a way to pull a report that shows an REQ's approver and any RITM approvers related to it.  Columns I'd need are 

REQ, RITM, REQ Approver RITM Approver Requested for

I know req would be duplicated... any ideas?

I tried from sysapproval_approver and I can get one or the other but not both.

Thanks

[Daniel Peel]

This is the path I expected.  I figured since the approval records are linked to the REQ and RITM separately that a database view would be what I needed, but could not figure out the correct combination of fields to get me the data I needed.

Thanks for trying 🙂

[kevinray · Accepted solution]

Hi Daniel,

I just came across your post. This should work for you

- 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.

[Pelo Ntwampe]

Hi Kevin,

This mostly works. Thank you.

I'm having difficulty getting to the Variables. Even though I can dot-walk via the Request to the Questions, when I refresh the report after adding them, none of the columns is visible in the report. 

Any idea on how I can achieve adding the variables to the report?

Regards.

[Prashanth redd4]

Hi pelo,

I am stuck with the same problem. Did you find any way to bring in the variables.

Regards.

[Daniel Peel]

Thanks for taking the time to reply to this even though it was an old post.  You prompted me to go back and look at this one once more.  You lead me in the right direction, I only needed to make one change.  

#3: where clause is this: appr_sysapproval=ritm_sys_id || appr_sysapproval=ritm_request

I needed to see the approvers that approved the RITM which you included... but also who approved the REQ which held the RITM.  

Thanks you!