Advanced Report for Helpdesk

For all of those mangers who want to know…this report will help give an overview of incidents for your different queues. It will return information that will allow management to review individual users who work within a specific queue (global parameter) over a period of time (global date parameters). I created this previously to allow management to review incidents completed over a date range for review/bonus purposes.

Creating a report that will return the following:

• Queue name
• Worker Name
• Average number of incidents for a queue
• Count of the total for the queue
• Count of per worker incidents within that queue
• Difference between the queue total and the Worker total
• Percentage of completed incidents for that Worker relative to his/her other queue members

Global parameters:

• Begindate
  • Parameter Type: Date/Time
  • Settings: Type: Short Date
• Enddate
  • Parameter Type: Date/Time
  • o settings: Type: Short Date
• Team
  • Parameter Type: Dropdown
  • User Prompt: Team Name
  • List Type: Query Results
    • You could also do a fixed list if you wanted to simply list the queue names, however, this does not allow for growth
  • Value Type: String
  • Value: select distinct queue_name from dbo.HD_worker_view where queue_name IS NOT NULL
    • Here you can also please and AND queue_name LIKE ‘%’ if you have specific teams you want to build this for only.

SQL Statement:

SELECT         
 wrkr.queue_name AS 'Team',         
 wrkr.worker_name AS 'Worker Name',         
 abc.TeamAverage,      
 abc.TeamIncidents,         
 COUNT (*) AS 'Indiv. # of incidents',         
 COUNT (*) - abc.TeamAverage AS 'Difference',         
 (cast(COUNT (*) as money) / cast(abc.TeamIncidents as money)) * 100 as '% for individual'         

FROM dbo.HD_workitem_current_view hd1         
INNER JOIN dbo.HD_worker_view wrkr          
 ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]         
INNER JOIN         
(         
 SELECT         
  queue_name,         
  count(queue_name) AS 'number_in_queue'         
 FROM dbo.HD_worker_view wrkr         
 WHERE          
  [queue_name] IS NOT NULL         
  AND (wrkr.[queue_name] LIKE %team%)    
 GROUP BY [queue_name]         
) X         

 ON X.[queue_name] = wrkr.[queue_name]      
   
INNER JOIN         
(          
 SELECT         
  wrkr.queue_name as 'queue',         
  COUNT(wrkr.queue_name) / X.number_in_queue AS 'TeamAverage',         
  COUNT (*) AS 'TeamIncidents'         
 FROM dbo.HD_workitem_current_view hd1         
 INNER JOIN dbo.HD_worker_view wrkr          
  ON hd1.[workitem_assigned_to_worker_id] = wrkr.[worker_id]      
   
 INNER JOIN         
 (         
  SELECT         
   queue_name,         
   count(queue_name) AS 'number_in_queue'         
  FROM dbo.HD_worker_view wrkr         
  WHERE [queue_name] IS NOT NULL         
   AND (wrkr.[queue_name] LIKE %team%)    
GROUP BY [queue_name]         
 ) X         

  ON X.[queue_name] = wrkr.[queue_name]    
     
 WHERE  hd1.[workitem_created_on] between %begindate% AND %enddate%    
  AND hd1.[workitem_status_lookup_value] = 'Closed'         
  AND (wrkr.[queue_name] LIKE %team%)    

GROUP BY          
  wrkr.queue_name,          
  X.number_in_queue         
) abc         

 ON abc.queue  = wrkr.queue_name      
   
WHERE  hd1.[workitem_created_on] between %begindate% AND %enddate%         
 AND hd1.[workitem_status_lookup_value] = 'Closed'         
AND (wrkr.[queue_name] LIKE %team%)    

GROUP BY          
 wrkr.queue_name,          
 X.number_in_queue,         
 wrkr.worker_name,         
 abc.TeamAverage,         
 abc.TeamIncidents         

ORDER BY wrkr.queue_name, count(*) desc