ServiceDesk Report Help
Hi all,
I'm trying to get a report set up that shows a list of all tickets by owner for the last week, including hours worked. I then want to show a total for each owner so something like:
OWNER
- Job 1 - Job Title - 2 hours
- Job 2 - Job Title - 4 hours
- Job 3 - Job Title - 5 mins
Total for ONWER: 6 hours 5 mins
Any ideas on how to achieve this? SQL is not my strong point and whilst I can get the results back, I can't figure out how to sum the hours worked for each group.
Cheers. Dave
Answers (1)
Here's an example report I created to sum up all of the vacation, personal and sick days for each person in my time tracking queue (which I'll be documenting completely and sharing as I have time). But this should at least let you get started with how to sum the hours and rename the columns to whatever you want (I'm doing both with one statement). Just for reference, I have three categories (Request::Vacation, Request::Personal Day, Sick Day) and the three SUM lines basically say "if the category is ____, then add up all of the days (kept in T.CUSTOM_FIELD_VALUE6) and call that column _____". Hopefully enough of an example here to let you get yours working.
_________________________________
*Title*
Time Tracking - Concise Listing
*Category*
Time Tracking (Custom)
*Description*
Concise listing of days off by employee.
*SQL Select Statement*
SELECT T.CUSTOM_FIELD_VALUE0 as EMPLOYEE,
SUM(CASE WHEN C.NAME like '%Vacation%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as VACATION_DAYS,
SUM(CASE WHEN C.NAME like '%Personal%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as PERSONAL_DAYS,
SUM(CASE WHEN C.NAME like '%Sick%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as SICK_DAYS,
T.CUSTOM_FIELD_VALUE1 as DEPARTMENT,
T.CUSTOM_FIELD_VALUE2 as LOCATION, U.FULL_NAME AS MANAGER
FROM HD_TICKET T
JOIN HD_CATEGORY C ON (C.ID = HD_CATEGORY_ID)
JOIN USER U on (U.EMAIL = T.CUSTOM_FIELD_VALUE7)
WHERE T.HD_QUEUE_ID = 4
GROUP BY EMPLOYEE
_________________________________
Hope that helps!
John
