Reporting From IDM

[Former Member]

HI Experts,

We are on IDM 7.2,SP9

We have a requirement that we need to create report which can give below information-

1.   When Access Workflow was Started with User, Business Role Requested Information.

2.   Role Owner name with whom it is pending & for how long.

We can use BODS/ Business Objects for this reporting but need to create a customised SQL queries to fetch the information we need from IDM.

We need to find the IDM tables/views which contains below Information-

1. Business Role Requsted in Workflow.

2. User for whom access workflow is started.

3. Position Number of User.

4.. Time when Workflow was started.

5.. Role Owner name with whom it is pending.

4  Time for which it is pending with role owner.

We Tried to fetch information starored in IDM View -" MXWV_ALL_APPROVALS” but it is not giving all information  we need such as

role owner name & above information, it is giving us below Information as in screen shot-

SELECT * FROM mxmc_oper.mxmv_all_approvals where status='WAIT'

Please advise if it is better to use BW reporting or HTML reports visa to ASCII pass to create report for above requirement.

Regards

Aman

[jaisuryan · Accepted solution]

Hi Aman,

As I already said you are using custom attributes (I just refreshed the notes for attribute names), you won't find information in approval views and tables. You can get the current approver from idmv_vallink_ext2. Use the below SQL and you will get the current approver;

Select * from mxmc_oper.idmv_vallink_ext2 where mskey = <MSKEY of position> and mcattrname = 'APPROVERS';

If you want all the approvers for the workflow;

Select * from mxmc_oper.idmv_vallink_ext2 where mskey = <MSKEY of position> and mcattrname = 'MX_APPROVERS';

Let me know if it worked.

Kind regards,

Jai

[Former Member · Accepted solution]

Hi Jai,

We are able to find current approver & all approver with this SQL for a pending workflow.

Thanks a lot !

Regards

Aman Kashyap

[Former Member · Accepted solution]

Hi Aman,

for current approvers , use

select mcattrname, mcthismskeyvalue, mcothermskeyvalue from idmv_link_ext where mcattrname = 'MX_APPROVERS' or mcattrname = 'MX_ESCALATION_APPROVERS_1'

you can use different attributes required in select query.

Regards,

Pradeep

[Former Member]

Hi Aman,

You would need to check below table/views

Idmv_linkaudit_ext

Idmv_approvals_ext

Idmv_approver_ext

Mxi_approver

Mxi_approval

Idmv_value_ext

Moreover you would get details abt tables/views from workflow/notification tasks

Regards

Pradeep

[Former Member]

Hi Aman,

Are you using the standard approval task workflow?

Also when there is an approval pending, it should show up in the mxi_approver and mxi_approval table. You might need to perform multiple joins in order to get all the information that you need.

Regards,

Anu

[Former Member]

Hi All,

Also checked below views to find Role Approvers but there is no data in below

Approvers

idmv_approvers_basic

idvm_approvers_ext

Approvals

idmv_approvals_basic

idmv_approvals_ext

Regards

Aman

[jaisuryan]

Hi Aman,

If I remember correctly, IDM system in your project uses custom attributes for approval workflow and role references. Hence you will not get information from standard approvers or approvals view. Please check the SQLs used in notification tasks which will give you an idea of where to get the information. Since its been a while, I couldn't be more specific from top of my head.

Kind regards,

Jai