Home› Service Manager

SQL Widget Query - @createdFilter

Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭
in Service Manager

I have been using this filter on a query in Analytics to get completed IRs and where I can see if it met or breached SLA amongst other things.

The @createdFilter is great for the jobs created in that filter, but now I need WI closed within the filter. Is there a closed filter I can apply?

My SQL:

SELECT wiIR.WorkItemId AS [Id], wiIR.Title, wiIR.Status, wiIR.PriorityId AS 'Priority', 

CASE FCR.BMBCFirstCallResolution_0687BDE9_DF2D_90B0_A69B_D33CCA065645 WHEN 1 THEN ISNULL(wiIR.AssignedUser,wiIR.CreatedByUser) ELSE ISNULL(AssignedUser, '') END AS AssignedUser, 

wiIR.AffectedUser, wiIR.Category, wiIR.Tier AS 'SupportGroup', ISNULL(wiIR.ResolvedDate,wiIR.LastModified) AS 'Resolved', 

CASE FCR.BMBCFirstCallResolution_0687BDE9_DF2D_90B0_A69B_D33CCA065645 WHEN 1 THEN ISNULL(DS.DisplayString,'Completed') ELSE ISNULL(DS.DisplayString, '') END AS ResolutionCategory, 

FCR.BMBCFirstCallResolution_0687BDE9_DF2D_90B0_A69B_D33CCA065645 AS 'FCR', 

CASE SLOStatus WHEN 'SLAInstance.Status.Breached' THEN 'Breached' ELSE 'Met' END AS 'SLA'

FROM CiresonAnalytics.dbo.SM_WorkItem_Incident AS wiIR

LEFT JOIN ServiceManager.dbo.MT_BMBC$WorkItem$Incident$FirstCallResolution$Extension FCR ON FCR.BaseManagedEntityId = wiIR.Id

LEFT JOIN CiresonAnalytics.dbo.SM_DisplayString DS ON wiIR.ResolutionCategory = DS.ElementID and DS.LocaleID = 'ENU' AND wiIR.ResolutionCategory IS NOT NULL

WHERE (wiIR.Status = 'Resolved' OR wiIR.Status = 'Closed') 

AND @createdFilter

ORDER BY Resolved, wiIR.Id

Best Answer

Answers

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    Hi @Emma_Jenkins

    Currently I do not believe there is what you are asking for a closed filter, if you take a look at these KBs this is what filters we have:

    https://support.cireson.com/KnowledgeBase/View/1291#/
    https://support.cireson.com/KnowledgeBase/View/1359#/


    Thanks,

    Shane.

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭

    Recommend to upvote the feature request

    https://community.cireson.com/discussion/2168/other-options-for-filtering-data-on-sql-widgets#latest

  • Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭

    Will do @Brian_Wiest, writing some SQL for this is going to take some time for a novice like me without a schema.

  • Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭

    Without a date filter in Analytics, I think I will need to produce this each quarter manually with a script like this, I just need help filtering the dates on closeddate column as I just cannot seem to get the date formats right here. So closed dates between 2019-04-01 and 2019-06-30 .

    My other option would be to put this through Report Builder but seems to defeat the object of this being our one stop shop.


    SELECT wiIR.WorkItemId AS [Id], wiIR.Title, wiIR.Status, wiIR.PriorityId AS 'Priority', wiIR.Closeddate,

    CASE FCR.BMBCFirstCallResolution_0687BDE9_DF2D_90B0_A69B_D33CCA065645 WHEN 1 THEN ISNULL(wiIR.AssignedUser,wiIR.CreatedByUser) ELSE ISNULL(AssignedUser, '') END AS AssignedUser, 

    wiIR.AffectedUser, wiIR.Category, wiIR.Tier AS 'SupportGroup', ISNULL(wiIR.ResolvedDate,wiIR.LastModified) AS 'Resolved', 

    CASE FCR.BMBCFirstCallResolution_0687BDE9_DF2D_90B0_A69B_D33CCA065645 WHEN 1 THEN ISNULL(DS.DisplayString,'Completed') ELSE ISNULL(DS.DisplayString, '') END AS ResolutionCategory, 

    FCR.BMBCFirstCallResolution_0687BDE9_DF2D_90B0_A69B_D33CCA065645 AS 'FCR', 

    CASE SLOStatus WHEN 'SLAInstance.Status.Breached' THEN 'Breached' ELSE 'Met' END AS 'SLA'

    FROM CiresonAnalytics.dbo.SM_WorkItem_Incident AS wiIR

    LEFT JOIN ServiceManager.dbo.MT_BMBC$WorkItem$Incident$FirstCallResolution$Extension FCR ON FCR.BaseManagedEntityId = wiIR.Id

    LEFT JOIN CiresonAnalytics.dbo.SM_DisplayString DS ON wiIR.ResolutionCategory = DS.ElementID and DS.LocaleID = 'ENU' AND wiIR.ResolutionCategory IS NOT NULL

    WHERE wiir.ClosedDate is not null

  • Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭

    @Shane_White I am certain that is the exact SQL I used yesterday that was not working 😅 It has worked now so that should give managers what they need for now. Thanks!

    Is there any way we can make date ranges more dynamic in the dashboards? Previously I would put calendars and sliding date bars into my PowerBI dashboards which always went down well.

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    @Emma_Jenkins I added in one line at the end which is in bold so you could see haha 😀

    Yes I believe there is a couple of ways to do it but I am not SQL expert! I used to do a yearly one that would be kind of like your query but repeated in the same query for different quarters.

    Thanks,

    Shane.

  • Emma_JenkinsEmma_Jenkins Customer IT Monkey ✭

    @Shane_White yes but I am sure I had that line, I will blame the heat!

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭

    Haha! 😋

Sign In or Register to comment.