Creating reports is not an easy task; in System Center Service Manager this task is not simplified, you need to have some SQL reporting knowledge to be able to build them. If we take the deployment of the custom reports via management pack into account it is even more difficult to go through the procedure and successfully deploy the reports.
This blog will explain how you can build custom reports that are based on an existing report of Service Manager. The following steps need to be taken in order to get the custom reports working in the SCSM console, without deploying them via mgmt pack:
- - SQL stored procedure (SP) will need to be adjusted to include the necessary values.
- - Report needs to be adjusted to have the additional columns of information.
- - SQL Stored Procedure and reports need to be deployed.
Next procedure will guide you through the steps to create a custom report. The “list of changes” report will be used as base report for the example report that will be build in this procedure. This report will be adjusted to include all Review Activities that are part of the Change. Let’s start…
Creating/updating the SQL stored procedure
Navigate in the SQL Management Studio to the DWDataMart Database, expand Programmability folder, expand Stored Procedures folder
Right click on the “ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_Install” SP, select Script Stored Procedure as, select CREATE To and select New Query Editor Window.
The query will be opened in a new pane, you need to adjust the name of the SP before you can execute the creation and start modifying the SP. For this procedure the name is adjusted to:
CREATE PROCEDURE [dbo].[ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities]
After changing the name you can start modifying the SP. To get the needed information in the SP you need to update the “SELECT”, “FROM” and “WHERE” sections in the Query. Right click on the new created SP and select Modify. For the example report that will be created we need to have to include review activity information. This code is added in the FROM of the query to get this additional information.
The SELECT section in the query is updated to include the additional values.
In the WHERE section of the query you can filter the information. A filter on the ActivityType can be added to exclude these kind of activity entries in the report. Following code is added in the example query:
ActivityType.DisplayName NOT LIKE (‘%Extension%’)
All changes for the example SP are made and the query can be executed. This will make the changes in the SP, the only thing that need to be done is to make sure that reportuser role in SQL can execute the SP. click on the new created SP, and select Properties.
- - Select Permissions, in the Permissions pane click on Search button
- - Add “reportuser” in the selected objects (check names), click OK
- - Grant Execute rights for the report user
All done for this part.
As you can read, the information you need to add in the query is depended on what you need to present in the report. This example is getting the review activities, another could be to get all Incidents that are related to the Change. The same procedure can be applied to other mgmt function reports. Copy the SP, modify the query and update permissions.
To test your SP you need to remove the parameter information from the query. Click Modify on the SP you have created. In the begin of the SP you need to remove All “@” and DECLARE lines. Your SP should look like this:
In the WHERE section of the SP you can remove all parameter information, you own filters can stay in the SP.
Execute the query in order to update the SP. After this update you should be able to execute and test your SP. Once you have finished testing, you can copy and paste the parameter information back in your SP. Click Modify on the SP, copy the parameter information from your “source” SP and execute the query. In this way your SP includes the needed information for SCSM reporting.
SCSM custom report creation
To create the custom report you can use the same approach. Start from an existing report, adjust the SP and update the column definition to include the needed information. Run through next procedure to create a custom report.
Start Report Builder and open an existing report. Navigate to your reportserver website (http://<YourServer>/ReportServer) and browse to the “source” report. In this example the following reporting is used as starting point for the custom report: ServiceManager.Report.ChangeManagement.ListOfRFCs
In the Report Data pane (left of the Report Builder console), expand Datasets and right click on the Table_RFC dataset, select Dataset Properties.
On the Query page of the Dataset properties update the dataset to point to the new created SP. Select the new SP in the Select or Enter stored procedure name dropdown (ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities)
Click on the Refresh Fields button and click Ok.
The fields in the dataset should be updated with the additional fields defined in the selected SP.
If you haven’t done it yet, save your report with another name on the report server. (The example report is saved as “Changes with Activities list”)
Now you can update the columns in the report. For this report we don’t need all time information, these columns can be deleted. The Activity Id, status and activity type columns need to added. To do this you can right click and select Delete Columns to remove the unneeded columns. Right click on the last column, select Insert Column and click Right to add columns.
In the new column, hover over the white space of the column (an icon should appear in the upper right of the white area) and click on the icon. The list of dataset properties pops-up from where you can select the property to bind in to the report column. Same steps need to be taken to include the other properties from the dataset. Final result for the example report is illustrated below.
Save the report to the SQL report server. At this point we have all SQL reporting configuration done if we want to deploy the report with a management pack. The detailed procedure how to do this is explained here.
The purpose of this blog is to illustrate how you can deploy the custom reports without management pack. The missing part in this solution is the Report Parameter Definition Language (.rpdl) file for the custom report. This is not a difficult part; on the SQL reporting server (in this case the ‘”ListOfRFCs” report) download the .rpdl file from the source report and rename this file to the same name as the new report. Upload the file in the same directory on the reporting server.
Both files are now on the report server and the job is done.
Report in SCSM console (restart your SCSM console if the report is not directly appearing in the folder)
All “default” report parameters are included in the report
Report created and working!
SQL Stored Procedure and report deployment
If you deploy the reports via management pack then you don’t need any additional steps to get the reports working in another environment. All is done via the MP deployment. In this procedure the SP, report and Report Parameter Definition Language (rpdl) file needs to be downloaded and uploaded in the new environment. For example if you are building your reports in a development environment and need to get them in the production environment.
Via SQL management Studio you can create a deployment script. Right click on the SP, select Script Stored Procedure as, Select CREATE TO and click on File… Provide a name for the query and click on Save.
This file can be used to deploy the SP in another environment.
Report and Report Parameter Definition Language file
You can download the files from the reporting server. The report file needs to be adjusted to point to the correct reporting server (all this is done if you deploy via MP. I need to check if we can avoid this step…). Open the report file (rdl) and navigate to the end of the file. Update the ReportServerUrl tag in the file with your reporting server.
Create the SP via the file, upload the report files and custom report is deployed!
Hope this helps creating you own report in SCSM. I like the cube functionality, but in a lot of deployments the people want to have it in a standard report. Following the procedure in this blog you can easily create and update custom reports without the need to delete/import a management pack.
Kurt Van Hoecke