Video Screencast Help

"Average Time to Close Incidents" report is empty

Created: 26 Jun 2012 | 1 comment
Raynald CHAFFAUX's picture

Hi,

On our Helpdesk solution based on the NS6 version, the "Average time to close Incidents" is empty, there is no datas.

Can you help us please ?

thanks a lot

raynald chaffaux

Comments 1 CommentJump to latest comment

Rob Moore's picture

Can you clone the report, then edit the SQL query and post the query here? my report is working, and below is the query it uses for the report:

 

Query 0:

 

 
SELECT
  hd1.[workitem_category_tree_value] as 'Category',
count(*) as 'Number of Incidents',
avg(datediff(%Time Unit%,hd1.[workitem_created_on],hd1.[workitem_modified_on])) as 'Avg Age(%Time Unit%)'
FROM dbo.HD_workitem_current_view hd1
WHERE UPPER(hd1.[workitem_action]) = UPPER('Closed')
--hd1.[workitem_status_lookup_ordinal] = '6'
AND UPPER(hd1.[workitem_priority_lookup_value]) LIKE UPPER('%Priority%')
AND UPPER(hd1.[workitem_category_tree_value]) LIKE UPPER('%Category%')
AND DATEDIFF(DD, hd1.[workitem_created_on], GETDATE()) <= %Number Days%
AND UPPER(hd1.[assigned_to_worker_name]) LIKE UPPER('%Worker%')
AND UPPER(hd1.[contact_company]) LIKE UPPER('%Company%')
AND UPPER(hd1.[contact_department]) LIKE UPPER('%Department%')
GROUP BY hd1.[workitem_category_tree_value]
ORDER BY 'Category'
 
 
Query 1
SELECT 
hd1.[workitem_number] as 'Id',
convert(varchar(50),hd1.[workitem_created_on], 120) AS 'Create Date',
convert(varchar(50),hd1.[workitem_modified_on], 120) AS 'Last Modified',
datediff(%Time Unit%,hd1.[workitem_created_on],hd1.[workitem_modified_on]) as 'Age(%Time Unit%)',
  isnull(dbo.fnLocalizeString('listitem.'+ hd1.[workitem_status_lookup_value], '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'), hd1.[workitem_status_lookup_value]) as 'Status',
datediff(%Time Unit%,hd1.[workitem_modified_on],getdate()) as 'Since Last Modified',
  isnull(dbo.fnLocalizeString('listitem.'+ hd1.[workitem_priority_lookup_value] , '77b56ff3-33e5-4bb0-bca6-de0e6dbb3cba', '%_culture%'), hd1.[workitem_priority_lookup_value] ) as 'Priority',
  hd1.[workitem_category_tree_value] as 'Category',
hd1.[workitem_title] as 'Title'
FROM dbo.HD_workitem_current_view hd1
WHERE 
UPPER(hd1.[workitem_action]) = UPPER('Closed')
--hd1.[workitem_status_lookup_ordinal] = '6'
AND UPPER(hd1.[workitem_priority_lookup_value]) LIKE UPPER('%Priority%')
AND UPPER(hd1.[workitem_category_tree_value]) LIKE UPPER('%Category%')
AND DATEDIFF(DD, hd1.[workitem_created_on], GETDATE()) <= %Number Days%
AND UPPER(hd1.[assigned_to_worker_name]) LIKE UPPER('%Worker%')
AND UPPER(hd1.[contact_company]) LIKE UPPER('%Company%')
AND UPPER(hd1.[contact_department]) LIKE UPPER('%Department%')
ORDER BY 'Id'