/build/static/layout/Breadcrumb_cap_w.png

Reporting Create High Level Ticket Category Report

Ninja's,

I'm trying to create a report for the number of ticket sorted by category. The only problem is we have sub categories that the K1000 is also breaking on.

For instance in the report right now, I have:
Account Request::Service Account    5 tickets

Account Request::User Account 3 Tickets

Desktop::HardDrive 10

Desktop::Keyboard 7

What I'm trying to do is:
Account Request 8 tickets
Desktop  17 tickets

Basically I'm trying to get a total for each main category including the subcategories, but not have the subcategories in the report

0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: smalls 5 years ago
Senior Yellow Belt
2
I have built something similar for us and was not able to get all the formatting 100% with the SQL but export it to excel and do the final touches there.

I home it will put you on the right track.

Below is my SQL Report

SELECT
    COUNT(T.ID) AS '# Tickets',
    T.CUSTOM_FIELD_VALUE13 AS Branch,
    SUM(CAST(HOUR(TIMEDIFF(STOP, START)) + IF((MINUTE(TIMEDIFF(STOP, START)) = 0),
            0,
            (MINUTE(TIMEDIFF(STOP, START)) / 60)) + ADJUSTMENT_HOURS
        AS DECIMAL (10 , 2 ))) as Hours_Worked,
    SUM(CASE
        WHEN T.CUSTOM_FIELD_VALUE9 REGEXP '^[0-9]' THEN "1"
        ELSE ""
    END) AS AfterHoursCall,
    CASE
        WHEN HD_CATEGORY.NAME LIKE 'Support::%' THEN 'Support'
        WHEN HD_CATEGORY.NAME LIKE 'Network Operations::%' THEN 'Network Operations'
        WHEN HD_CATEGORY.NAME LIKE 'Projects::%' THEN 'Project'
        WHEN HD_CATEGORY.NAME LIKE 'Training' THEN 'Training'
        WHEN HD_CATEGORY.NAME LIKE 'User Maintenance' THEN 'User Maintenance'
        WHEN HD_CATEGORY.NAME LIKE 'Hardware Maintenance' THEN 'Hardware Maintenance'
        ELSE "UNKNOWN"
    END AS Category
    FROM (HD_TICKET T, HD_WORK W)
        JOIN
    HD_CATEGORY ON (HD_CATEGORY.ID = T.HD_CATEGORY_ID)
WHERE
    W.HD_TICKET_ID = T.ID
        and isnull(W.VOIDED_BY)
        and T.HD_STATUS_ID = 11
        AND TIMESTAMP(T.TIME_CLOSED) > DATE_SUB(NOW(), INTERVAL 1 MONTH)
        AND T.HD_QUEUE_ID = 3
GROUP BY BRANCH , CATEGORY
Posted by: chucksteel 5 years ago
Red Belt
1

Top Answer

This query will automatically use just the first part of the category so you don't have to hard code the top levels:

SELECT SUBSTRING_INDEX(HD_CATEGORY.NAME, "::", 1) AS TopCategory, COUNT(HD_TICKET.ID) FROM ORG1.HD_TICKETJOIN HD_CATEGORY ON HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_IDWHERE HD_TICKET.HD_QUEUE_ID = 2GROUP BY TopCategory
Be sure to change the HD_QUEUE_ID to match your queue.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