Field Status change date - shown in a report

Field Status change date - shown in a report

Hi,


Is there a way that you can captured the date that a Field with in a template was changed and show this in a report? E.G in a template the support rep needs to add the current status of a work flow. I would like to than capture this in a report that shows the following

The Status is a drop down field that is changed through out the ticket life span, in the report I would like to be able to capture how many days the status has been active.....


I can capture everything in the above report except for the "Days in Status" I thought I may be able to pick it up some how from the history section where it outlines the change .....

The report has been produced and when I look at the query it is showing the below

SELECT wof.UDF_CHAR18 "Property ID",wof.UDF_CHAR3 "Property Address",wof.UDF_CHAR17 "Lease Renewal Status",wo.CREATEDTIME "Created Time",wo.LASTUPDATED "Last Updated",ti.FIRST_NAME "Support Rep",wof.UDF_CHAR35 "Project Sponsor" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID 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 LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID WHERE  ((((((cd.CATEGORYNAME = N'Project Work' COLLATE Latin1_General_CI_AS) AND (icd.NAME = N'Lease Renewal' COLLATE Latin1_General_CI_AS)) AND ((std.STATUSNAME = N'Onhold' COLLATE Latin1_General_CI_AS) OR (std.STATUSNAME = N'Open' COLLATE Latin1_General_CI_AS))) AND (((((((((wof.UDF_CHAR17 LIKE N'%Awaiting Direction on how to proceed%' COLLATE Latin1_General_CI_AS) OR (wof.UDF_CHAR17 LIKE N'%Seeking internal approvals%' COLLATE Latin1_General_CI_AS)) OR (wof.UDF_CHAR17 LIKE N'%Negotiating terms with agent%' COLLATE Latin1_General_CI_AS)) OR (wof.UDF_CHAR17 LIKE N'%PAR pending execution%' COLLATE Latin1_General_CI_AS)) OR (wof.UDF_CHAR17 LIKE N'%Awaiting lease form Lessor%' COLLATE Latin1_General_CI_AS)) OR (wof.UDF_CHAR17 LIKE N'%Legal review%' COLLATE Latin1_General_CI_AS)) OR (wof.UDF_CHAR17 LIKE N'%LWB Execution%' COLLATE Latin1_General_CI_AS)) OR (wof.UDF_CHAR17 LIKE N'%Lessor Execution%' COLLATE Latin1_General_CI_AS)) OR (wof.UDF_CHAR17 IS NULL))) AND (((wo.CREATEDTIME >= datetolong('2015-01-01') AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= 1467295199000) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))) AND (wo.DEPARTMENTID = 4))  AND wo.ISPARENT='1' 


My thinking is that it can not be done and I will need to produce the report and than within excel do some calculation work. 

Hope someone can help,


Regards

Cathy

              New to ADManager Plus?

                New to ADSelfService Plus?