Create a report to identify if request was completed by assignee or someone else

[Sharma]

Hello,

We are trying to create a report where we can identify if a request was completed by the assignee or someone else(admin, other owner, etc.)

Ideally, it is mentioned in the comments of the request regarding which user completed the request, but we need data on a daily basis through a schedueld report.

The query we are currently using is not working as expected. Could you help us identify a way to get this report working. If we could get the tables and columns that can be used to see who was the assignee of the request and who actually completed it, that would help us crate this report.

Thanks

[rushikeshvartak]

Please share current query

[rushikeshvartak]

Use below query

SELECT DISTINCT Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.')
                                              + 1, Length(
                                jbpmprocessinstanceid))                    AS
                'REQUEST ID',
                CASE
                  WHEN ( ar.requesttype = 1
                          OR ar.requesttype = 3 ) THEN 'Grant Access'
                  WHEN ar.requesttype = 2 THEN 'Revoke Access'
                  WHEN ar.requesttype = 12 THEN 'Update Account'
                  WHEN ar.requesttype = 11 THEN 'Emergency Access Request'
                END                                                        AS
                'REQUEST TYPE',
                ar.requestdate                                             AS
                'REQUEST SUBMIT DATE',
                u2.username                                                AS
                'REQUESTED FOR',
                Concat(u2.firstname, ' ', u2.lastname)                     AS
                'REQUESTEE NAME',
                endpointascsv                                              AS
                'APPLICATION',
                aa.jbpm_activity_name                                      AS
                'APPROVAL TYPE',
                approver.username                                          AS
                'ASSIGNEE ID',
                Concat(approver.firstname, ' ', approver.lastname)         AS
                'ASSIGNEE NAME',
                approveredby.username                                      AS
                'APPROVED BY ID',
                Concat(approveredby.firstname, ' ', approveredby.lastname) AS
                'APPROVED BY NAME',
                CASE
                  WHEN approver.username != approveredby.username THEN
                  'Request Approved by someone else'
                  ELSE 'Request approved by Approver'
                END                                                        AS
                'Who Approved?',
                CASE
                  WHEN aa.status = 1 THEN 'Pending Approval'
                  WHEN aa.status = 2 THEN 'Approved'
                  WHEN aa.status = 3 THEN 'Rejected'
                  WHEN aa.status = 4 THEN 'Escalated'
                  WHEN aa.status = 6 THEN 'Discontinued'
                END
                'REQUEST STATUS'
FROM   ars_requests ar
       JOIN request_access ra
         ON ar.requestkey = ra.requestkey
       JOIN users u2
         ON ra.userkey = u2.userkey
       JOIN access_approvers aa
         ON ra.request_accesskey = aa.request_access_key
       JOIN users approveredby
         ON approveredby.userkey = aa.approveby
       JOIN users approver
         ON approver.userkey = aa.approverkey