Request Raised Report Along with Status | USERCONTEXT

[Manu269]

Hello Team,

I am looking for an analytics report for the logged in manager for all the request raised in last 3 months along with dashboard.

Attributes reqd:

Request I'd,

Request raised for

Request creation date

Application name

Requested access

Request type 

Request status

Task status

Task created date

Task updated date.

I already referred user context method but it seems not working at all.

[NM]

@Manu269 share your query with user context should work.

[rushikeshvartak]

SELECT Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.') + 1,
       Length(
              jbpmprocessinstanceid)) AS 'Request ID',
       u2.username                    AS 'Requested For',
       ar.requestdate                 AS 'Request Creation Date',
       endpointascsv                  AS 'Application Name',
       u3.username                    AS 'USERCONTEXT',
       CASE
         WHEN ar.requesttype != 11 THEN Ifnull((SELECT entitlement_value
                                                FROM   entitlement_values v
                                                WHERE  v.entitlement_valuekey =
                                                       ra.accesskey), 'Account')
         ELSE (SELECT role_name
               FROM   roles r
               WHERE  r.rolekey = ra.accesskey)
       END                            AS 'Requested Access',
       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',
       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                            AS 'Request Status',
       CASE
         WHEN t2.status = 1 THEN 'Open'
         WHEN t2.status = 2 THEN 'InProcess'
         WHEN t2.status = 3 THEN 'Completed'
         WHEN t2.status = 4 THEN 'Discontinued'
       END                            AS 'Task Status',
       ar.requestdate                 AS 'Task Created Date',
       t2.updatedate                  AS 'Task Updated Date'
FROM   ars_requests ar
       LEFT JOIN request_access ra
              ON ar.requestkey = ra.requestkey
       LEFT JOIN access_approvers aa
              ON ra.request_accesskey = aa.request_access_key
       LEFT JOIN users u2
              ON u2.userkey = ra.userkey
       LEFT JOIN arstasks t2
              ON t2.requestaccesskey = ra.request_accesskey
       LEFT JOIN users u3
              ON u3.userkey = ar.requestor
WHERE  t2.assignedfromrule IS NULL
ORDER  BY jbpmprocessinstanceid,
          jbpm_activity_name,
          u2.username;