Trying to Create Dashboard Report for Resolution Category

robert_fewins-kalb

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!

Adam_Dzyacky

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.

Adam_Dzyacky

Analytics or SCSM DW?

robert_fewins-kalb

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

Adam_Dzyacky

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-kalb

That's awesome! Thank you!

Adam_Dzyacky

Looks great!