How to create a custom report for System Center 2012 Service Manager with SLA information included SLA instance durations counted by SCUtils SLAInstanceDuration

In this post I described how to create a custom report based on the standard one to present a list of incidents with additional columns Rate and Comment defined in SCUtils SurveyLite. Many of our customers also ask us to do the same for SLA instance durations calculated by SCUtils SLAInstanceDuration solution. I also met the similar requests on the SCSM forums and finally decided to create the report with the information about SLA’s status, name and duration.

To be honest, the approach I used was entirely the same as in first post. Again I started with default report List of Incidents. However, I spent more time to extend the stored procedure to add the information about SLA instance and SLA name.

First, I needed to get a required data about fact tables in Data Warehouse. In the management pack named “SCUtils SLA Instance Duration Data Warehouse Management Pack” I found this information:

<Dimensions>

  <Dimension ID="SLAInstanceTimeInformationDim" Accessibility="Public" InferredDimension="true" Target="CoreSLA!System.SLA.Instance.TimeInformation" HierarchySupport="IncludeExtendedClassProperties" Reconcile="true" />

</Dimensions>

<Facts>

  <RelationshipFact ID="WorkItemHasSLAInstanceInformationFact" Accessibility="Public" Domain="DWBase!Domain.Common" TimeGrain="Daily" SourceType="WorkItem!System.WorkItem" SourceDimension="DWBase!WorkItemDim">

    <Relationships RelationshipType="CoreSLA!System.WorkItemHasSLAInstanceInformation" TargetDimension="SLAInstanceTimeInformationDim" />

  </RelationshipFact>

  <RelationshipFact ID="InformationRefersToSLAConfigurationFact" Accessibility="Public" Domain="DWBase!Domain.Common" TimeGrain="Daily" SourceType="CoreSLA!System.SLA.Instance.TimeInformation" SourceDimension="SLAInstanceTimeInformationDim">

    <Relationships RelationshipType="CoreSLA!System.SLA.Instance.InformationRefersToSLAConfiguration" TargetDimension="DWSLA!SLAConfigurationDim" />

  </RelationshipFact>

  <RelationshipFact ID="SLAConfigurationsTargetUnitsFact" Accessibility="Public" Domain="DWBase!Domain.Common" TimeGrain="Daily" SourceType="CoreSLA!System.SLA.Configuration" SourceDimension="DWSLA!SLAConfigurationDim">

    <Relationships RelationshipType="CoreSLA!System.SLA.ConfigurationHasTarget" TargetDimension="DWSLA!SLATimeTargetDim" />

  </RelationshipFact>

  <RelationshipFact ID="SLAConfigurationsWarningUnitsFact" Accessibility="Public" Domain="DWBase!Domain.Common" TimeGrain="Daily" SourceType="CoreSLA!System.SLA.Configuration" SourceDimension="DWSLA!SLAConfigurationDim">

    <Relationships RelationshipType="CoreSLA!System.SLA.ConfigurationHasWarningThreshold" TargetDimension="DWSLA!SLATimeTargetDim" />

  </RelationshipFact>

</Facts>

Based on the definition of the Relationship Facts I added to SQL script (FROM section) the following:

LEFT OUTER JOIN    

       dbo.WorkItemHasSLAInstanceInformationFactvw

       ON dbo.WorkItemHasSLAInstanceInformationFactvw.WorkItemDimKey = WI.WorkItemDimKey

       AND(@IncludeDeleted = 1 OR dbo.WorkItemHasSLAInstanceInformationFactvw.DeletedDate IS NULL)

       LEFT OUTER JOIN

       dbo.SLAInstanceTimeInformationDimvw AS SLAinfo

       ON dbo.WorkItemHasSLAInstanceInformationFactvw.WorkItemHasSLAInstanceInformation_SLAInstanceTimeInformationDimKey = SLAinfo.SLAInstanceTimeInformationDimKey

       LEFT OUTER JOIN    

       dbo.InformationRefersToSLAConfigurationFactvw

       ON dbo.InformationRefersToSLAConfigurationFactvw.SLAInstanceTimeInformationDimKey = SLAinfo.SLAInstanceTimeInformationDimKey

       AND(@IncludeDeleted = 1 OR dbo.InformationRefersToSLAConfigurationFactvw.DeletedDate IS NULL)

       LEFT OUTER JOIN

       dbo.SLAConfigurationDimvw AS SLAconfig

       ON dbo.InformationRefersToSLAConfigurationFactvw.InformationRefersToSLAConfiguration_SLAConfigurationDimKey = SLAconfig.SLAConfigurationDimKey

       LEFT OUTER JOIN

       SLAInstanceStatusvw AS SLAStatusEnum

       ON SLAStatusEnum.SLAInstanceStatusId = SLAinfo.Status_SLAInstanceStatusId

       LEFT OUTER JOIN

       dbo.DisplayStringDimvw SLAStatusDS

       ON SLAStatusEnum.EnumTypeId=SLAStatusDS.BaseManagedEntityId

       AND SLAStatusDS.LanguageCode = @LanguageCode

Then I added in the script (SELECT section) the following code:

SLAinfo.duration as SLAInstanceDuration,

SLAStatus = ISNULL(SLAStatusDS.DisplayName, SLAStatusEnum.SLAInstanceStatusValue),

SLAStatusEnum.SLAInstanceStatusId AS SLAStatusId,

SLAconfig.DisplayName as SLAName

I saved the result in the file SCutils_Report_IncidentManagement_SP_GetListOfIncidentsWithSLA_Install.sql. Then I repeated all my steps from the first blog post. The last thing that challenged me was the table’s part of the report. In general, each incident could have more than 1 SLA instance. That means the particular incident could obtain SLAs like Resolution SLA, First Assigned SLA, First Response SLA in the same time. If I kept using the only group like the default report does we’d get a list of incidents with repeated IDs, Titles, etc but with different SLAs. To avoid that I chosen to use multiple groups to group the repeated fields. You will see the result if you open SCutils.Report.IncidentManagement.ListOfIncidentsWithSLA.rdl file in in SQL Server Business Intelligence Studio(or SQL Server Data Tools for Microsoft SQL 2012 Server. Finally I changed the order of the sorting in the report’s table and packed the solution.

You can find all files I used in the attached archive. Using those files you can customize/localize the report for your needs.

This is the new report with SLA information.

Please draw your attention to the incident IR62. It has two SLA instances.

 SLA report.zip