Editing a report after wizard creation makes dates static??
I created a report yesterday to list all requests for HR department groups that gathers information from the previous day. I've done similar reports with different time frame filters with test data. The one I created yesterday is to be the production report that HR requested. Since they just started using ServiceDesk Plus yesterday, there was no live data for the report to collect. I ran it today as a quick test and was surprised to find out there was still no data available to report although there are several closed requests in the tool.
I started creating the report using the wizard and selected "Yesterday" as the filter. I then had to use the query editor to make some changes that I couldn't do with the wizard. The query is below and it looks to me that the "Yesterday" filter is hard-coded dates.
I also checked the previously created report and it is showing the same results I had at the time I created it.
I would have thought that the filter would have created a function based on the current run-time date, not the date the report was made. Please help.
SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
sdu.EMPLOYEEID "Empl. ID",
longtodate(wo.CREATEDTIME) "Created Time",
longtodate(wo.COMPLETEDTIME) "Completed Time"
FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE(((((qd.QUEUENAME = N'HR Career Zone' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'Human Resources' COLLATE SQL_Latin1_General_CP1_CI_AS))
OR (qd.QUEUENAME = N'U.S. HRIS' COLLATE SQL_Latin1_General_CP1_CI_AS))
OR (qd.QUEUENAME = N'U.S. Payroll' COLLATE SQL_Latin1_General_CP1_CI_AS))
AND (((wo.COMPLETEDTIME >= 1325480400000) AND ((wo.COMPLETEDTIME != 0)
AND (wo.COMPLETEDTIME IS NOT NULL))) AND ((wo.COMPLETEDTIME <= 1325566799000)
AND (((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))
AND (wo.COMPLETEDTIME != -1))))) AND wo.ISPARENT=1 AND wo.IS_CATALOG_TEMPLATE=1
I started creating the report using the wizard and selected "Yesterday" as the filter. I then had to use the query editor to make some changes that I couldn't do with the wizard. The query is below and it looks to me that the "Yesterday" filter is hard-coded dates.
I also checked the previously created report and it is showing the same results I had at the time I created it.
I would have thought that the filter would have created a function based on the current run-time date, not the date the report was made. Please help.
SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
sdu.EMPLOYEEID "Empl. ID",
longtodate(wo.CREATEDTIME) "Created Time",
longtodate(wo.COMPLETEDTIME) "Completed Time"
FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE(((((qd.QUEUENAME = N'HR Career Zone' COLLATE SQL_Latin1_General_CP1_CI_AS)
OR (qd.QUEUENAME = N'Human Resources' COLLATE SQL_Latin1_General_CP1_CI_AS))
OR (qd.QUEUENAME = N'U.S. HRIS' COLLATE SQL_Latin1_General_CP1_CI_AS))
OR (qd.QUEUENAME = N'U.S. Payroll' COLLATE SQL_Latin1_General_CP1_CI_AS))
AND (((wo.COMPLETEDTIME >= 1325480400000) AND ((wo.COMPLETEDTIME != 0)
AND (wo.COMPLETEDTIME IS NOT NULL))) AND ((wo.COMPLETEDTIME <= 1325566799000)
AND (((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))
AND (wo.COMPLETEDTIME != -1))))) AND wo.ISPARENT=1 AND wo.IS_CATALOG_TEMPLATE=1