How to create a custom report for System Center 2012 Service Manager with user satisfaction survey data collected with SCUtils SurveyLite

Our product, SCUtils SurveyLite for System Center 2012 Service Manager, provides the functionality to gather user opinions about IT support team’s work. And some of our visitors have been asking about how to make this information available in SCSM reports. For SCSM super-pros this is an easy task but for others it isn’t so obvious. That’s why we’ve decided to make a sample report to present the approach how to achieve the goal. In our scenario we will create the report that lists SCSM incidents with user rate and comment if any.

First of all, I appreciate Miao Jun Huang, who published a blog post where he described the whole process of the report’s customization. What we have to do is just following his instructions with some extra steps because we need to add the data related to the Incident class. You can download the whole set of files that we used in the end of this post and use it as you wish or you can follow the process and find out the details of each stage.

In comparison with Miao’s post we already have a class that we want to bring to reports - SCutils.Survey.Lite. This class is defined in management pack SCutils.Survey.Lite.MP.mp that is listed in the Service Manager’s console as ‘SCutils User Satisfaction Survey Library’. You have to export it and open in the XML editor of your choice. In the XML code you can see the two important pieces of information: 1) definition of SCutils.Survey.Lite; 2) definition of SCutils.WorkItemHasSurveyInformation. The latter is a relationship that defines a link between System.WorkItem class and SCutils.Survey.Lite. System.WorkItem is a parent class for Incident (System.WorkItem.Incident) and Service Request (System.WorkItem.ServiceRequest) classes so this relationship is applicable for both of them. This part of XML shows these definitions:

<ClassTypes>
       <ClassType ID="SCutils.Survey.Lite" Accessibility="Public" Abstract="false" Base="System!System.Entity" Hosted="false" Singleton="false" Extension="false">
         <Property ID="workitemId" Type="string" AutoIncrement="false" Key="true" CaseSensitive="false" MaxLength="10" MinLength="0" Required="false" Scale="0" />
         <Property ID="userName" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" />
         <Property ID="rate" Type="int" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="256" MinLength="0" Required="false" Scale="0" />
         <Property ID="comment" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="4000" MinLength="0" Required="false" Scale="0" />
       </ClassType>
</ClassTypes>
<RelationshipTypes>
       <RelationshipType ID="SCutils.WorkItemHasSurveyInformation" Accessibility="Public" Abstract="false" Base="System!System.Reference">
          <Source ID="Source" MinCardinality="0" MaxCardinality="2147483647" Type="WorkItem!System.WorkItem" />
          <Target ID="Target" MinCardinality="0" MaxCardinality="2147483647" Type="SCutils.Survey.Lite" />
       </RelationshipType>
</RelationshipTypes>

In our second management pack SCutils.Survey.Lite.MPDW.mp (SCutils User Satisfaction Survey Data Warehouse Library) we store the definition the dimension with survey data for Data Warehouse. You can export and open in XML editor as well. The most interesting part is a definition of relationshipfact (WorkItemHasSurveyInformationFact) for SCutils.WorkItemHasSurveyInformation. Based on this definition Data Warehouse jobs create tables and views in DWDataMart database.

 <Facts>
      <RelationshipFact ID="WorkItemHasSurveyInformationFact" Accessibility="Public" Domain="DWBase!Domain.Common" TimeGrain="Daily" SourceType="WorkItem!System.WorkItem" SourceDimension="DWBase!WorkItemDim">
        <Relationships RelationshipType="SSLM!SCutils.WorkItemHasSurveyInformation" TargetDimension="SCutilsSurveyLiteDim" />
      </RelationshipFact>
 </Facts>

Now we know all we need to make our custom report that lists incidents with user rates and comments. Let’s follow Miao’s way.

Of course, we can’t use the ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_Install stored procedure because we work with incidents not change requests. We have to choose ServiceManager_Report_IncidentManagement_SP_GetListOfIncidents instead. Following the guide we create a new customized stored procedure named SCutils_Report_IncidentManagement_SP_GetListOfIncidents_Install.

Then we have to make some changes. First, we add the following code in the SCutils_Report_IncidentManagement_SP_GetListOfIncidents_Install  to drop the existing stored procedure.

-- Drop stored procedure if it already exists

IF EXISTS(

  SELECT*

    FROMINFORMATION_SCHEMA.ROUTINES

   WHERESPECIFIC_SCHEMA=N'dbo'

     ANDSPECIFIC_NAME=N'SCutils_Report_IncidentManagement_SP_GetListOfIncidents_Install'

)

   DROPPROCEDUREdbo.SCutils_Report_IncidentManagement_SP_GetListOfIncidents_Install

