How to create a custom report and display it in the console

How to create a custom report and display it in the console

  • Comments 7
  • Likes

In this blog post I’m going to explain in detail how to add new properties to an existing class and make the corresponding changes which are required to enable reporting on the new properties.

For this example, we’ll add a new CostCenter property to our Change Requests, take the necessary steps to ensure this new property flows through to the data warehouse, and finally create a custom report which includes the new property.

Extending the Change Request class

First, to add the CostCenter property to the System.WorkItem.ChangeRequest class we need to create a new management pack (let’s call it Woodgrove.ChangeRequest.Extension). In this management pack we’ll add a reference to the management pack which defined the class we’re extending…in this case it’s System.WorkItem.ChangeRequest.Library. We do this in the References section of the Manifest like below:

<Manifest>

  <Identity>

    <ID>Woodgrove.ChangeRequest.Extension</ID>

         <Version>1.0.0.0</Version>

    </Identity>

       <Name>Woodgrove ChangeRequest Extension</Name>

       <References>

    <Reference Alias="ChangeRequestLibrary">

           <ID>System.WorkItem.ChangeRequest.Library</ID>

           <Version>7.0.5826.0</Version>

           <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>

      </Reference>

    </References>

  </Manifest>  

Now we can add the CostCenter property by creating a new extension class. This isn’t a true class all by itself; it just serves to add one or more properties to its base class (the class which you’re extending). We can specify this is an extension class by specifying Extension=”true” while defining the class like below:

<ClassType ID="Woodgrove.ChangeRequest.ExtensionClass" Accessibility="Public" Base="ChangeRequestLibrary!System.WorkItem.ChangeRequest" Extension="true">       

   <Property ID="CostCenter" Type="int" MaxLength="5" MinLength="5" />

</ClassType> 

Creating a custom report

Let’s go ahead and add Cost Center into the List of Change Requests report which shipped with Service Manager 2010. To do this, there are 6 major steps we’ll take:

1.       Make sure Woodgrove.ChangeRequest.Extension deployed successfully to the warehouse

2.       Customize the report stored procedure to retrieve Cost Center data

3.       Customize the RDL (report definition) which shipped out of the box to display the Cost Center

4.       Create a custom management pack to make it easy to deploy all the required resources

5.       Seal and bundle the customized RDL and stored procedure into our custom management pack

6.       Import the management pack bundle into Service Manager (this does the deployment of all the resources)

If you’ve worked with Reporting Services before, you probably know that you don’t need to create the management pack to deploy (you can publish right from SQL Management Studio) and there are shortcuts for many of the below steps. I want to show you the best, most consistently repeatable process to follow. Remember, management packs are like the Xbox games and Service Manager is like the Xbox console. The game has to not only include the logic of the game, but also the graphics and all the nitty gritty stuff to make it work every time you put it in the console. You can “mod” your console and maybe do cool stuff with less effort, but if you follow the below process you’ll be able to easily handoff a management pack bundle to any other team for quality assurance or production deployment and know it will correctly deploy, contain all required resources, and not need your “expertise” in following a deployment guide. 

Important Things to Note!

1.       We don’t recommend modifying the reports and their stored procedures directly due to the fact that upgrading to future versions of Service Manager could potentially overwrite your changes (SQL supports altering an entire stored procedure, for example, so if you change one line of it and later we need to change a different line, your change isn’t preserved).

2.       To avoid conflicting changes we recommend you create custom reports and corresponding stored procedures.

Step 1: Make sure Woodgrove.ChangeRequest.Extension deployed successfully to the warehouse

Before we can start customizing the List of Change Request report, we need to ensure the Woodgrove.ChangeRequest.Extension management pack has completed importing to Service Manager, synchronized to Service Manager Data Warehouse via the MPSync job, and deployed successfully. For more on MPSync see Data Warehouse – Anatomy of Management Pack Synchronization.  

