Service Desk report for number of tickets by catagory, By month and YTD
We use categories as an owner only option on our tickets. I need to generate a report that will show number of tickets opened in each category and sub-category. This report would need to be by month and YTD cumulative for each count. Thank you in advance for your assistance with this.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
h2opolo25
9 years ago
This should get you started...
SELECT HD_QUEUE.NAME as Queue, HD_CATEGORY.NAME as Category,
count(HD_TICKET.ID) as 'Total Closed Tickets'
FROM HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN HD_CATEGORY ON (HD_TICKET.HD_CATEGORY_ID=HD_CATEGORY.ID)
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE ((HD_STATUS.STATE like '%closed%')
AND (HD_TICKET.TIME_CLOSED > utc_timestamp() - interval 1 month)
)
GROUP BY HD_TICKET.HD_CATEGORY_ID DESC
order by count(HD_TICKET.ID)
Comments:
-
Thanks. does this report show only one month back or YTD? Also is this only showing closed tickerts? Just a newbie trying to get a feel for reports and what is entailed in this one. - comjam 9 years ago
-
This is a baseline report showing closed tickets in the past month.
I suggest you learn MySQL since it will be really useful for creating the type of reports you want.
For example if you look at the code you can change the following line...
AND (HD_TICKET.TIME_CLOSED > utc_timestamp() - interval 1 month) to AND HD_TICKET.TIMECLOSED > "2015-01-01 00:00:00" and this would give you all closed tickets from January 1st to today. - h2opolo25 9 years ago