Reporting: Count of Incidents by Classification Query and How to Get Enumeration Display Strings

Reporting: Count of Incidents by Classification Query and How to Get Enumeration Display Strings

  • Comments 2
  • Likes

There really isn’t a lot of information out there about reporting so I’m just going to start putting some things out here in the blog as I come across them.  Hopefully, these little posts will eventually add up to something meaningful collectively.

This blog post will cover a couple of things –

1) How to join two dimensions – the incident dimension and the incident classification dimension.

2) How to get the display strings for a particular language for a particular enumeration.

Here is the query:

SELECT Strings.DisplayName AS Classification, COUNT(*) AS Incidents
FROM IncidentDimvw Incident
Join  IncidentClassificationvw Classification ON Incident.Classification_IncidentClassificationId = Classification.IncidentClassificationId
Join  DisplayStringDimvw Strings ON Classification.EnumTypeId = Strings.BaseManagedEntityId
WHERE Strings.LanguageCode = 'ENU'
GROUP BY Strings.DisplayName

Here is a sample of the results:

image

A couple of interesting things that are good to know:

All enumerations (aka “Lists”) that are brought over to the data warehouse are stored on dimension tables.  We also call them “Outriggers”.  The display strings for those enumerations are stored on the DisplayString dimension.  You’ll need to join across the enumeration dimension table to the DisplayString dimension table to get the localized display string for a given enumeration value.

Also notice how the query above queries the views (notice the vw at the end of the names) not the tables directly.  This is definitely a recommended best practice.  Although it is unlikely that we will change the table schema drastically, having customers use the views instead of the tables provides us the flexibility to change the table schema if we need to in the future without breaking customers’ reports.

More to come on report authoring…

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hey,

    I have been trying to use this script and modify appropriately to display the for a time period starting from today - 7 days to today.

    Can anyone please help me ?

    F

    :)

  • declare

    @Start varchar(10) = convert(varchar(10),GETDATE()-7,121),                   -- 7 days prior to today

    @End varchar(19) = convert(varchar(10),GETDATE(),121) + ' ' + '23:59:59'     -- today (31 Jan 2012)

    SELECT

    Strings.DisplayName AS Classification

    ,COUNT(*) AS Incidents

    --,Incident.CreatedDate

    FROM IncidentDimvw Incident

    Join  IncidentClassificationvw Classification ON Incident.Classification_IncidentClassificationId = Classification.IncidentClassificationId

    Join  DisplayStringDimvw Strings ON Classification.EnumTypeId = Strings.BaseManagedEntityId

    WHERE Strings.LanguageCode = 'ENU'

    and Incident.CreatedDate between @Start and @End

    GROUP BY

    Strings.DisplayName

    --,Incident.CreatedDate

    /*

    I filtered by CreatedDate, you can also use any other date such as Incident.ResolvedDate, etc. whatever returns dates.

    */