Custom report that show all the tickets that have been opened and closed in a 7 day period, but only for the certain offices, where the offices are represented by labels on the Machine and the Machine field in the ticket is populated.
To change the label look at the line that reads L.NAME='Florida'
Also, when using the K1000 appliance to run this report put the LABEL_NAME in the break on columns section.
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
CONCAT(TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
'd ', DATE_FORMAT(ADDTIME("2000-00-00 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
L.NAME LABEL_NAME
from HD_TICKET
JOIN MACHINE ON HD_TICKET.MACHINE_ID=MACHINE.ID
JOIN MACHINE_LABEL_JT ML ON MACHINE.ID=ML.MACHINE_ID
JOIN LABEL L ON L.ID=ML.LABEL_ID and L.NAME='Florida'
LEFT JOIN USER ON SUBMITTER_ID=USER.ID
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
WHERE HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY L.NAME,SUBMITTER_NAME,HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL