Hi team

 

Wondering if you could provide me a report which performs the following query, but makes the Time Spent column a number which is more friendly in Xls?  Currently, the format 00:00:00 does not calculate in pivot tables (because it sees it as text).  Changes to the cell format in XLS have not been successful.

 

SELECT mdd.MODENAME "Request Mode",aau.FIRST_NAME "Requester",dpt.DEPTNAME "Department",cd.CATEGORYNAME "Category",wo.TITLE "Subject",cri.FIRST_NAME "Created By",ti.FIRST_NAME "Technician",pd.PRIORITYNAME "Priority",wo.CREATEDTIME "Created Time",wo.COMPLETEDTIME "Completed Time",wo.TIMESPENTONREQ "Time Elapsed",wos.ISOVERDUE "Overdue Status",scf.GUDF_CHAR2 "Resolved at First Call",wo.WORKORDERID "Request ID" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID 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 SDUser crd ON wo.CREATEDBYID=crd.USERID LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN ServiceCatalog_Fields scf ON wo.WORKORDERID=scf.WORKORDERID WHERE  (((wo.CREATEDTIME >= datetolong('2012-12-01') AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= 1369231199000) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))))  AND wo.ISPARENT='1'

 

Many thanks