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
marcelzehner.ch 
Great way to get started with your own custom reports, thanks for this article! 🙂
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
Hi Marcel,
Can you provide suggestions on how to set up the parameters for the custom reports?
Thank you,
John
Hey Vinh
I will publish a post about that shortly …
regards
Marcel
Pingback: Creating Custom Service Manager Reports with Parameters | SCSMfaq.ch
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
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.
Pingback: SCOM 2012 – Custom AEM Reports | SCOMfaq.ch
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
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.
Hello Marcel,
Hi, How are you..
Have you got a chance to find file ?
Thanks
Sandeep
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
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
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
Thank you Marcel
Will play with the builder
Regards
Rado
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
Hey
Can you run the report successfully in the Report Builder?
Cheers
Marcel
Yes I can run them ok.
Are you using an embedded data source or a shared? did you design the report in the production environment or in test/dev?
I was using embedded connection. I just switched it to shared connection and now it works! Thanks you for your quick reply and support 🙂
Pingback: Create a report for “Support Group Changes” | marcelzehner.ch
Hello Marcel. Thanks for the article!
i have to show custom fields (a service request extended class field) in a SCSM 2012 report. Do you know the table/view which contains these fields relationship??
I see these fields descriptions in DisplayStringDimvw but i don’t know how join it with ServiceRequestDimvw or WorkItemDimvw.
Hello!! I have requirements for 2 reports. One to show how many incidents/service request were resolved and re-opened. The other is to show how many went from support group 1 to support group 2. I am not very familiar with database infrastructure of the service manager, so would be great if you could give me some light on how/where to get this data from. Thanks.