Log in  \/ 
x
x
x
Register  \/ 
x

29
October
2013

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="SLAInstanceTimeInformationDimAccessibility="PublicInferredDimension="trueTarget="CoreSLA!System.SLA.Instance.TimeInformationHierarchySupport="IncludeExtendedClassPropertiesReconcile="true" />

</Dimensions>

<Facts>

  <RelationshipFact ID="WorkItemHasSLAInstanceInformationFactAccessibility="PublicDomain="DWBase!Domain.CommonTimeGrain="DailySourceType="WorkItem!System.WorkItemSourceDimension="DWBase!WorkItemDim">

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

  </RelationshipFact>

  <RelationshipFact ID="InformationRefersToSLAConfigurationFactAccessibility="PublicDomain="DWBase!Domain.CommonTimeGrain="DailySourceType="CoreSLA!System.SLA.Instance.TimeInformationSourceDimension="SLAInstanceTimeInformationDim">

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

  </RelationshipFact>

  <RelationshipFact ID="SLAConfigurationsTargetUnitsFactAccessibility="PublicDomain="DWBase!Domain.CommonTimeGrain="DailySourceType="CoreSLA!System.SLA.ConfigurationSourceDimension="DWSLA!SLAConfigurationDim">

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

  </RelationshipFact>

  <RelationshipFact ID="SLAConfigurationsWarningUnitsFactAccessibility="PublicDomain="DWBase!Domain.CommonTimeGrain="DailySourceType="CoreSLA!System.SLA.ConfigurationSourceDimension="DWSLA!SLAConfigurationDim">

    <Relationships RelationshipType="CoreSLA!System.SLA.ConfigurationHasWarningThresholdTargetDimension="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.

Pic1

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

Pic2

 SLA report.zip 

Categories: System Center 2012 Service Manager

Marat Kuanyshev

Comments (12)

  • Audrey Paige

    Audrey Paige

    30 October 2013 at 18:37 |
    What should we need to customize in the xml? Can we just load it directly? Also, should the SCUtils SLA Instance pack be installed first or is this independant of that?

    reply

  • Marat Kuanyshev

    Marat Kuanyshev

    30 October 2013 at 18:48 |
    Dear Audrey,
    You have to install SCUtils SLA Instance pack before and make sure that all ETL jobs have been processed at least once before first use of the report.
    After importing SCutils.SLAIncidentManagement.Report.Library.mpb you have to wait till this management pack becomes fully associated (check in Data Warehouse->Data Warehouse Jobs-> MPSyncJob's details)

    reply

  • Marat Kuanyshev

    Marat Kuanyshev

    30 October 2013 at 18:52 |
    Regarding the customizing - you could add your language's support if you wish or additional columns. However, the report 'as is' is fully functional. Of course, I highly recommend to test it in the testing environments for more confidence.

    reply

  • Audrey Paige

    Audrey Paige

    30 October 2013 at 18:53 |
    Ok, thanks!

    reply

  • Judy Olsen

    Judy Olsen

    15 July 2016 at 02:06 |
    Do you have any queries for SLA's on Service Requests?

    reply

    • Marat Kuanyshev

      Marat Kuanyshev

      15 July 2016 at 06:20 |
      Dear Judy,
      Do you need some specific queries?

      reply

  • Thiago Beier

    Thiago Beier

    08 June 2017 at 20:22 |
    Hi guys,
    I went through 3 articles
    SCUtils - How to create a custom report for System Center 2012 Service Manager with SLA information included SLA instance durations counted by SCUtils SLAInstanceDurationIn 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 www.scutils.com#1 https://www.scutils.com/blog/scutils-blog/system-center-2012-service-manager/slainstanceduration
    #2 https://www.scutils.com/blog/scutils-blog/system-center-2012-service-manager/how-to-create-a-custom-report-for-system-center-2012-service-manager-with-user-satisfaction-survey-data-collected-with-scutils-surveylite
    #3 https://www.scutils.com/blog/scutils-blog/system-center-2012-service-manager/how-to-create-a-custom-report-for-system-center-2012-service-manager-with-user-satisfaction-survey-data-collected-with-scutils-surveylite

    which one should I do 1st? to have the results posted here? I got myself lost after reading the 3 articles.

    thanks,

    reply

  • Marat Kuanyshev

    Marat Kuanyshev

    08 June 2017 at 22:08 |
    Hi Thiago,
    Can you specify your goal?
    Regards,

    reply

    • Thiago Beier

      Thiago Beier

      09 June 2017 at 00:50 |
      I need to create the proposal custom report for SCSM2012 with SLA information included. I reached this article and read a few others here as mentioned but I didn't get how to do it as steps. I'm not pretty familiar with SQL server administration.

      reply

      • Marat Kuanyshev

        Marat Kuanyshev

        09 June 2017 at 06:00 |
        You can start with analyzing our report but you need some basic knowledge in the SQL and structure of SCSM database.

        reply

        • Thiago Beier

          Thiago Beier

          09 June 2017 at 06:11 |
          so if I follow the steps posted here I can't get the same results? because this is what we're looking for. "create a custom report for System Center 2012 Service Manager with SLA information"

          reply

          • Marat Kuanyshev

            Marat Kuanyshev

            09 June 2017 at 06:19 |
            You can actually but you need take into account that this report uses a class defined in SCUtils SLAInstanceDuration. If you don't plan to use SCUtils SLAInstanceDuration, you have carefully repeat all steps described here but using default SCSM classes, SQL tables, etc.

            reply

Leave a comment

You are commenting as guest.

This is the default dialog which is useful for displaying information. The dialog window can be moved, resized and closed with the 'x' icon.