To determine the management pack has deployed successfully, we can click on the Data Warehouse wunderbar -> Management Packs, type the name Woodgrove.ChangeRequest.Extension in the search box at the top of the page (it says Filter), and we can see the deployment status of this MP. If it’s completed, the MP deployed successfully.

      image

 

Once the Woodgrove.ChangeRequest.Extension MP deployed successfully, the view which represents the ChangeRequest dimension (changeRequestDimvw) will be updated automatically to include Cost Center.

image

Once the ChangeRequestDimvw is updated with the new property Cost center, when choose a change request, we will be able to filter on this property from the Dimension picker automatically without any modification of reports.

Please see the Dimension Picker screenshot for Change Request.

Before the deployment of Woodgrove.ChangeRequest.Extension:

image

After the deployment of Woodgrove.ChangeRequest.Extension:

image

Step 2: How to customize the stored procedure to retrieve Const Center data for List of Change Requests

On the SQL server that hosts the Service Manager Data Warehouse Datamart

1.       Open Microsoft SQL Server Management Studio,

2.       Go to Databases -> DWDataMart -> Programmability ->Stored Procedures, right click on ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_Install, Script Stored Procedure as >CREATE To > New Query Editor Window

3.       In the Editor Window, modify the procedure name to be Woodgrove_Report_ChangeManagement_SP_GetListOfRFCs_Install and then in the Select clause, add the Cost Center (see the following figure), Click Execute

image

4.       You will find the customized stored procedure is installed in DWDataMart -> Programmability ->Stored Procedures.

Copy the custom stored procedure from the Editor Window and paste it to a file and name this file as Woodgove.Report.ChangeManagement.SP.GetListOfRFCs.Install.sql. This SQL script will get called by the management pack later when we do the deployment. We need to modify this file a little bit to ensure it’s re-runnable (so you can deploy it as many times as you like without getting SQL exceptions). Before the CREATE PROCEDURE statement we’ll add the following piece of code:

-- Drop stored procedure if it already exists

IF EXISTS (

  SELECT *

    FROM INFORMATION_SCHEMA.ROUTINES

   WHERE SPECIFIC_SCHEMA = N'dbo'

     AND SPECIFIC_NAME = N'Woodgrove_Report_ChangeManagement_SP_GetListOfRFCs_Install'

)

   DROP PROCEDURE dbo.Woodgrove_Report_ChangeManagement_SP_GetListOfRFCs_Install

GO

And at the end of this file, we append the following: 

GRANT EXECUTE ON dbo.Woodgrove_Report_ChangeManagement_SP_GetListOfCustomRFCs_Install TO reportuser

GO

This Grant Execute command is very important as it ensures that anyone who has rights to run the reports (ie in the reportuser role) can execute the stored procedure. Without this, the report will fail for users who don’t have full access to the data warehouse (and most report users should not!). For the complete stored procedure, please see the attached files.

We also need to create an uninstall script which will be called when the custom management pack is uninstalled (this cleans up database objects after the MP is no longer needed). See the attachment (Woodgrove.Report.ChangeManagement.SP.GetListOfRFCs.Uninstall.sql ), it’s really not that interesting so I won’t include it in the post itself.

These install and uninstall SQL scripts (as well as the RDL we’re about to create) will get bundled up in a single management pack bundle which will facilitate easy deployment.

Step 3: How to customize the List of Change Requests RDL to add Cost Center in the report

1.       Open Internet Explorer

2.       Navigate to http://<report server name>/reports 

3.       Click SystemCenter

4.       Click ServiceManager

5.       Click ServiceManager.Console.Reporting.ChangeManagement 

6.       Click ServiceManager.Report.ChangeManagement.ListOfRFCs

7.       Click the Properties tab

8.       Click Edit in the Report Definition section

9.       Click Save, specify a file location

10.   Open SQL Server Business Intelligence Studio

11.   Click File>New Project

12.   Click Report Server Project, name the project and click OK

13.   Right-click Shared Data Sources in the In the Solution Explorer in the right pane.

14.   Select Add New Data Source

15.   Type DWDataMart in the Name textbox

16.   Click the Edit button next to the Connection String text area

