Working with SCSM reporting

April 10, 2012 at 6:05 am in Service Manager by kurtvh

In Service Manager you have the possibility to extend the classes with additional properties. For example, the Incident class can be extended with a “Location” property. How to do this is explained in different blog articles  around SCSM. The reason why you extend the class can be for visualization, but in most cases this is done for reporting purpose. The Process owner wants to measure on this property.

This blog articles provides some more information what you need to do in Service Manager (2010 or 2012, no difference in versions) before you can actually start customizing your reports.

The information that you want to make visible has an impact on the procedure to prepare the DWDataMart database in order to be able customize the report in Report Builder or Business Intelligence Design Studio. The following figure provides an overview of the important components that builds-up your reporting environment.

reports

Some explanation around the illustration.

It starts with the import of the information (custom class property) via a sealed management pack if you need extended information in your report. Once you need to start reporting on this extended class property, first point to know is what information you need to make visible in the report. If it is a direct class property (string, int…), a list or a relationship property. Each of these class property flavors demand for different preparation work and maybe demand for an additional management before you can report on this values. A little overview:

  • Direct class property: The default property values are synchronized with DWDataMart. The View “dbo.IncidentDimvw” for example holds these default properties and extended properties from sealed management packs. Same for other classes that are available for reporting.
  • List class properties: In order to enumerate these values in a custom report you need to create an <Outrigger> MP.
  • Relationship class properties: Relationship property is in most cases to a standard class property. For example, Incident mgmt reporting where you want to have the AffectedUser displayed in a column. If the relationship property is an own extended property, you need to create the required Views in SQL DWDataMart database.

The SQL Stored Procedure (SP) holds the information that you want to display in your report. When you need to create a custom report that requires additional values that is not available in the default SCSM delivered Stored Procedures you need to create your own SP. Start point of your own SP can be a copy of an existing one, this gives you a quick start. To extend the SP so that it holds the additional properties for reporting you need to keep following in mind:

  • Direct class property: The SQL View “dbo.IncidentDimvw” includes automatically this information. For example, you want to extend a report for Incident Management. A direct class property column must be added. You use a copy of the “ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents” SP as basis for your custom SP. In this SP the SQL View “dbo.IncidentDimvw” is already included. With a single line you make this information available in your own SP.
  • List class properties: To get this information available in the SP you need to translate the GUID (direct property in the “dbo.IncidentDimvw” View) with the additional Views that are created via your “Outriggers MP”.
  • Relationship class properties: To extend a SP to include a relationship property for reporting you have to start enumerating the relation in the SP. For example, if you need to get the AffectedUser name in the custom report; via the “WorkItemAffectedUserFactvw” View you find the AffectedUser and properties can be found in the “dbo.UserDimvw” View.

After having this done in your SCSM and SCSM DW SQL environment you can start customizing the report. Details how to do this can be found in following blog articles:

This blog post doesn’t provide detailed information where to click in different console, but tries to help positioning what you need to do for customizing reports in SCSM. The referenced blog post include procedures how to realize the different tasks.

Hope this helps start customizing reports in SCSM.

Have Fun!

Kurt Van Hoecke

Digg thisTweet about this on TwitterShare on LinkedInShare on Facebook