Home› Analytics

Reporting help needed

alex_kokinalex_kokin Member IT Monkey ✭
in Analytics

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

Best Answer

Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @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

    AND (

    d.DisplayString = 'Product Support'

    OR

    d.DisplayString = 'Product Support Level 1'

    OR

    d.DisplayString = 'Product Support Level 2'

    )

    AND @createdFilter

    GROUP BY sgrp.DomainGroupId, d.DisplayString

    ORDER BY 'AverageResolutionTime' ASC

  • alex_kokinalex_kokin Member IT Monkey ✭
    Answer ✓

    thank you very MUCH

  • alex_kokinalex_kokin Member IT Monkey ✭

    can you by chance see in here why the filter is not working?


    Select  

      wi.TierQueue

      ,wi.Title

      ,wi.Classification

      ,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'

      ,wi.Priority

      ,wi.Status

      ,wi.ResolutionCategory

      ,wi.ResolutionDescription    

      ,wi.Urgency

      ,wi.Impact

      ,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'

       

    FROM 

      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%'

    AND @createdFilter

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    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.

  • alex_kokinalex_kokin Member IT Monkey ✭

    thank you...

  • alex_kokinalex_kokin Member IT Monkey ✭

    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,

Sign In or Register to comment.