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.
