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

Comments

Please log in to comment

Community Chosen Answer

1
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;

Answered 07/23/2014 by: grayematter
Fourth Degree Black Belt

Please log in to comment

Answers

0
Great looking exactly what i wanted but there is no data generated ?
Answered 07/23/2014 by: markusg4373
White Belt

  • Make sure to adjust the user names in the where clause, or remove it.
    • when i remove it keeps looping the report not generating sorry really appreciate the help i dont know sql at all
      • 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).
Please log in to comment
Answer this question or Comment on this question for clarity