Weekley/Monthly ticket report
Hi looking for basic report for my manger that show an overview of the help desk
Open/closed tickets category, owner count so basically just a simple report that show all open and closed tickets same for the monthly report. Is there any way to have this show in a pie chart or graph
thanks so much for the help in advance
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
grayematter
10 years ago
Here is one of our weekly reports that should help get you started. To get a chart or graph, open the data in your favorite spreadsheet, select the appropriate data, and graph away.
SELECT
USER.USER_NAME as Technician,
(select
count(a.ID)
from
HD_TICKET a
left join
HD_STATUS b ON a.HD_STATUS_ID = b.ID
where
b.STATE <> 'closed' and a.MODIFIED > DATE_SUB(NOW(), INTERVAL 14 DAY) and a.OWNER_ID = USER.ID) as 'Open_over_14_days',
(select
count(c.ID)
from
HD_TICKET c
left join
HD_STATUS d ON c.HD_STATUS_ID = d.ID
where
d.STATE = 'closed' and c.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 7 DAY) and c.OWNER_ID = USER.ID) as 'Closed_last_7_days',
(select
count(e.ID)
from
HD_TICKET e
left join
HD_STATUS f ON e.HD_STATUS_ID = f.ID
where
f.STATE = 'closed' and e.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 28 DAY) and e.OWNER_ID = USER.ID) as 'Closed_last_28_days',
(select
count(g.ID)
from
HD_TICKET g
left join
HD_STATUS h ON g.HD_STATUS_ID = h.ID
where
h.STATE = 'closed' and g.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 56 DAY) and g.OWNER_ID = USER.ID) as 'Closed_last_56_days',
(select
count(i.ID)
from
HD_TICKET i
left join
HD_STATUS j ON i.HD_STATUS_ID = j.ID
where
j.STATE <> 'closed' and i.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) and i.OWNER_ID = USER.ID) as 'Created_last_7_days',
(select
count(k.ID)
from
HD_TICKET k
left join
HD_STATUS l ON k.HD_STATUS_ID = l.ID
where
l.STATE <> 'closed' and k.OWNER_ID = USER.ID) as 'Total_open_tickets'
FROM
HD_TICKET
left join
USER ON HD_TICKET.OWNER_ID = USER.ID
WHERE
USER.USER_NAME IN ('tech1','tech2', 'tech3')
GROUP BY USER.USER_NAME
ORDER BY USER.USER_NAME;
Posted by:
markusg4373
10 years ago
Great looking exactly what i wanted but there is no data generated ?
Comments:
-
Make sure to adjust the user names in the where clause, or remove it. - grayematter 10 years ago
-
when i remove it keeps looping the report not generating sorry really appreciate the help i dont know sql at all - markusg4373 10 years ago
-
It will loop several times because of the subqueries. Try running the query with a third party query tool, like MySQL Workbench (http://dev.mysql.com/downloads/workbench/). The connection directions are here (http://www.kace.com/support/resources/kb/solutiondetail?sol=114992). - grayematter 10 years ago