Authoring Friday

Every friday, adventures in System Center authoring

Creating custom reports in SCSM

Hello,

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.

 image

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]

image

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.

image

The SELECT section in the query is updated to include the additional values.

image

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%’)

image

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

image

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:

image

In the WHERE section of the SP you can remove all parameter information, you own filters can stay in the SP.

image

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)

image

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.

image

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.

image

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.

image

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.

image

Both files are now on the report server and the job is done.

image

Report in SCSM console (restart your SCSM console if the report is not directly appearing in the folder)

image

All “default” report parameters are included in the report

image

Report created and working!

image

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.

Stored Procedure

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.

image

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.

image

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.

Have Fun!!

Kurt Van Hoecke

1 Comment “Creating custom reports in SCSM”

  1. Hi – I tried modifying the stored procedure based on your instructions and get this message when I try to execute after modifying the WHERE portion:
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 106
    Incorrect syntax near ‘ActivityStatusDSStatusDS’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 197
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 216
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 216
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 217
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 217
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 218
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 218
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 219
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 219
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 220
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 220
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 221
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 221
    Incorrect syntax near ‘)’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 227
    Incorrect syntax near ‘=’.
    Msg 102, Level 15, State 1, Procedure ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities, Line 232
    Incorrect syntax near ‘)’.

    Any assistance would be appreciated. I am include the query that I modified below. Not a DBA and do not have one to bounce this off of.

    USE [DWDataMart]
    GO

    /****** Object: StoredProcedure [dbo].[ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities] Script Date: 10/28/2013 12:23:28 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[ServiceManager_Report_ChangeManagement_SP_GetListOfRFCs_WithActivities]
    @AssignedTo int = -1,
    @CreatedBy int = -1,
    @DateFilter nvarchar(256) = null,
    @StartDate datetime,
    @EndDate datetime,
    @ID nvarchar(max) = null,
    @Description nvarchar(max) = null,
    @Status nvarchar(max) = null,
    @Priority nvarchar(max) = null,
    @Impact nvarchar(max) = null,
    @Risk nvarchar(max) = null,
    @Category nvarchar(max) = null,
    @ChangeResult nvarchar(max) = null,
    @RelatedCIs nvarchar(max) = null,
    @LangCode nvarchar(15) = ‘ENU’,
    @ScheduledStartFrom datetime,
    @ScheduledStartTo datetime,
    @ScheduledEndFrom datetime,
    @ScheduledEndTo datetime,
    @ActualStartFrom datetime,
    @ActualStartTo datetime,
    @ActualEndFrom datetime,
    @ActualEndTo datetime,
    @IncludeDeleted bit = 0
    AS
    BEGIN
    SET NOCOUNT ON

    SET @EndDate = DateAdd(DAY, 1, @EndDate)
    SET @ScheduledStartTo = DateAdd(DAY, 1, @ScheduledStartTo)
    SET @ScheduledEndTo = DateAdd(DAY, 1, @ScheduledEndTo)
    SET @ActualEndTo = DateAdd(DAY, 1, @ActualEndTo)
    SET @ActualStartTo = DateAdd(DAY, 1, @ActualStartTo)

    DECLARE @Error int
    DECLARE @ExecError int

    DECLARE @tableID TABLE (value nvarchar(256))
    INSERT @tableID (value)
    SELECT * FROM dbo.fn_CSVToTableString(ISNULL(@ID,”))

    DECLARE @tableStatus TABLE (value nvarchar(256))
    INSERT @tableStatus (value)
    SELECT * FROM dbo.fn_CSVToTableInt(@Status)

    DECLARE @tablePriority TABLE (value nvarchar(256))
    INSERT @tablePriority (value)
    SELECT * FROM dbo.fn_CSVToTableInt(@Priority)

    DECLARE @tableImpact TABLE (value nvarchar(256))
    INSERT @tableImpact (value)
    SELECT * FROM dbo.fn_CSVToTableInt(@Impact)

    DECLARE @tableRisk TABLE (value nvarchar(256))
    INSERT @tableRisk (value)
    SELECT * FROM dbo.fn_CSVToTableInt(@Risk)

    DECLARE @tableCategory TABLE (value nvarchar(256))
    INSERT @tableCategory (value)
    SELECT * FROM dbo.fn_CSVToTableInt(@Category)

    DECLARE @tableChangeResult TABLE (value nvarchar(256))
    INSERT @tableChangeResult (value)
    SELECT * FROM dbo.fn_CSVToTableInt(@ChangeResult)

    DECLARE @tableRelatedCIs TABLE (value nvarchar(256))
    INSERT @tableRelatedCIs (value)
    SELECT * FROM dbo.fn_CSVToTableInt(@RelatedCIs)

    SELECT DISTINCT
    C.CreatedDate,
    C.ScheduledStartDate,
    C.ScheduledEndDate,
    C.ActualStartDate,
    C.ActualEndDate,
    C.Id,
    C.Title,
    C.ChangeRequestDimKey,
    C.Category,

    ISNULL(StatusEnumDS.DisplayName, StatusEnum.ChangeStatusValue) AS Status,
    StatusEnum.ID AS StatusValue,

    ISNULL(CategoryEnumDS.DisplayName, CategoryEnum.ChangeCategoryValue) AS CategoryValue,

    ISNULL(CreatedByUserDim.DisplayName, CreatedByUserDim.UserName) AS CreatedByUser,

    CreatedByUserDim.UserDimKey AS CreatedByUserId,

    ISNULL(AssignedToUserDim.DisplayName, AssignedToUserDim.UserName) AS AssignedToUser,

    AssignedToUserDim.UserDimKey AS AssignedToUserId,

    ContainsActivity.ID As ActivityID,

    ContainsActivity.Title AS ActivityTitle,

    ContainsActivity.Description,

    ActivityType.Displayname as Type

    ActivityStatusDSStatusDS.Displayname as ActivityStatus

    FROM
    dbo.ChangeRequestDimvw C

    INNER JOIN
    dbo.WorkItemDimvw WI ON
    WI.EntityDimKey = C.EntityDimKey

    LEFT OUTER JOIN
    dbo.WorkItemContainsActivityFactvw as WICAF on
    WI.WorkItemDimKey = WICAF.WorkItemDimKey
    and WICAF.DeletedDate is null

    LEFT OUTER JOIN
    dbo.ActivityDimvw AS ContainsActivity on
    WICAF.WorkitemContainsActivity_ActivityDIMkey = ContainsActivity.ActivityDimKey

    INNER JOIN dbo.EntityManagedTypeFactvw EntityofType
    on EntityofType.EntityDimKey = ContainsActivity.EntityDimKey

    Inner Join dbo.ManagedTypeDimvw EntityType
    on EntityType.ManagedTypeDimKey = EntityofType.ManagedTypeDimKey

    LEFT OUTER JOIN DisplayStringDimvw ActivityType
    on Activitytype.BaseManagedEntityId = EntityType.BaseManagedEntityId
    and ActivityType.LanguageCode = @LangCode

    LEFT OUTER JOIN dbo.ActivityStatusvw ActivityStatusenum
    on ActivityStatusenum.ActivityStatusId = ContainsActivity.Status_ActivityStatusId

    LEFT OUTER JOIN
    dbo.DisplayStringDimvw Activitiystatusdsstatusds
    on ActivityStatusenum.EnumTypeId = Activitiystatusdsstatusds.BaseManagedEntityId
    and Activitiystatusdsstatusds.LanguageCode = @LangCode

    LEFT OUTER JOIN
    dbo.WorkItemAssignedToUserFactvw WIATU ON
    WI.WorkItemDimKey = WIATU.WorkItemDimKey AND (@IncludeDeleted = 1 OR WIATU.DeletedDate IS NULL)

    LEFT OUTER JOIN
    dbo.UserDimvw AS AssignedToUserDim ON
    WIATU.WorkItemAssignedToUser_UserDimKey = AssignedToUserDim.UserDimKey

    LEFT OUTER JOIN
    dbo.WorkItemCreatedByUserFactvw WICBU ON
    WI.WorkItemDimKey = WICBU.WorkItemDimKey AND (@IncludeDeleted = 1 OR WICBU.DeletedDate IS NULL)

    LEFT OUTER JOIN
    dbo.UserDimvw AS CreatedByUserDim ON
    WICBU.WorkItemCreatedByUser_UserDimKey = CreatedByUserDim.UserDimKey

    LEFT OUTER JOIN
    dbo.WorkItemAboutConfigItemFactvw AS WIACI ON
    WIACI.WorkItemDimKey = WI.WorkItemDimKey AND (@IncludeDeleted = 1 OR WIACI.DeletedDate IS NULL)

    LEFT OUTER JOIN
    dbo.ChangeStatusvw AS StatusEnum ON
    StatusEnum.ChangeStatusId = C.Status_ChangeStatusId

    LEFT OUTER JOIN
    dbo.DisplayStringDimvw AS StatusEnumDS ON
    StatusEnum.EnumTypeId = StatusEnumDS.BaseManagedEntityId AND StatusEnumDS.LanguageCode = @LangCode

    LEFT OUTER JOIN
    dbo.ChangePriorityvw AS PriorityEnum ON
    PriorityEnum.ChangePriorityId = C.Priority_ChangePriorityId

    LEFT OUTER JOIN
    dbo.DisplayStringDimvw AS PriorityEnumDS ON
    PriorityEnum.EnumTypeId = PriorityEnumDS.BaseManagedEntityId AND PriorityEnumDS.LanguageCode = @LangCode

    LEFT OUTER JOIN
    dbo.ChangeImpactvw AS ImpactEnum ON
    ImpactEnum.ChangeImpactId = C.Impact_ChangeImpactId

    LEFT OUTER JOIN
    dbo.DisplayStringDimvw AS ImpactEnumDS ON
    ImpactEnum.EnumTypeId = ImpactEnumDS.BaseManagedEntityId AND ImpactEnumDS.LanguageCode = @LangCode

    LEFT OUTER JOIN
    dbo.ChangeCategoryvw AS CategoryEnum ON
    CategoryEnum.ChangeCategoryId = C.Category_ChangeCategoryId

    LEFT OUTER JOIN
    dbo.DisplayStringDimvw AS CategoryEnumDS ON
    CategoryEnum.EnumTypeId = CategoryEnumDS.BaseManagedEntityId AND CategoryEnumDS.LanguageCode = @LangCode

    WHERE
    ActivityType.DisplayName NOT LIKE (‘%Extension%’)
    (
    (@ID IS NULL) OR (C.Id IN (Select value from @tableID))
    ) AND
    (@StartDate = @StartDate) AND (C.CreatedDate = @ScheduledStartFrom)) AND
    ((@ScheduledStartTo IS NULL) OR (C.ScheduledStartDate = @ScheduledEndFrom)) AND
    ((@ScheduledEndTo IS NULL) OR (C.ScheduledEndDate = @ActualStartFrom)) AND
    ((@ActualStartTo IS NULL) OR (C.ActualStartDate = @ActualEndFrom)) AND
    ((@ActualEndTo IS NULL) OR (C.ActualEndDate 0
    )
    = 0
    )
    OR
    (
    WIACI.WorkItemAboutConfigItem_ConfigItemDimKey in (select value from @tableRelatedCIs)
    )
    )

    ORDER BY C.Id

    SET @Error = @@ERROR

    QuitError:

    RETURN @Error
    END

    GO

    Comment by Rob — October 28, 2013 @ 20:00

RSS feed for comments on this post. TrackBack URL

Leave a Response