How to get your Incident logs from the Data Warehouse into your report?

So, you’re copying your incident action logs, analyst comment logs and user comment logs to the Data Warehouse (What?! You’re not? Then see this first).

Great, but how do you actually get these into a report and also in the correct order? Read on…

The above picture is an addition to the end of the standard Incident Detail Report. When creating new reports for Service Manager, it is easiest to copy an existing report that closely matches what you need.

Note – You should always work on a copy of a report and also on copies of any relevant Stored Procedures.

This way, you’ll not break the out-of-the-box functionality plus you’ll always have working reports and SPs to go back to if you mess up your new ones. If this is not possible or practical, then you should at least store unedited versions of the report rdl files, data sources and the stored procedures somewhere safe.

Having said this, to keep things simple, I am just going to show you how to edit the existing report

Therefore, please download a copy of your rdl first and save it somewhere so you can revert back if you need to.

Also, you’ll be adding a new Stored Procedure and not changing an existing one.

I’m assuming that your Data Warehouse database is called DWDataMart. If it is not, you’ll need to replace your database name in the instructions below and also change the SP code to “USE” the correct database.

I normally use SQL Server Business Intelligence Development Studio (SQL BIDS) to create and edit my reports, but, again for simplicity’s sake, I am going to use Report Builder 3.0.

Firstly, you need to create the new Stored Procedure:

Download the Stored Procedure text

This stored proc uses a temporary table to store all of the 3 log classes so they can be sorted into date order later.

If your Data Warehouse database is not called DWDataMart, change the USE statement on the first line.

Next, open SQL Server Management Studio (SSMS) and open a new query window on the DWDataMart database. Copy and paste the SP text you just downloaded into the query window and hit F5 to run and create a new Stored Procedure called “ServiceManager_Report_IncidentManagement_SP_GetIncidentLogs”.

Now you’ll need to grant permissions to run this SP to the ReportUser user. Open another new query window (or clear and reuse the existing one) and execute:

grant execute on ServiceManager_Report_IncidentManagement_SP_GetIncidentLogs to ReportUser

If you have setup additional report security, please grant any additional permissons that are required.

To edit the Incident Detail Report, browse to your SQL Server Reporting Services URL (for example, http://yourserver/reports). Don’t do this on your SQL Server! Why? It uses too many resources that are better used by SQL.

Then navigate your way down to the incident reports and right-click the detail report and select “Edit in Report Builder”.

If this the first time you have done this, allow Report Builder 3.0 to download and install.

On the Insert tab click Table\Table Wizard.

Select the option to Create a new dataset and click Next.

Select DWDataMart as the data source and click Next. Select credentials to connect to the data source (this does not update the data source). Either specify the password for the current stored credentials or use Windows Authentication if you have rights to do so.

Expand dbo\Stored Procedures, scroll down and find “ServiceManager_Report_IncidentManagement_SP_GetIncidentLogs” and tick it.

Order the columns up and down to your liking.

For the parameter IncidentId, just enter a 1 for now and click next.

Select the 3 available fields and drop them onto the Values area. Click Next and Next. Choose a style, slate is similar to the others but you can tweak later.

Click Finish.

Your new table will probably end up hovering somewhere near the top, so move it down to the bottom or wherever you want it. Widen the columns so you see the column headers and match the table width to your report. Change the column headers to say what you want.

Double-click your new data set on the right to edit it. Change the name if you like. Click on Parameters and set the value to use IncidentDimKey:

Confusion Alert! I called the parameter IncidentId but actually it is IncidentDimKey that needs passing

Lastly, this process has probably added an unwanted report parameter for IncidentId, check and remove it if needed:

Save your updated report. That’s it! Run the report and you should now see your action logs.

For a more advanced look at creating a new report check out this Microsoft post:
Woodgrove Report Example

Lastly, check through all of the various Stored Procedures in the DWDataMart database under “Programmabilty\Stored Procedures”. These will tell you how to do pretty much anything you need to create a new report, they are a great starting point.