report bad date
Hi all,
When I create custom report, using Tabular Report -> TimeSpent , and select columns (see screenshot1) I cannot have access to Description field.
Querry:
SELECT wo.WORKORDERID 'Request ID',wo.TITLE 'Subject',pd.PRIORITYNAME 'Priority',aau.FIRST_NAME 'Requester',rc.DESCRIPTION 'Description',rc.MM2COMPLETEREQUEST 'Time Spent' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE (wot.THD_WOID=wot.WORKORDERID)
I configure that I should change table from rc.DESCRIPTION to wo.DESCRIPTION then generally is ok. But in report date has changed and even if I using DATE_FORMAT(FROM_UNIXTIME(COLUMN_NAME/1000) ,'%d-%m-%Y %k:%i') 'Column Alias'.
SELECT
wo.WORKORDERID "ID zgloszenia",wo.TITLE "Tytulpd.PRIORITYNAME "Priorytet", aau.FIRST_NAME "Uzytkownik",wo.DESCRIPTION "Opis", DATE_FORMAT(FROM_UNIXTIME(rc.MM2COMPLETEREQUEST/1000) ,' %k:%i') 'Czas pracy', rctd.FIRST_NAME "Serwisant"
FROM
WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN RCTechnicianIDs rct ON rc.REQUESTCHARGEID=rct.REQUESTCHARGEID LEFT JOIN SDUser rcti ON rct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
WHERE
((wo.WORKORDERID = 13) AND (((((((( rctd.FIRST_NAME LIKE '%Administrator%') OR (rctd.FIRST_NAME LIKE '%HEATHER GRAHAM%')) OR (rctd.FIRST_NAME LIKE '%HOWARD STERN%')) OR (rctd.FIRST_NAME LIKE '%JENIFFER DOE%')) OR (rctd.FIRST_NAME LIKE '%JOHN ROBERTS%')) OR (rctd.FIRST_NAME LIKE '%ROBOTECHNICIAN%')) OR (rctd.FIRST_NAME LIKE '%SHAWN ADAMS%')) OR (rctd.FIRST_NAME LIKE '%NONE%'))) AND wot.THD_WOID=wot.WORKORDERID
Gennerally it works but Date is one hour bigger than shoud be. (screenshot2)
What I doing bad?
When I create custom report, using Tabular Report -> TimeSpent , and select columns (see screenshot1) I cannot have access to Description field.
Querry:
SELECT wo.WORKORDERID 'Request ID',wo.TITLE 'Subject',pd.PRIORITYNAME 'Priority',aau.FIRST_NAME 'Requester',rc.DESCRIPTION 'Description',rc.MM2COMPLETEREQUEST 'Time Spent' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE (wot.THD_WOID=wot.WORKORDERID)
I configure that I should change table from rc.DESCRIPTION to wo.DESCRIPTION then generally is ok. But in report date has changed and even if I using DATE_FORMAT(FROM_UNIXTIME(COLUMN_NAME/1000) ,'%d-%m-%Y %k:%i') 'Column Alias'.
SELECT
wo.WORKORDERID "ID zgloszenia",wo.TITLE "Tytulpd.PRIORITYNAME "Priorytet", aau.FIRST_NAME "Uzytkownik",wo.DESCRIPTION "Opis", DATE_FORMAT(FROM_UNIXTIME(rc.MM2COMPLETEREQUEST/1000) ,' %k:%i') 'Czas pracy', rctd.FIRST_NAME "Serwisant"
FROM
WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN RCTechnicianIDs rct ON rc.REQUESTCHARGEID=rct.REQUESTCHARGEID LEFT JOIN SDUser rcti ON rct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
WHERE
((wo.WORKORDERID = 13) AND (((((((( rctd.FIRST_NAME LIKE '%Administrator%') OR (rctd.FIRST_NAME LIKE '%HEATHER GRAHAM%')) OR (rctd.FIRST_NAME LIKE '%HOWARD STERN%')) OR (rctd.FIRST_NAME LIKE '%JENIFFER DOE%')) OR (rctd.FIRST_NAME LIKE '%JOHN ROBERTS%')) OR (rctd.FIRST_NAME LIKE '%ROBOTECHNICIAN%')) OR (rctd.FIRST_NAME LIKE '%SHAWN ADAMS%')) OR (rctd.FIRST_NAME LIKE '%NONE%'))) AND wot.THD_WOID=wot.WORKORDERID
Gennerally it works but Date is one hour bigger than shoud be. (screenshot2)
What I doing bad?