Workaround for Time-stamp/snapshot report creation
Sample Requirement: To Compare request count that was open yesterday with the request count that was open the day before yesterday.
Step 1: Add a new table in Analytics Plus using Import from Local DB.
In this step you will create a new table using the Local DB import option, which will execute the below custom query on the Servicedesk Plus Database and get the output stored in the new table.
You would also set the consecutive imports to be appended on the same table with 1 import per day.
Query: (Query is based on PGSQL. This will work on Servicedesk Plus running with PGSQL)
SELECT qd.QUEUENAME AS "Group", now() AS "Date from SDP", std.ISPENDING AS "Pending Status", Count(wo.WORKORDERID) AS "Request ID Count" FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (std.ISPENDING = true) AND wo.ISPARENT='1' GROUP BY 1 ,std.ispending
Output:
I have written the query to fetch only those which are having 'Pending Status'='true'. If you wish to have more specific request status you could use the 'Request status' column instead of Pending status.
Step 2: Create a Report to compare past dates:
The above query based import will append data on a daily basis. So within couple of days you should have data for last 2 days using which you could build a report like the below.
Create a new Report using the table created
Place 'Group' on X axis, Request Count (Actual Values -- Measure) on Y axis, Date From SDP (Full date) in Color and Request Count (Count) on Size.
Under the Filter section, Add the below two filters
Filter 1: Date from SDP (Relative) - Include Last 3 days
Filter 2: Date from SDP (Relative) - Exclude Today
The report should show you the open tickets status from Yesterday and the previous day.
New to ADSelfService Plus?
Related Articles
Steps to Embed a report across databases
This document captures the steps to embed a report from an ITOM Database into a dashboard under ITSM Database. This could be used when there is a requirement to view data/report from multiple databases in one single dashboard. Prerequisite: This ...Workaround to add Region Field in Servicedesk plus data
With the out of the box integration, we don't bring 'Region' details from the ServiceDesk Plus. However, you can follow the below steps to import the region details into a new table 1. Import using Local Database by connecting to the Servicedesk Plus ...How to find the breakdown time of each technician assignment in request?
Eg: If you have multiple request tickets, each ticket has been assigned to multi-technician ( the first assignment to tech A, then assign to tech B, then assign to tech C, etc). You want to know measure exact time of each assignment for each ...Upgrade to AnalyticsPlus 5400 Fails for Users with Missing zsoid Values
Issue Upgrading from builds between 5301 and 5360 to build 5400 fails because the zsoid field for some users is null. Error Trace java.sql.SQLException: This column [ZSOID] has some null values, hence it cannot be modified as not-null. Cause The ...Compare Analytics Plus vs Zoho Reports
Please find the attachment