When you are working with Service Manager, you have some report definitions that are delivered with Management Packs for basic reporting. With SM12, the concept of OLAP Cubes is another method that you can use for getting information out of the data warehouse by slicing and dicing data. This is indeed a much easier approach, but it also has downsides. Without any configuration, cubes are processed on a daily basis, and the data they contain is not real time data. Depending on the exact needs, this can be a show stopper. And maybe the delivered cubes do not contain the data you are looking for. Cusomizing this is even harder that creating reports. So thanks to classical reporting, we have the possibility to create reports in “near real time” without investing too much time. Another positive aspect is, that these reports can be scheduled an delivered automatically. But what if you want to use classical reporting but are not satisfied with the delivered reports? In this post I will show you how to create custom reports from scratch.

I’m pretty sure almost anyone out there that deals with Service Manager is not too happy when it comes to reporting. Getting relevant data in a usable form out of the CMDB is essential, but the requirements differ from company to company. The reports that are delivered with Service Manager are OK, but not very flexible. So most of the time you will have to create your own reports or at least modify an existing one. But how can this be done? What’s the approach? In this post I will give a quick overview and will show one way reports can be built and delivered.

The first question always is, what tools can be used. Actually you can use any available SQL report designer. For this example, I used SQL 2008 R2 Report Builder 3.0. I like this tool because it’s very handy and not too complex, but offers the most important functions to create nice reports.

Step 1 – The Data Source

The basic of every report is some data source, so when you start the tool to create a new report you need to define the data source. There are 2 types of data sources available:

• Embedded data sources

This is a report-specific data source that only lives inside your report. That means, it is not shared and can only be used by your report. Just select the SQL Server name and the instance, then select the SQL database.

• Shared data sources

If you have multiple reports that use the same data source, this is the way to go. Because the data source is defined once and can be re-used in multiple reports, you can save time. The data source configuration is then stored on a Reporting Services server or a SharePoint server can be access by multiple reports. The reports that are delivered with Service Manager are using a shared data source. That means that a data source configuration for accessing the Data Mart is already available and we only have to connect to it.

Step 2 – The Data Set

The configured data source will contain much data that will never be used in your new report. By using queries, you can pull the needed data out of the configured data source and make it available for the report. Again, you can choose to use embedded or shared data sets. The principle is almost the same as for data sources. I will use an embedded data set in this example because they are not re-used as much as data sources are.

The SQL query gets the exact information out of the database and can later be used in the report. You can create your SQL queries directly in Management Studio and then transfer them to the data set configuration in the Report Builder. As you can see, in this very simple example I get all Incidents from the database and select id, title and status attributes.

Step 3 – Design the Report

When the data set is ready we are ready for the fun part: report design! Report Builder brings lots of options to present data in different forms, simple tables, cool gauges, charts etc. I select a Matrix. This is a more complex table that also allows grouping of data.

The data set that holds the data we want to display needs to be selected.

Now the fields from the data set can be dragged to the correct place. I want to have a grouped view of my Incidents based on the status, so I drag the status field to the row. Then I want to see the ID and Title in the details, so I drag those to the value area.

After two more simple selections the report is ready and displayed in the designer. The headers are automatically added and can be edited if needed.

You can also add more information if needed. In this example I want to have the total count of incidents of every status. So I add a column and configure an expression in the light blue (grouped) field.

I select “Count” and then specify the ID field to count on.

Now I repeat the steps for the dark blue field (total).

Now I’m ready for a test run. On the top left I select “run”. After some seconds the result should be displayed.

The groups can be expanded to get the Incident details.

Step 4 – Deploy the Report

When the report is ready, it can be deployed to make it available to others. When saving the report you can choose to save it as a file or directly on a SQL Reporting Services server. I select the SSRS option with the correct URL to save the report to the same location where the other Service Manager reports are living.

Step 5 – Use the Report

The report is now accessible from the Service Manager console. Just switch to the reporting area and look for your new report. The select and run it.

The report in this example is not very helpful nor has it a great look, but gives you an idea about the possibilities. With this procedure you are able to create reports for every kind of data that exists in the data warehouse. Start playing around and within a short time you will be ready to impress your boss with great reports

Update: Check out how to use Report parameters in this post!

regards
Marcel