17.   Type <DW server name> in the “Server Name” text box

18.   Select DWDataMart in the “Select or enter a database name” drop down list

19.   Click OK

20.   Right-click Shared Data Sources in the In the Solution Explorer in the right pane.

21.   Select Add New Data Source

22.   Type DWStagingandConfig in the Name textbox

23.   Click the Edit button next to the Connection String text area

24.   Type <DW server name> in the “Server Name” text box

25.   Select DWStagingandConfig in the “Select or enter a database name” drop down list

26.   Click OK

27.   Right-click Reports

28.   Click Add>Existing Item

29.   Select the ServiceManager.Report.ChangeManagement.ListOfRFCs.rdl file you downloaded in step 9 above

30.   Double-click the added ServiceManager.Report.ChangeManagement.ListOfRFCs report in the Solution Explorer pane to open it in Design mode

31.  In the left hand side Report Data pane, click on the DWDataMart, and then double-click the Table_RFC, in the open Dataset Properties window, select the custom stored procedure Woodgrove_Report_ChangeManagement_SP_GetListOfRFCs_Install (which we previously created) from the drop down fields of Select or enter stored procedure name.

image

32.  In the Design pane, right-click on the [Title] > Insert Column > Right, in the empty cell, select the CostCenter from the display list.

33.  Right-click ServiceManager.Report.ChangeManagement.ListOfRFCs.rdl in the Solution Explorer in the right pane, select Rename, and change the name to Woodgrove.Report.ChangeManagement.ListOfRFCs.rdl

34.  Save this project

You could deploy this to Reporting Services server at this time, but we recommend you keep reading a little further and to learn about bundling management packs and for easy, repeatable deployment.

Step 4: How to create a custom management pack

We could create a management pack from scratch or we can use the ServiceManager.ChangeManagement.Report.Library MP which shipped with Service Manager as a template and modify it to meet our needs. (Remember, the final management pack is attached but it’s a great idea to read this to understand how we got there J)  

To get the xml of the ServiceManager.ChangeManagement.Report.Library MP, query the ServiceManager database:

select CONVERT(xml, mpxml) from ManagementPack

where MPName='ServiceManager.ChangeManagement.Report.Library'

Copy the output to a file and name it as Woodgrove.ChangeManagement.Report.Library.xml. Open this file and edit it:

1.       Change the mp Identity and its name to Woodgrove.ChangeManagement.Report.Library (the ID must match the filename)

 <Identity>

      <ID>Woodgrove.ChangeManagement.Report.Library</ID>

      <Version>7.0.5826.0</Version>

 </Identity>

 <Name>Woodgrove Change Management Report Library</Name>

2.       Remove all the sections that are not needed for this custom report:

·         In the <DataWarehouseScripts> node, delete all the nodes except <DataWarehouseScript ID="ServiceManager.Report.ChangeManagement.SP.GetListOfRFCs.Script" Accessibility="Public">

·         In the <Reports> node delete all the nodes except <Report ID="ServiceManager.Report.ChangeManagement.ListOfRFCs" Accessibility="Public" Visible="true" Target="Change!System.WorkItem.ChangeRequest">

·         In the <DisplayStrings> node, delete all the display strings for the other reports

·         In the <Resources> node, only keep the resources for the GetListOfRFCs, delete all the other resources

3.       Next we’ll need to modify some of the management pack elements

·         To change the folder where the report will get deployed to, delete all the <Folder> elements under <Folders> and add the following.

<Presentation>

    <Folders>

      <Folder ID="Woodgrove.Console.Reporting.ChangeManagement" Accessibility="Public" ParentFolder="SMReport!ServiceManager" />

    </Folders>

    <FolderItems>

      <FolderItem ElementID="Woodgrove.Report.ChangeManagement.ListOfRFCs" ID="ChangeManagement.FolderItem.1" Folder="Woodgrove.Console.Reporting.ChangeManagement" />

    </FolderItems>  

