Reporting help needed
is there anyone here that has any good queries for reporting resolution time for specific groups? or modify the query to grab only the following three support groups ( Product Support, Product Support Level 1, Product Support Level 2 )
Also can you set it so I can either change a date range when needed.
DECLARE @Month int = DATEPART(mm, GetDate()), @Day int = DATEPART(dd,GETDATE()), @Year int = DATEPART(yyyy, GETDATE())
SELECT TOP 25 ISNULL(d.DisplayStringOverride, DisplayString) as SupportGroup, AVG(DATEDIFF(mi,Created, ResolvedDate)) as AverageResolutionTime
FROM WorkItem wi
INNER JOIN CI$User u ON wi.AssignedUserId = u.Id
INNER JOIN GroupMembership_CI$DomainGroup_CI$User ugrp ON u.Id = ugrp.UserId
INNER JOIN SupportGroupMapping_CI$DomainGroup_Enumeration sgrp ON ugrp.DomainGroupId = sgrp.DomainGroupId
INNER JOIN DisplayString d ON sgrp.EnumerationId = d.ElementID
WHERE ResolvedDate IS NOT NULL
AND Created IS NOT NULL
AND ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' --Incident
AND DATEPART(yyyy, ResolvedDate) = @Year
AND DATEPART(mm, ResolvedDate) = @Month
AND DATEPART(dd, ResolvedDate) BETWEEN 1 AND @Day
AND d.LocaleID = 'ENU'
GROUP BY sgrp.DomainGroupId, d.DisplayStringOverride, d.DisplayString
ORDER BY AverageResolutionTime ASC
alex_kokin Member IT Monkey ✭
thank you very MUCH1
@alex_kokin - I have done a small bit of rewriting of the query. It now filters for the support group names you specified and includes the @createdFilter in the WHERE clause that will allow for selecting the 'Enable Chart Filtering' option in the dashboard widgets. With the @createdFilter and Enable Chart Filtering turned on, you can apply the date filters dynamically.
SELECT d.DisplayString as 'SupportGroup',
AVG(DATEDIFF(mi,w.Created, w.ResolvedDate)) AS 'AverageResolutionTime'
FROM WorkItem w
INNER JOIN CI$User u
ON u.Id = w.AssignedUserId
INNER JOIN GroupMembership_CI$DomainGroup_CI$User ugrp
ON u.id = ugrp.UserId
INNER JOIN SupportGroupMapping_CI$DomainGroup_Enumeration sgrp
ON ugrp.DomainGroupId = sgrp.DomainGroupId
INNER JOIN DisplayString d
ON sgrp.EnumerationId = d.ElementID
AND d.LocaleID = 'enu'
WHERE classid = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68'
AND ResolvedDate IS NOT NULL
AND Created IS NOT NULL
d.DisplayString = 'Product Support'
d.DisplayString = 'Product Support Level 1'
d.DisplayString = 'Product Support Level 2'
GROUP BY sgrp.DomainGroupId, d.DisplayString
ORDER BY 'AverageResolutionTime' ASC
thank you very MUCH
can you by chance see in here why the filter is not working?
,wi.System_WorkItemAssignedToUser_DisplayName AS 'AssignedTo'
,CASE WHEN wi.Title LIKE '%ADO-%' THEN Left(wi.Title,CHARINDEX(']',wi.Title,0)) ELSE NULL END AS 'ADO'
,wi.Source_Id AS 'Ticket_ID'
,CAST(wi.FirstAssignedDate AS DateTime) AS 'Assigned_Date'
,CAST(wi.ClosedDate AS DateTime) AS 'Closed_Date'
,CAST(wi.ResolvedDate AS Date) AS 'Resolved_Date'
,DateDiff(Day, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Days_To_Close'
,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Hours_To_Close'
,DateDiff(Minute, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Minute_To_Close'
,wi.System_WorkItemCreatedByUser_DisplayName AS 'CreatedBy'
,wi.System_WorkItemAffectedUser_DisplayName AS 'AffectedUser'
ServiceManagement_New.cachert.csnCached_MT_System_WorkItem_Incident AS wi
WHERE 1 = 1
AND (wi.CreatedDate BETWEEN DATEFROMPARTS(Year(getDate()),MONTH(GetDate()),1) AND EOMONTH(GetDate(),0)
OR wi.FirstAssignedDate BETWEEN DATEFROMPARTS(YEar(getDate()),MONTH(GetDate()),1) AND EOMONTH(GetDate(),0)
OR wi.ResolvedDate BETWEEN DATEFROMPARTS(YEar(getDate()),MONTH(GetDate()),1) AND EOMONTH(GetDate(),0)
OR wi.Status = 'Active'
AND wi.TierQueue like '%Product Support%'
There's a couple of reasons this query won't work. The first is you can't use special characters( in this cause, you're using an Underscore) in the Column Aliases. That causes the results to not render in the SQL grid widget. Another reason this query won't work is the @createdFilter is designed to work with the Created column in the WorkItem table in the ServiceManagement database not with the cachert.csnCached_MT_System_WorkItem_Incident table.
Can you find out if there is a way for us to add customer filters to the widgets. The one that is in place currently is restricted to time and relies on a date filed to be named “Created”.
From there, is it possible to add multiple filters within a single widget. It would be nice to treat these much like slicer on a chart in other MS products.
Example would be the TierQueue. I can limit the data to be specific via the query, but it would be nice if I could add a TierQueue hat allows me to bring in multiple queues that I care about while having the ability to limit to specific ones on the widget or see them all grouped together.
Let me know if you need more information to help with this inquiry.
Thank you sir,