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.


Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

0
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)


Answered 02/16/2015 by: h2opolo25
Red Belt

  • 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.
    • 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.
Please log in to comment
Answer this question or Comment on this question for clarity