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

System-Center-Team · ‎Feb 15 2019

First published on TECHNET on Apr 23, 2010

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:

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

</Identity>

<Name> Woodgrove ChangeRequest Extension </Name>

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

</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>

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.

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

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:

After the deployment of Woodgrove.ChangeRequest.Extension:

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

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. Un install.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 .

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)

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

</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.

</Folders>

</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)

...

<Name>Name</Name

</DisplayString>

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

</Resources>

· Update the display strings for the modified elements

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

</DisplayString>

<Name>Woodgrove Change Management</Name>

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

</DisplayString>

· Add one mp reference to <References> node

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

</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:

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.

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/WoodgroveC...

dimension.png

Products (50)

Special Topics (27)

Video Hub (462)

Most Active Hubs

Most Active Hubs

Video Hub

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