Home› General Discussion
Options

Trying to Create Dashboard Report for Resolution Category

robert_fewins-kalbrobert_fewins-kalb Member IT Monkey ✭
in General Discussion

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

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    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

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Analytics or SCSM DW?

  • Options
    robert_fewins-kalbrobert_fewins-kalb Member IT Monkey ✭

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

  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    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.

  • Options
    robert_fewins-kalbrobert_fewins-kalb Member IT Monkey ✭

    That's awesome! Thank you!


    image.png


  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Looks great!

Sign In or Register to comment.