GO

Then we add in the end of the stored procedure the code that grants EXECUTE permission for ReportUSer database role.

GO

GRANT EXECUTEONdbo.SCutils_Report_IncidentManagement_SP_GetListOfIncidents_InstallTOreportuser

GO

Finally, we have to include the most complicated part of the code that adds the survey data to T-SQL select statement.

Just under the text (ON dbo.WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey=AssignedTo.UserDimKey) add the following code (which was created based on the above-mentioned XML definitions):

LEFT OUTERJOIN           

dbo.WorkItemHasSurveyInformationFactvw

ON dbo.WorkItemHasSurveyInformationFactvw.WorkItemDimKey=WI.WorkItemDimKey

AND(@IncludeDeleted = 1 ORdbo.WorkItemHasSurveyInformationFactvw.DeletedDateISNULL)

LEFT OUTERJOIN

dbo.SCutilsSurveyLiteDimvw ASUserSurvey

ON dbo.WorkItemHasSurveyInformationFactvw.WorkItemHasSurveyInformation_SCutilsSurveyLiteDimKey=UserSurvey.SCutilsSurveyLiteDimKey

Then find string AssignedTo.DisplayName AssignedToUserName and put a comma just after and insert these 2 strings:

UserSurvey.rate,

UserSurvey.comment

The result should look like this:

AssignedTo.DisplayName AssignedToUserName,

UserSurvey.rate,

UserSurvey.comment

You can find the final version of the file (SCutils.Report.IncidentManagement.SP.GetListOfIncidents.Install.sql) in the attached zip file.

Following the initial blog instructions, we create the SCutils.Report.IncidentManagement.SP.GetListOfIncidents.Uninstall.sql file.

Then we download the ServiceManager.Report.IncidentManagement.ListOfIncidents.rdl file from Report Services web site. Then we open it in SQL Server Business Intelligence Studio (renamed to SQL Server Data Tools for Microsoft SQL 2012 Server) and start editing. We follow the guide directly only using our own stored procedure SCutils_Report_IncidentManagement_SP_GetListOfIncidents_Install for DataSet Table_Incidents.  Then we make some additional steps for DataSet LT_Report.

We open LT_Report DataSet Properties, then go to Parameters and change Parameter Values for @ManagementPackSystemName to SCutils.IncidentManagement.Report.Library and @ReportSystemName to SCutils.Report.IncidentManagement.ListOfIncidents.

We have to do so to retrieve customized display strings for our report.

We also create two columns and define values (rate and comment) for cells.

We also define the interactive sorting for both columns as shown on the pictures below.

We also have to define expressions for menu. For rate we use this expression =Code.GetLocTable("LT_Report").GetString("RateLabel", "[Rate]").

And for comment  =Code.GetLocTable("LT_Report").GetString("CommentLabel", "[Comment]")

Please note that we use RateLabel and CommentLabel for labels. Later we’ll include those labels in our XML file defined the report.

We rename the report definition file to SCutils.Report.IncidentManagement.ListOfIncidents.rdl as Miaosuggestedandsave all changes. Optionally you may change a bit the report header’s design to make it look more polished as we do.

Now we start to build XML file for the report. We use ServiceManager.IncidentManagement.Report.Library.xml (Incident Management Report Library) as a template. Using the same steps like Miao did we customize the file in an XML editor. Furthermore, we add additional display strings for custom labels: RateLabel and CommentLabel. I don’t describe all changes we do with the original XML file because you will have the final version of the file (SCutils.IncidentManagement.Report.Library.xml) in the attached zip. And there you can easily trace all changes by yourself. We’ve deleted all language packs except the default English one. If you want to add a language pack for another language, please use the blog post written by Travis Wright in the blog of the System Center 2012 Service Manager’s product team.

Now time to collect all parts and build a management pack for System Center 2012 Service Manager. We use tools FastSeal.exe with testkeys.snk (which I borrowed from here long time ago for such purposes) and New-MPBFile.ps1 and create SCutils.IncidentManagement.Report.Library.mpb. After the management pack’s import and deployment completed and MPSyncJob finished the cycle we’ll get our report in Incident Management:

You can use the same approach to create or customize other reports for Incidents or Service Requests. The best way to learn is a close study of default SCSM reports and official blog of the product team.

Your questions are always welcomed. Of course, the best way to get and analyze data is using OLAP cubes. I do hope that this month our company will release SCUtils CubeDesigner that makes the process of OLAP cube building as simple as possible. With this great tool building reports is a real fun and pleasure. Good luck with SCSM reporting!

incident custom report with rate and comment.zip

SCutils.IncidentManagement.Report.Library.mpb