Service Desk reports based on department
I created a custom field on my tickets that include department (department is selected from a dropdown predefined list).
We would like to create a report so we can see how many tickets were worked for a particular department in the last 7 days for example... I am not very good with SQL and after looking at this it seems it would require a custom SQL report to be created. Does anyone have any good pointers to accomplish something like this? We have multiple queues so I imagine that the queue id would like have an impact.
Answer Chosen by the Author
This query will show the number of tickets opened in the past seven days grouped by a custom field, in this case custom field 11:
SELECT CUSTOM_FIELD_VALUE10, COUNT(ID)
WHERE CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY CUSTOM_FIELD_VALUE10
You will notice that the column name for the custom field is one less than the field's name, that is because the database column names are zero based (custom field 1 is CUSTOM_FIELD_VALUE0, etc.) You will need to change that column name to match the custom field that holds your department. If you have the department field in multiple queues, then hopefully you are using the same custom field for all of them, otherwise things will be more complicated.
Also, you said "tickets were worked", did you want a report showing tickets where "Work" was entered in the past seven days, or opened, closed, modified, etc.?