Community and Support
            Knowledge Base Analytics Plus Importing Data Local Databases ServiceDesk Plus database

            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
            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 final report:


            The report should show you the open tickets status from Yesterday and the previous day.
            Updated: 25 Dec 2017 10:36 PM
            Help us to make this article better
            0 0