Note that any folder in Reporting Services under /SystemCenter/ServiceManager will automatically show up in the console. The ParentFolder above (SMReport!ServiceManager) references this root folder behind the scenes, but you can deploy RDL files there directly if you’d like.

·         Change the Report ID and all corresponding display strings (many of them)

<Report ID="Woodgrove.Report.ChangeManagement.ListOfRFCs" Accessibility="Public" Visible="true" Target="Change!System.WorkItem.ChangeRequest">

...

<DisplayString ElementID="Woodgrove.Report.ChangeManagement.ListOfRFCs" SubElementID="RFCName">

          <Name>Name</Name

</DisplayString> 

·         Change the resource binding files to the custom stored procedures (from step 1) and RDL (from step 2)

<Resources>

<Resource ID="ServiceManager.Report.ChangeManagement.SP.GetListOfRFCs.Install"   Accessibility="Public"  FileName="Woodgrove.Report.ChangeManagement.SP.GetListOfRFCs.Install.sql" HasNullStream="false" />

<Resource ID="ServiceManager.Report.ChangeManagement.SP.GetListOfRFCs.Uninstall" Accessibility="Public" FileName="Woodgrove.Report.ChangeManagement.SP.GetListOfRFCs.Uninstall.sql" HasNullStream="false" />   

<ReportResource  ID="ServiceManager.Report.ChangeManagement.ListOfRFCs.ReportRdl" Accessibility="Public" FileName="Woodgrove.Report.ChangeManagement.ListOfRFCs.rdl" HasNullStream="false"   MIMEType="application/octet-stream" /> 

</Resources>  

·         Update the display strings for the modified elements

<DisplayString ElementID="Woodgrove.ChangeManagement.Report.Library">

    <Name>Woodgrove Change Management Report Library</Name>

    <Description />

 </DisplayString>

 <DisplayString ElementID="Woodgrove.Console.Reporting.ChangeManagement">

    <Name>Woodgrove Change Management</Name>

    <Description>Contains Woodgrove Change Management reports</Description>

</DisplayString>  

·         Add one mp reference to <References> node

<Reference Alias="ChangeReport">

    <ID>ServiceManager.ChangeManagement.Report.Library</ID>

    <Version>7.0.5826.0</Version>

    <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>

</Reference>

...

<DataWarehouseScript>ChangeReport!ServiceManager.Report.ChangeManagement.SP.ReportOptimizations.Script</DataWarehouseScript>   

Save the file after the changes. If you got this far, pat yourself on the back…we’re almost done.  

Step 5: Seal and bundle the customized RDL and Stored procedure into the custom management pack

Remember, management packs get imported into Service Manager CMDB via the console or powershell, then pulled over into the warehouse via the MPSync job (Management Pack Synchronization). Use fastseal.exe downloaded from Sealing Management Packs blog to seal this mp. To generate the key pair, please see How to: Create a Public/Private Key Pair . After sealing the management pack, we get Woodgrove.ChangeManagement.Report.Library.mp.  

There are several methods you can use to bundle a management pack file with its resources, such as using the New-MPBFile script for Windows PowerShell.  You can learn more and download this script from the Introducing Management Pack Bundles blog post. For convenience, The file fastseal.exe and  New-MPBFile.ps1 are attached to this blog.

To make the management pack bundle you should put all the resources into the same folder…let’s create c:\custom reports and copy the following files to this folder:

·         Woodgrove.Report.ChangeManagement.SP.GetListOfRFCs.Install.sql(from step 2),

·         Woodgrove.Report.ChangeManagement.SP.GetListOfRFCs.Uninstall.sql (from step 2),

·         Woodgrove.Report.ChangeManagement.ListOfRFCs.rdl (from step 3),

·         Woodgrove.ChangeManagement.Report.Library.mp (from step 4),

·         New-MPBFile.ps1 (attached file)

Now fire up the Powershell console and type (on one line):

.\New-MPBFile.ps1 .\Woodgrove.ChangeManagement.Report.Library.mp  woodgroveReport

woodgroveReport is the name of management pack bundle.

It should look like:

image

