Basic reporting in SCOM

This is the part three of a set of articles related to SCOM

Part1 - SCOM BASICS

Part2 - Useful SQL queries in SCOM database

Basic reports in SCOM

There are two main ways to edit or develop a report for SCOM: via Report Builder or via Visual Studio. 

In this article we are going to use Visual Studio 2010 with bussines inteligence tools installed.       


Creation of a new project

Firstly we have to create a new proyect in Visual Studio, to do it, you can use a wizard if you want to or start with a plain report template. In this example we are going to use the wizard.

File--> New project --> select bussines inteligence templates -->Report Server Project Wizard
And finally you have to fill the project name, location and solution name and then click OK.

The wizard starts and ask you for the data source, you have to click on edit connection string and provide a user and location of SCOM datawarehouse. User with read permissions is enough to develop a report.

Click NEXT and the wizard will ask you for one query, the report is going to be built from this query, for example, we are going to use this query which displays all modified alerts by specific user.

 select ars.alertguid, alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, priority, managedentityrowID, repeatcount   
 from Alert.vAlertResolutionState ars   
 inner join Alert.vAlert alt on ars.alertguid = alt.alertguid   
 where statesetbyuserid like '%olopez%'   
 order by statesetdatetime  

Click NEXT and now wizard will ask if you want to create a tabular or matrix report.

This quotation is an extract of MSDN, about differences between tables and matrix in this context:

Tables, matrices, and lists are data regions that display report data in cells that are organized into rows and columns. The cells typically contain text data such as text, dates, and numbers but they can also contain gauges, charts, or report items such as images. Collectively, tables, matrices, and lists are frequently referred to as tablix data regions.
The table, matrix, and list templates are built on the tablix data region, which is a flexible grid that can display data in cells. In the table and matrix templates, cells are organized into rows and columns. Because templates are variations of the underlying generic tablix data region, you can display can display data in combination of template formats and change the table, matrix, or list on to include the features of another data region as you develop your report. For example, if you add a table and find it does not serve your needs, you can add column groups to make the table a matrix.
The table and matrix data regions can display complex data relationships by including nested tables, matrices, lists, charts and gauges. Tables and matrices have a tabular layout and their data comes from a single dataset, built on a single data source. The key difference between tables and matrices is that tables can include only row groups, where as matrices have row groups and column groups.
Lists are a little different. They support a free-layout that and can include multiple peer tables or matrices, each using data from a different dataset. Lists can also be used for forms, such as invoices.
The following pictures show simple reports with a table, matrix, or list.

The following steps of the wizard will ask you about report design and how you want to display the rows provided by the dataset.

Finally, last click before finishing the wizard, will ask you about the SSRS location. You can fill these fields now or in the future, we usually do it when we have the report project finished.

Now, click FINISH and you have your first report, but it doesn´t look very useful or powerful, the best way to improve it, is to start to play with report objects.

What does the wizard do?
The wizard basically creates a Datasouce with connection string, Dataset with the query that we provided and tabular matrix with the results of the query.
We are going to explain in this image what the wizard does and what we have to do if we start with a blank project.

Datasource is where you select which databases and connections strings are used by report, you can have multiple datasources from multiple SCOM databases or other different databases.

Dataset contains the resultant data of one query. We are going to use this data in the report objects, like tables, charts, text fields, maps, etc:

  

In the propierties of project you can select which SRSS you are going to use to deploy the report and which folder:

Finally there is another important tab, it is Toolbox, which contains a set of objects that we can use in a report, such as tables, maps, images, matrix, etc:

How to create a basic table

To create a basic table and fill it with the Dataset query, you have to:

• Drag and drop the control over the report (in this case table object).
• Select the table object.
• Do a right click over the left top corner of the table. 
• Select tablix propierties from context menu.

In the tablix propierties you have to select the dataset which is going to provide data to the table, in this case Dataset. Click ACCEPT and return to the report design view.

Now to populate the table with the data of the query :

• Put the mouse over the first field of the second row.
• Click over the icon that appears.
• Select the database field that you want to display in this field.

Automatically the first row is updated with the field description of database column. You can modify the headers of the table if you want.

Now you can click to preview the results before deploying the report in the server.

How to plot a basic chart
Plotting a chart with SSRS is very easy, you just have to follow these steps:

• Drag and drop chart object
• Select chart type in the example lines
• Set a dataset to the chart by clicking on the propierties of the chart (right button)
• Double click over the chart and you will get an extra panel

• Set Values which are the vaules of the dots that conform the line of graph. For example an average data from a performance counter.
• Set Category group. This is X values of the chart, for example a date field.
• Series Group contains the name of the data which is displayed on the chart, for example the machine name

It´s time to click in the preview and get the results before deploying the report in the server.

Parameters
At some point of the reports deployment you may need parameters in your reports. To include parameters in the reports, you have to create a new parameter in the report data tab.

Modify the query in the dataset. 
To modify the query in the dataset you have to click over Fx button on the main propierties of dataset and do something like this:

 ="select ars.alertguid, alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, priority, managedentityrowID, repeatcount    
  from Alert.vAlertResolutionState ars    
  inner join Alert.vAlert alt on ars.alertguid = alt.alertguid    
  where statesetbyuserid like '%" & Parameters!username.Value & "%'    
  order by statesetdatetime"  

Here is a extended explanation about how parameters work in SSRS:
http://blogs.msdn.com/b/sqlforum/archive/2011/05/03/walkthrough-control-dataset-query-dynamically-based-on-ssrs-report-parameters.aspx
Here is another way to use parameters in reports:
http://technet.microsoft.com/en-us/library/ff714047(v=sql.105).aspx

Deployment
If you have configured your SSRS server in the report propierties(context menu), deploy it with just a click on Deploy.
This will ask you for creditials to access to the SSRS, you will need write permissions in the destination folder.

Others
The use of formulas and filters in the datasets and tables is very powerful. We strongly recommend you take a moment and try them out.