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

Published Feb 15 2019 05:03 AM 315 Views
First published on TECHNET on Feb 10, 2011

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:

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 view s (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…

Version history
Last update:
‎Mar 11 2019 08:39 AM
Updated by: