Creating Custom Service Manager Reports

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.

image

image

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

image

image

image

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.

image

image

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.

image

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

image

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.

image

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.

image

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.

image

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

image

image

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

image

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

image

The groups can be expanded to get the Incident details.

image

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.

image

image

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.

image

image

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 Smile

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

regards
Marcel

This entry was posted in SCSM and tagged , , , , , , , , . Bookmark the permalink.

26 Responses to Creating Custom Service Manager Reports

  1. Great way to get started with your own custom reports, thanks for this article! :)

  2. Annie says:

    Hello Marcel,
    We followed your tutorial to get the custom report displays and runs in Service Manger 2010 console.
    We wanted to put the custom report in the same location as those reports that came with Service Manger, but we couldn’t find them in our SSRS. Could you provide help?
    By the way your tutorials are great! We learned a lot from them.
    Thanks,
    Annie

    • Hey Annie

      When saving the report just navigate to one of the default folders and save it there (System Center\Service Manager\Subfolder). The report will then appear correctly together with the default reports.

      regards
      Marcel

  3. Vinh says:

    Hi Marcel,
    Can you provide suggestions on how to set up the parameters for the custom reports?
    Thank you,
    John

  4. Pingback: Creating Custom Service Manager Reports with Parameters | SCSMfaq.ch

  5. Annie says:

    Marcel,

    Could you please provide help?

    Do you know why my report column is showing like this?

    IncidentStatusEnum.Active
    IncidentStatusEnum.Closed
    IncidentStatusEnum.Resolved

    How do you remove “IncidentStatusEnum” and keep “Active” etc.. on the report.

    Thanks,
    Annie

    • Hey

      You need to use a join with the table/view that contains the strings. Unfortunately I dont have an example at hand, but check the database for that table/view and get the correct string from there.

      regards
      Marcel

    • Tom Aguero says:

      I worked with our DBA to fix this up. Just add this to the select statement

      Replace(Classification.[IncidentClassificationValue], ‘IncidentClassificationEnum.’, ”) as ‘Classification’

      Well, replace Classification with Status or whatever column you’re working with.

  6. Pingback: SCOM 2012 – Custom AEM Reports | SCOMfaq.ch

  7. Sandeep says:

    Great Article,
    I have screwed up “List of Incidents” in SCSM 2012reports by using Report Builder. All other reports are working fine except this. Now if I run this report, I got an error saying

    Exception has been thrown by the target of an invocation.
    at System.RuntimeMethodHandle._InvokeConstructor(Object[J args, SignatureStrict& signature, IntPtr declaringType)
    at System.Reflection.Runbmeconstructorlnfo.Invoke(Binding9ags invokeAttr, Binder binder, Object[] parameters,
    Culturelnfo culture)
    at Microsoft.EnterpriseManagernentReporting.Pararneters. Parameter5lockEditorsetbngs.CreateEdìtorControl
    (EnterpriseReportParameterlnfoCollection reportParameters)
    at Microsoft.ErterpriseManagernent.Reporting.Viewer.ParameterßlockControl.InitializePararneterßlock
    (IPararr eter5lockprovider pararn eterBlockProvider)
    at Microsoft. ErterpriseJ’ar agement. Reporting .Viewer.ParameterßlockCon:rol. .ctor(IParanieterßlockProvider
    parameterßlockProvider, SenierReport serqerReporz, EnterpnseRepoxz enterpriseReport, Object context)
    at
    parameterBlockProvider, En:erpriseReport report, Object context)
    at
    (Object serer, DependencyPrcpertyCharçecEventArgs e)

    Can you please guide me.

    • Hey

      If you just want to recover the default report definition, I would just copy it over from another SCSM Management Group.

      Cheers
      Marcel

      • Sandeep says:

        Thanks Marcel for assisting me .
        Unfortunately, I have only one server. Is that Possible if You can send me “ServiceManager.Report.IncidentManagement.ListOfIncidents.rpdl” file.

      • Sandeep says:

        Hello Marcel,
        Hi, How are you..
        Have you got a chance to find file ?

        Thanks
        Sandeep

  8. Eric says:

    Hello Marcel,
    Clear overview on steps that need to be taken. I’m wondering if it is possible to create the incident report showing / using the “department” of the affected user.

    • Hey

      That’s of course possible as you have the incident and user information in the DWH. Try playing around with the Report Builder and you will be successful :) Unfortunately I do not have an example for that …

      Cheers
      Marcel

      • Eric says:

        Many thanks, I will give this a try and browse through the available items in the data set. I couldn’t see it at first and thought it had to do with ‘department’ being an AD attribute of the user and not being stored with the incident.
        Thanks again,
        Eric

  9. Radoslav says:

    Hello Marcel , many thanks for your blog about SCSM ! Its main source of info for my test project.
    I’m a little newbie in SQL and want to ask because i cannot find it with report builder : Is it possible to create a report with option to sort /display from custom attribute (for examle Customer) that have relationship to incident class ?
    Thank yoy for answer in advance ..

    • Hey

      Sure, you can build (almost) anything with Report Builder :) You should be able to achieve this by inserting your record set into a table and use the sorting option there.

      Cheers
      Marcel

  10. Sonia says:

    Thank you for the great article! I was able to create the report, however, none of the users can run it through SCSM console. The error is :
    Message:
    System.Exception: An error has occurred during report processing. —> System.Exception: Cannot create a connection to data source ‘DataSource1′. —> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors
    — End of inner exception stack trace —
    — End of inner exception stack trace —
    at Microsoft.Reporting.WinForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension)
    at Microsoft.Reporting.WinForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension)
    at Microsoft.Reporting.WinForms.ProcessingThread.ProcessThreadMain(Object arg)
    System.Exception: Cannot create a connection to data source ‘DataSource1′. —> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors
    — End of inner exception stack trace —
    System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors

  11. Pingback: Create a report for “Support Group Changes” | marcelzehner.ch

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s