Custom Reports for ServiceDesk Plus - Tracking Request Approvals

As a proactive ManageEngine reseller we were recently contacted by one of our customers who was interested in tracking the Approvals Requests made from requests in ManageEngine ServiceDesk Plus. They were interested in tracking the following information:

When the authoriser received the request for approval

When it was approved

How long it has taken the approver to action the request

What is the average time to approve

Now the Reports module in ManageEngine ServiceDesk Plus doesn't currently support getting this type of information by using the report wizards so it requires a custom report.

The main database table of interest is 'ApprovalDetails' as this records all the approvals activity in ManageEngine ServiceDesk Plus.

Running 'SELECT * FROM ApprovalDetails' in the Query Report Editor of the Reports module yields the following output:

Now to get a useful report we'll need to draw on some other tables. The data table 'ApprovalStageMapping' cross references if the 'Approval_stageid' is mapped to a current request in ManageEngine ServiceDesk Plus by means of the 'workorderid':

The final data table of 'ApprovalStage' will provide us with details of when the approval request was raised; we already have the time it was approved, 'Sent_date', in the 'ApprovalDetails' table.

The customer's database was MS SQL so we managed to create the following query:

SELECT asm.workorderid "Request ID", ad.email "Sent To",

-- used CASE statement to presnet meaningful text rather than a value

CASE

WHEN ad.stageid = 1 THEN 'Stage 1'

WHEN ad.stageid = 2 THEN 'Stage 2'

WHEN ad.stageid = 3 THEN 'Stage 3'

WHEN ad.stageid = 4 THEN 'Stage 4'

ELSE 'Stage 5 and above'

END as "Approval Stage",

-- these are date conversions for MS SQL, PostGreSQL and MySQL will differ

CONVERT(VARCHAR(20), dateadd(s,datediff(s, getutcdate(),getdate())+(aps.sent_date/1000),'1970-01-01 00:00:00'), 100) AS "Sent Date",

CONVERT(VARCHAR(20), dateadd(s,datediff(s, getutcdate(),getdate())+(ad.action_date/1000),'1970-01-01 00:00:00'), 100) AS "Actioned Date",

DATEDIFF(minute, dateadd(s,datediff(s, getutcdate(),getdate())+(aps.sent_date/1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(ad.action_date/1000),'1970-01-01 00:00:00')) as "Minutes taken to Respond",

-- used CASE statement to presnet meaningful text rather than a value

CASE

WHEN ad.statusid = 1 THEN 'Requested'

WHEN ad.statusid = 2 THEN 'Approved'

WHEN ad.statusid = 3 THEN 'Denied'

WHEN ad.statusid = 4 THEN 'To be sent'

ELSE 'Unspecified'

END as Status,

ad.comments "Approval Comments" FROM approvaldetails ad

-- used RIGHT JOIN so we only pick up Requests with approvals, this includes service requests

RIGHT JOIN approvalstagemapping asm ON asm.Approval_stageid=ad. Approval_stageid

LEFT JOIN approvalstage aps ON aps.approval_stageid=ad. approval_stageid