Home General Discussion

Trying to Create Dashboard Report for Resolution Category

We are trying to create a dashboard report showing a simple pie chart by resolution category, but we are really having a difficult time joining to a table with plain text for the Resolution Category. We can find the GUID all over the place,. but I'm wondering if anyone has had success getting the plain text for Resolution Category (similarly, with Implementation Result).


Thanks!

Best Answer

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    Accepted Answer

    In this very specific case, the bar to clear is near identical. It's the Incident table and the DisplayStrings table.

    Analytics

    select ds.DisplayString as 'Resolution Category', count(ds.DisplayString) as 'Total'
    from SM_WorkItem_Incident as ir
        inner join SM_DisplayString as ds on ir.ResolutionCategory = ds.ElementID
    where ResolutionCategory is not null
        and ds.LocaleID = 'ENU'
    group by ds.DisplayString
    

    SCSM DW

    select ds.DisplayName as 'Resolution Category', count(ds.DisplayName) as 'Total'
    from IncidentDim as ir
        inner join DisplayStringDim as ds on ir.ResolutionCategory = ds.ElementName
    where ResolutionCategory is not null
        and ds.LanguageCode = 'ENU'
    group by ds.DisplayName
    


    More times than not though, Analytics is going to be the fastest/easiest option as there are significantly less JOINS required to render queries as the tables are incredibly flat.

Answers

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Analytics or SCSM DW?

  • robert_fewins-kalbrobert_fewins-kalb Member IT Monkey ✭

    @Adam_Dzyacky - Either. We're just trying to get started. Is one simpler than the other?

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    Accepted Answer

    In this very specific case, the bar to clear is near identical. It's the Incident table and the DisplayStrings table.

    Analytics

    select ds.DisplayString as 'Resolution Category', count(ds.DisplayString) as 'Total'
    from SM_WorkItem_Incident as ir
        inner join SM_DisplayString as ds on ir.ResolutionCategory = ds.ElementID
    where ResolutionCategory is not null
        and ds.LocaleID = 'ENU'
    group by ds.DisplayString
    

    SCSM DW

    select ds.DisplayName as 'Resolution Category', count(ds.DisplayName) as 'Total'
    from IncidentDim as ir
        inner join DisplayStringDim as ds on ir.ResolutionCategory = ds.ElementName
    where ResolutionCategory is not null
        and ds.LanguageCode = 'ENU'
    group by ds.DisplayName
    


    More times than not though, Analytics is going to be the fastest/easiest option as there are significantly less JOINS required to render queries as the tables are incredibly flat.

  • robert_fewins-kalbrobert_fewins-kalb Member IT Monkey ✭

    That's awesome! Thank you!



  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Looks great!

Sign In or Register to comment.