Archived and Active Request in a same report

Archived and Active Request in a same report

This Report is used to get the complete list of request both active and archived request in the same report. 


To make any changes to a query, refer to the KB article below.


SELECT wo.WORKORDERID "Request ID",
       wo.TITLE "Subject",
       aau.FIRST_NAME "Requester",
       dpt.DEPTNAME "Department",
       ti.FIRST_NAME "Technician",
       LONGTODATE(wo.CREATEDTIME) "Created Time",
       LONGTODATE(wo.COMPLETEDTIME) "Completed Time",
       std.STATUSNAME "Request Status" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE (wo.ISPARENT=1)
  AND wo.CREATEDTIME >= <from_thismonth>
  AND wo.CREATEDTIME <= <to_thismonth>
UNION
SELECT arcwo.WORKORDERID "Request ID",
       arcwo.TITLE "Subject",
       arcRequester.FIRST_NAME "Requester",
       arcwo.DEPTNAME "Department",
       arcTech.FIRST_NAME "Technician",
       LONGTODATE(arcwo.CREATEDTIME) "Created Time",
       LONGTODATE(arcwo.COMPLETEDTIME) "Completed Time",
       arcwo.STATUSNAME "Status" FROM Arc_WorkOrder arcwo
LEFT JOIN AaaUser arcRequester ON arcwo.REQUESTERID=arcRequester.USER_ID
LEFT JOIN AaaUser arcTech ON arcwo.OWNERID=arcTech.USER_ID
WHERE arcwo.CREATEDTIME >= <from_thismonth>
  AND arcwo.CREATEDTIME <= <to_thismonth> ORDER BY 1

Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report.