Step 6: Import the management pack bundle

From the console you can import the woodgroveReport.mpb management pack bundle from the Administration workspace or you can use powershell cmdlet like:

   Add-PSSnapin smcmdletsnapin

 Import-SCSMManagementPack  woodgroveReport.mpb

Note that we should always import management pack (bundle) into Service Manager instead of Service Manager Data Warehouse.

Once imported and deployment complete, you’ll see a new report folder in the Report workspace called Woodgrove Change Management and it will contain the report we just developed. The followings are some screenshots of the new custom report.

image

image

You can download the custom rdl, extension mp .xml formatting file, report custom mp .xml formatting file, sql files for report,  fastSeal.exe application, New-MPBFile.ps1 powershell script from here:

http://cid-17faa48294add53f.skydrive.live.com/self.aspx/.Public/DW%20and%20Reporting/WoodgroveCustomReport.zip

Attachment: dimension.png
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • holy crap that's a lot of work to create a report

  • I get errors running the "New-MPBFile.ps1":

    New-Object : Exception calling ".ctor" with "2" argument(s): "XSD verification failed for the management pack

    , Position: 0]"

    At C:\Users\anonymous\Desktop\CustomIncidentReport\New-MPBFile.ps1:117 char:24

    +     $theMP = new-object <<<<  $MPTYPE $mpfilepath,$EMG

       + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodInvocationException

       + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

    VERBOSE: Adding MP:

    Exception calling "AddManagementPack" with "1" argument(s): "Value cannot be null.

    Parameter name: managementPack"

    At C:\Users\anonymous\Desktop\CustomIncidentReport\New-MPBFile.ps1:119 char:30

    +     $BUNDLE.AddManagementPack <<<< ($theMP)

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Invoke" with "2" argument(s): "Non-static method requires a target."

    At C:\Users\anonymous\Desktop\CustomIncidentReport\New-MPBFile.ps1:50 char:26

    +     $genericMethod.Invoke <<<< ($object,$parameters)

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : DotNetMethodException

    Exception calling "Write" with "2" argument(s): "package"

    At C:\Users\anonymous\Desktop\CustomIncidentReport\New-MPBFile.ps1:137 char:35

    + $mpbfullpath = $bundleWriter.Write <<<< ($BUNDLE,$mpbname)

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : DotNetMethodException

  • The error you see indicate that your management pack has syntax error - XSD verification failed.

  • So if I want to add a custom report to the Service Manager Cosnole, what do I need to do?

    1. Simply upload the RDL into the Repoer Manager website?

    2. Or manually do all the steps in this article?

    Because I have tried to fight my way thru this article just to finaly realize that maybe I don't even have to spend 2 hours of my life trying to clear out what Travis was trying to say, and to correct display strings in the managemenet pack for hours.

  • @anonymous - What Miao is describing in this article is how to package a report in an MP so that it is transportable as part of the MP.  If all you want to do is add a report to SQL RS and have it show up in the console all you have to do is add the report to SRS somewhere under the Service Manager folder.

    Something more like this:

    blogs.technet.com/.../displaying-non-service-manager-reports-in-service-manager.aspx

    but in this case the report would actually be querying the views in the DWDataMart DB not some other financial system.

    Really the only people that should be packaging reports in MPs like this are ISV partners.  There is no need for a customer to go through all this MP packaging hassle to get their reports on the SRS web site.

  • Hey, if my extension class contains a property "List" type ( enumeration ) .. how can I include it into my report? .. thank you

  • Hi,
    i use a custom sql script in a bundle management pack, the script contains the custom written stored procedure, with a unicode characters within(All the characters are marked like N'Unicode' within the script).
    When the bundle goes to DataWarehouse using the Mpsyncjob, the stored procedure cannot be imported, cause all the unicode characters are showing like '????', hence cannot be recognized...
    Ive used a sql profiler to troubleshoot this issue and it shows that the sp_executesql statement comes with ??? from DW server, so it is the job....
    Any ideas of how could i bundle the SQL script with unicode characters?