Create a report model with localized outriggers (aka “Lists”)

Create a report model with localized outriggers (aka “Lists”)

  • Comments 4
  • Likes

If you've watched my Reporting and Business Intelligence with Service Manager 2010 webcast and followed along in your environment, you may have unintentionally created a report which displays enumeration guids instead of Incident Classification strings, like below. Not too useful. In this post I'll tell you the simple way to fix your report model to include the display strings for outriggers for a specific language, and in a follow on post I'll share more details as to how to localize your reports and report models.

You may be wondering what happened. This is because we made a change in SP1 to consistently handle outrigger values which removed the special handling we had for our out of the box enumerations in outriggers. If you're now wondering what outriggers are, read up on the types of tables in data warehouse in my last post in which I provided the service manager data warehouse schema.

Here's the screenshot of the report we need to fix, the rest of the post will explain how to fix it.

 

Replace table binding in the Data Source view with Query binding

Rather than including references to the outriggers directly (in the screenshot below the outriggers are IncidentClassificationvw, IncidentSourcevw, IncidentUrgencyvw, and IncidentStatusvw) we'll replace these with named queries.

To do this, you simply right click the "table" and select Replace Table > With New Named Query.

 

You then paste in your query which joins to DisplayStringDimvw and filter on the language of your choice. Repeat for each outrigger.

SELECT outrigger.IncidentClassificationId, Strings.DisplayName AS Classification

FROM IncidentClassificationvw AS outrigger INNER JOIN

DisplayStringDimvw AS Strings ON outrigger.EnumTypeId = Strings.BaseManagedEntityId

WHERE (Strings.LanguageCode = 'ENU')

 

Create & publish your report model

To create a simple report model, right click the Report Models node in the Solution Explorer (right pane) and select Add New Report Model. Follow the wizard, selecting the default options.

 

If you want to clean it up a little, double click the Report Model, then select IncidentDim on the left.

Scroll down the properties in the center and you'll notice there is now a Role added to the IncidentDim named Classification Incident Classification, along with an Attribute named Classification. This is because using outriggers to describe dimensions is an industry standard approach and SQL BI Dev Studio understands that these outriggers should essentially get added as properties directly to the Incident dimension for the easiest end user report authoring experience.

The attribute is populated directly by the column I mentioned you should not use in reports, so you should select and delete that attribute from your model. You may also rename the Role "Classification Incident Classification" to a more user-friendly name like "Incident Classification" if you'd like to.

 

Now save, right click your report model and click Deploy.

Create a report to try out your new report model

Open up SQL Reporting Services Report Builder (below screenshots are using Report Builder 3.0). If you haven't gotten a chance to check it out yet, here's a good jump start guide.

 

Follow the wizard, select your newly published report model:

 

Drag & drop your Incident Classification and Incidents measure. Hit the red ! to preview.

 

Drag & drop to layout the report

 

Continue with the wizard, selecting the formatting options of your choice. If you would like, you can then resize the columns, add images and more. For our quick and simple example, though, I'm going to intentionally leave formatting reports for another post. If you've been following along, your report should now look like this:

 

Go ahead and publish to the SSRS server under the /SystemCenter/ServiceManager/ folder of your choice to make the report show up in the console.

 

 

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 Thanks

    It is really nice ..

  • Hello Chris,

    First off I have searched everywhere for the Reporting and Business Intelligence with Service Manager 2010 webcast,cant find it anywhere.This is my issue,in my case I have custimised the default incident form to include two lists: Business Unit and Support Location.I have read about using outriggers to get the display names from the GUID's,but I have tried everything possible cant seem to get this right,and I am stuck with report which just shows GUID's instead of display names.I user the below query:SELECT Strings.DisplayName AS Business_Unit, COUNT(*) AS Incidents

    FROM IncidentDimvw Incident

    Join IncidentDimvw Business_unit ON Incident.Business_unit = Incident.Business_unit

    Join  DisplayStringDimvw Strings ON Incident.Business_unit = Strings.BaseManagedEntityId

    WHERE Strings.LanguageCode = 'ENU'

    GROUP BY Strings.DisplayName

    But thats how far I can get to.I have even decided to raise a support with MS.I was hoping you could assist me with this Issue.

  • Hi Chris,

    I came across this blog entry while searching for a solution to outriggers that don't populate the display value correctly.  

    According to what I've read, the [<outriggername>Value] column should contain a display string.  For example, the Technet article "About Outriggers in the Data Warehouse" (technet.microsoft.com/.../hh916535.aspx) uses ActivityStatus as an example, and states: "The ActivityStatusValue is the actual user-friendly display name that appears in the report drop-down menus".

    This seems to be the case for many (most?) outriggers. One working example is the IncidentImpact outrigger table.  A couple of custom outriggers we have added have mixed results, and many 'out-of-the-box' outriggers now have a mixture of display stings and enum IDs (notably, those where we have changed the list content).

    I've reluctantly changed my reports to get display strings from the DisplayStringDim, and have noticed a significant degredation in performance - the queries now take MUCH longer to run, including those that populate report parameter dropdowns.  Running the queries in SSMS and showing the execution plans prove that it was the DisplayStringDim joins that were taking most of the time.

    You say the problem with missing display strings was caused "... because we made a change in SP1 to consistently handle outrigger values which removed the special handling we had for our out of the box enumerations in outriggers".  Surely the display strings should still be populated?  That is the whole idea of the outrigger - to simplify reporting queries and improve performance.

    Paul.

  • Hi Chris. Is there any chance you could make the webcast mentioned at the top of the article (https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032454293&Culture=en-US) available again please? I need it in order to implement your workaround. Thanks a mil, Ben