There is a built-in Incident KPI report that basically has all this data. However the help desk manager saw the multitude of parameters, the graph etc and didn't want that - just wanted something dead simple looking that gave him an overview with no extraneous info. So this query is the basis for that report. I need to clean up some unneeded joins etc but I leave them in there because often times I create new queries and reports from existing code and it helps my memory to have some of the other views already in there. Did I mention I have no real SQL training.....does it show? :) SRS is then used to do the sums and percentages. Screenshot below.
-Casey [cid:[email protected]] SELECT disp2.DisplayName + ' - ' + disp.DisplayName AS 'Category' ,COUNT (CASE WHEN inc.Status LIKE '%Active%' THEN 1 END) as [Active] ,COUNT (CASE WHEN inc.Status LIKE '%Resolved%' THEN 1 END) as [Resolved] ,COUNT (CASE WHEN inc.Status LIKE '%Closed%' THEN 1 END) as [Closed] ,COUNT (CASE WHEN inc.Status IS NOT NULL THEN 1 END) as [Total] FROM [DWDataMart].[dbo].[WorkItemDimvw] wi JOIN [DWDataMart].[dbo].[IncidentDimvw] inc on wi.EntityDimKey = inc.EntityDimKey JOIN dwdatamart.dbo.IncidentTierQueuesvw tqvw on inc.TierQueue_IncidentTierQueuesId = tqvw.IncidentTierQueuesId JOIn [DWDataMart].[dbo].[WorkItemAffectedUserFactvw] aff on inc.IncidentDimKey = aff.WorkItemDimKey JOIN [DWDataMart].[dbo].[UserDimvw] usr on aff.WorkItemAffectedUser_UserDimKey = usr.UserDimKey JOIN DWDataMart.dbo.[WorkItemAssignedToUserFactvw] assusr on inc.IncidentDimKey = assusr.WorkItemDimKey JOIN DWDataMart.dbo.UserDimvw usr2 on assusr.WorkItemAssignedToUser_UserDimKey = usr2.UserDimKey JOIN [DWDataMart].[dbo].[IncidentClassificationvw] incclass on inc.Classification = incclass.ID LEFT JOIN [DWDatamart].[dbo].[IncidentClassificationvw] incclass2 on incclass.ParentID = incclass2.IncidentClassificationID JOIN [DWDataMart].[dbo].[DisplayStringDim] disp on incclass.EnumTypeId = disp.MPElementId LEFT JOIN [DWDataMart].[dbo].[DisplayStringDim] disp2 on incclass2.EnumTypeID = disp2.MPElementId where Disp.LanguageCode = 'ENU' and Disp2.LanguageCode = 'ENU' AND inc.CreatedDate BETWEEN '05/15/2013' AND '06/06/2013' GROUP BY incclass.IncidentClassificationValue,disp2.DisplayName,disp.DisplayName, incclass.IncidentClassificationId,incclass.ParentId
<<inline: image001.png>>
