How to generate a report to analysis the RITM stage, approver and approval duration

[major li]

Hi community, 

I have request to generate a report to analysis the duration of each stage in RITM level, together with the approvers in each stages. I could find the metric_instance to calculate the duration of each stage, however, i could not find a table to calculate the approvers under specific stage, the sysapproval_approver table will only log the current stage.

Could you please help me? thank you. 

[Dr Atul G- LNG]

Hi @major li 

There isn’t an easy or straightforward way to achieve this. I’m not sure—it might be possible to get some help from the PA side if that works, otherwise you may need to write a full custom logic for it

[major li · Accepted solution]

Hi community,

here is the workaround.

in the [sysapproval_approver] table there is a wf_activity reference field liked to the workflow and stages. this field is not active by default but it is populated with the right value, even though I cannot understand why it is configured that way.

in the [metric_instance] table, there is definition field equals "Stage Duration Calculation" & value field equals wf_stage (type string).

So I created a database view to join [metric_instance], with [wf_activity] and [wf_stage] table to present the duration of each stage and exported [sysapproval_approver] table to the requester. She can use 3rd party analysis tool to analyze these 2 tables with the workflow stage as the key.

root cause why I don't join [sysapproval_approver] and [metric_instance]: sometimes there are multiple approvers involved in one stage, if I do so, it will be misleading because the audience will misunderstood that during one stage all approvers need to approve but in reality, only one of them will be enough. as a result the stage duration calcuation from the table will be wrong.