Helpdesk open close stats

I have found a report that will provide me with each individual stats, but I need a report that will give me an overall report of my entire tech department, showing the number submitted and completed/resolved each day. It is a good weather check for status of the department. If anyone has something close or can help hone down the report, let me know.

Here is a report I have. Gives me counts per status and owner, which for me is a queue. i.e. assigned to =Bob and owner=IT Help Desk.

select count(*) as COUNT,workitem_status_lookup_value as STATUS,owned_by_worker_name as OWNER from HD_workitem_current_view as hd1 where hd1.owned_by_worker_name in ('IT Help Desk', 'Network Engineering', 'Systems Engineering', 'Asset Management', 'Tech Solutions') and (hd1.workitem_modified_on between '%Begin Date%' and '%End Date%' ) group by hd1.workitem_status_lookup_value ,owned_by_worker_name    
order by owned_by_worker_name 

The values of 'IT Help Desk', 'Network Engineering', etc are my queue names, so insert your own here. If you do not have owner=queue, you may have to adjust that to pull a different field for those values.

The '%Begin Date%' and '%End Date%' are parameters you set up in the report, that allow you to specify a date range when the report is run. They are Basic type and my default value is 01/01/2008 for Begin and 12/31/2008 for End. Select the checkbox to "Replace with SQL Parameter" when setting up the parameters, and "Prompt user for value when report is run".

Let me know if you need additional help.

FYI - the view named HD_workitem_current_view will be your new best friend for most of your Helpdesk reports, most likely. :)

Here is one that sums the different status types. You will need to modify it to work with your work item status's

SELECT HD_workitem_current_view.assigned_to_worker_name AS Name,
SUM(CASE WHEN workitem_status_lookup_value = 'Planned' THEN 1 ELSE 0 END) AS 'Planned' ,
SUM(CASE WHEN workitem_status_lookup_value = 'Open' THEN 1 ELSE 0 END) AS 'Open' ,
SUM(CASE WHEN workitem_status_lookup_value = 'Work in Progress' THEN 1 ELSE 0 END) AS 'Work In Progress',
SUM(CASE WHEN workitem_status_lookup_value = 'Hold' THEN 1 ELSE 0 END) AS 'Hold',
SUM(CASE WHEN workitem_status_lookup_value = 'Resolved' THEN 1 ELSE 0 END) AS 'Resolved',
SUM(CASE WHEN workitem_status_lookup_value = 'Closed' THEN 1 ELSE 0 END) AS 'Closed',
SUM(CASE WHEN assigned_to_worker_contact_status ='a' THEN 1 ELSE 0 END) AS 'Total Tickets'
FROM HD_worker_view LEFT OUTER JOIN
HD_workitem_current_view ON HD_worker_view.worker_nt_id = HD_workitem_current_view.assigned_to_worker_nt_id
where HD_worker_view.queue_name like '%Queue%' %type% and datediff(dd,workitem_modified_on, getdate()) <=%Number Days%
group by HD_workitem_current_view.assigned_to_worker_name
order by HD_workitem_current_view.assigned_to_worker_name

and now you will need to have global parameters for this to work
queue = drop down , fixed list , string with your queues entered liked this Account
HelpDesk Level 1;'HelpDesk Level 1'|
HelpDesk Level 2;'HelpDesk Level 2'|
Network Support;'Network Support'|
Server Support;’Server Support’|

number days = basic , value type is number

type = dropdown , fixed list ,value type none with value entered like this - Access; AND HD_workitem_current_view.workitem_type_lookup_value = 'Access' |
Change; and HD_workitem_current_view.workitem_type_lookup_value = 'Change' |
Incident;AND HD_workitem_current_view.workitem_type_lookup_value = 'Incident' |
Request; AND HD_workitem_current_view.workitem_type_lookup_value = 'Request'|
All;|

--------------------------
of course there is a thousand ways to make a report like this and I am always changing things to make it better. But this will get you started in to right direction.