Creating a Custom Report for SCOM 2007 R2 with SQL 2008 reporting in Microsoft Visual Studio 2008

Most of the times we can rely on the more general reporting features delivered with SCOM 2007 R2 for reporting purposes. But when you want to design a really nice looking report which is much easier to generate and target you need to dive into report designer or even the even more flexible Visual Studio which.

Although the approaches may be the same for most steps in Visual Studio 2008 the reports created in Visual Studio are NOT backwards compatible with Sql Reporting Services 2005. Reports designed with visual studio 2008 can only be used if you are running SQL 2008 reporting services! So for SQL reporting 2005 you should visual studio 2005 instead!

Wouldn’t it be nice to design your own reports with nice looking bars or graphs with your company logo on it ?

test

 


Requirements before starting are:

  • Visual Studio 2008 which is delivered with SQL 2008
  • Authoring Console
  • Feeling really artistic!

 

Before we start with opening the Visual Studio console you will first need to determine which counters you want to generate the report on.

The example will create a custom report for reporting on logicaldisk space. Since this is one of the most asked for and is missing from the default reports.


Counter(s) of interest


 

We are going to start with % Free Space. image

This is the information we need to know to be able to collect the counters:

Counter : % Free Space

Object: LogicalDisk

The other ones are also of interest but to start with we need to know the object and counter to be able to setup a dataset to retrieve the information from the Datawarehouse.

 

 


The begin


Open visual studio and follow the steps below to start a new project in which we are going to create the report for disk space.

Go to new and start a new project.
Select a Report Server Project and give it a Name.

image After Creating the project we are going to create a report within this project.
Navigate to Solution Explorer on the right side of the screen.
(if not shown go to view and select Solution Explorer)
Right Click on Reports and select Add New Item…
image
Select Report and give it a Friendly Name and click Add. image Now we have a report in our project.
Next we are going to add an Item to the report.
As stated in the design surface you can add one by using the toolbox on the right or you can right click and select insert.
When you right click and select insert you have the following options you can select.
For our example we are going to select a Matrix.
image When the Matrix is selected we need to define a dataset. This is going to be the query we are going to use to query the OperationsManagerDW.
Give it a friendly name and select New next to Datasource.
image
The Datasource is the Shared datasource the Reporting server uses to connect to the OperationsManagerDW. The Name is “Data Warehouse Main” Unfortunately Visual studio 2008 doesn’t support datasource names with spaces… so we fill in “DataWarehouseMain” instead. We change this afterwards.
Next connection string, simply edit browse your sql server for the OperationsManagerDW.
The connection string to you data warehouse is used to test our reports directly from visual studio.
image After Setting up the datasource we can add the query we are going to use.
For ease click Query Designer…
The Query Designer will open and you need to add your query in the box with SELECT FROM

 
image

Main Dataset Query


image

This is the query we are going to use. The SELECT and FROM part are for collecting the data from the Perf.vPerfDaily view which is the view for Daily data from the OperationsmanagerDW.

TIP! If you change vPerfDaily to vPerfHourly you will retrieve the data per hour!

The WHERE part is the part we are filtering the data on. Notice we are going to filter on date @Start_Date and @End_Date which will be the parameters we are going to define. This way we can tell from which date to which date we want to run the report on.

The data is filtered on LogicalDisk which will only retrieve Data from the Object Logicaldisk.

Last we are also filtering on @ServerName which will also be a Parameter we are going to declare for the servername we want to retrieve the data.

 

Lastly the data is ORDER BY date. This way the data is retrieved in a chronological way.

 

 

 

 

Well after pasting in the query you can run it by pressing “!”.
It will ask for the parameters fill in a start date / end date and server name (FQDN).
The query designer now shows the data and how it collected the data by joining the tables.

How cool is that and you are starting to look like a developer now already with this cool code and models 😉

After looking over your result. You will notice we are retrieving more data then we are actually going to use in this example. No worries you will probably have enough inspiration after designing the report to use the other data as well!

When you are done looking at your developer like screen you can click ok and save your dataset settings.

image We are going to create 1 more dataset and after that declare the parameters.
First the Dataset, let’s call it DataSet_Servers
Use the following query:

Select DISTINCT vManagedEntity.Path
FROM Perf.vPerfDaily AS vPerf INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
Where ObjectName = ‘logicaldisk’

This query retrieves all Server Objects to report on.

 

image
Declaring the parameters in the last thing before we can start with designing the report.
These are our parameters:
@Start_Date
@End_Date
@ServerName

Go to parameters and go their properties.

image  At the parameters properties for @End_Date set the data type to Date/Time on the general tab. Next Default Values tab and select Specify values. This will set the default values for the parameters when opening the report.
For Value click the Function button (fx) and now you are in the expression designer. Go to Common Functions\Date & Time\ and select today (double click).

TIP!Also there are examples how to use this function.You can select each to find what it is used for!

Function for End_Date which should be today
=Today()

Do the same for @Start_Date with function
=DateAdd(“d”,-7,Today())

Finally the @Servers at the General Tab select Multiple values.Next open the Available values tab and select Get Values from Query.
Fill the Dataset we created DataSet_Servers and fill in both Value field and Label field with Path. Simple use the selection to select the dataset and the values.

image
Now we can start with the design drag and drop the values from the report data pane onto the Matrix like on the right.
You can now run a test report to check which results you get.
image When you did test the report you noticed there where many counters and data.
First we going to filter the results since we are interested in the % Free Space for this example.
To filter the results we need to change the Column Group Countername.
Open the group selecting the column and go to the group properties.
Now navigate to Filters. Here we are going to filter our results.
Click Add to add a filter and in expression select [CounterName] use the = sign as Operator and fill in % Free Space.
Select Ok and run the report again to check the results. running the report will tell you how the data is presented and give you a quick solution to be able to visualize what you are actually doing.

After Adding the filter we have narrowed the results down to only % Free Space. Except now the result is calculated all results together.

image
We need to change the results to 1 value which makes sense and probably the most up-to-date one.
Select the cell and right click to open the FX Expression designer again.
Almost every value in the report has a possibility to add expressions! This makes it very flexible!
Now change the expression to the following:
=Round(Last(Fields!AverageValue.Value))

The Round function is used to round the number so it’s easier to read instead of a PI like number 😉

The Last function is used to only retrieve the last value. Which is today because we ordered the query on Date we now this is the last number!

image The result should now look something like the right picture. By adding the filter on the Countername we only see the counter % Free Space.
And by adding the expression in the details part
=Round(Last(Fields!AverageValue.Value)) we have the results shown.
The screen is just from a test environment and the results are probably different but the basics should look the same.
Computer \ Instances running on the computer and free space.
The next steps will draw the graph in a more nicer way.
image

The Designing Part

 


The designing part really depends on your personal favor but I will show you how you can make your report look more sharp in a couple of minutes.

 

Let’s add some more to the report.
Open the toolbox and drag and drop a Gauge onto the drawing area.

Wow aren’t those meters looking sharp!

Select the one you want to use and ok.
The meter will be pasted into your design as a new object.
Simply drag and drop your new meter object into your matrix and let it go in the cell with =Round(Last(Fields!AverageValue.Value))
.

image First let’s go to report properties to check and maybe change the page settings of the report.
The values given here you should pay close attention since when designing you should always make sure you stay within these boundaries. Especially when you want the report to be converted to like.pdf.
When you go over the settings the graph will expand multiple pages which isn’t always nice looking.

If you look at the default values here you will notice the width is 8.5 and margins both right and left 1 in this makes your drawing area 6.5 same goes for the height of your page which would be 9.

Make sure your designing area is within 6.5 and 9!
Simply click on your designing area and go to it’s properties if no properties are shown on the left of the screen click F4 to make them appear. Now change your design area to 6.5 by 9. This way you are always sure you are editing within the page limit!

TIP! When you get blank pages it is most of the times because these boundaries are not set correctly!

image
The matrix should now look something similar. you can expand the matrix now from one point of your designing area to the other and make the gauge more visible.
You can select the different objects which make up the gauge graph. The important one is the bar as shown. For both pointers add the expression
=Round(Last(Fields!AverageValue.Value))
This will show the %free space collected value but now on the bar!!

TIP! when designing the bar first make it look like you want and after this you can edit the size. This makes navigation easier 😉
image After playing around endlessly with colors and settings let’s continue we have a report to make  😉

Let’s make a page header and footer to hold extra information like title company logo execution time etc.
Right click on your design area to add a page header and footer.Here you can add a text box or image.
In the text box you can also add expressions! Or drag and drop report properties to the text box.
This way you can display logos, a title, creation date, etc.of the report.
Save your report as .rdl file and open the SCOM Authoring Console.
image
Create a new Management pack and go to the reporting tap. Create a new report and give it a name.
Next go to the Definition tab and select “Load content from file
Navigate to the .rdl file and import it.
image Now you will see the xml version of your report in the authoring console.
First remove the first line

<?xml version=”1.0″ encoding=”utf-8″?>
After removing this line we need to correct the datasource setting.
Remove the connection settings

<ConnectString>Data Source=.;Initial Catalog=OperationsManagerDW</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>

And add after </rd:DataSourceID> the following <DataSourceReference>Data Warehouse Main</DataSourceReference>.

TIP! Don’t forget to change the options tab the visible setting to True otherwise no report will be shown!!!

Now you can save your report in the management pack. And after this you can import the management pack in your environment.

 

 

image

Result


This post described how to create a report for SQL 2008 reporting with visual studio 2008. Although it takes some time to get the graphical results you want you can now create a custom report.

This report is a simple example of how to create reports using visual studio 2008. I know there are more and possible better solutions and queries to get results but this is for another post 😉

 

 

Have fun with expanding your reporting functionalities and drawing skills!

8 thoughts on “Creating a Custom Report for SCOM 2007 R2 with SQL 2008 reporting in Microsoft Visual Studio 2008

  1. Profile photo of Stefan KoellStefan Koell

    Kudos, Oskar! You saved me a lot of time with your killer-post. I was planning to do a very similar guide for creating reports but honestly, mine would have looked embarrasing compared to yours. You have my vote for the post of the week!

    cheers

  2. Profile photo of Oskar LandmanOskar Landman Post author

    Great minds think alike 😉 Thanks Stefan.

    Will do an update of this one probably next week with a more complex way to create the queries.

  3. Profile photo of Ziemek BorowskiZiemek Borowski

    Select DISTINCT vManagedEntity.Path

    FROM Perf.vPerfDaily AS vPerf INNER JOIN

    vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

    vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

    vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId

    Where ObjectName = ‘logicaldisk’

    impresive method to get computer. Of course req. having SQL 2008 @ OpsMgr reporting. 🙁

  4. Profile photo of Pete ZergerPete Zerger

    Those are some really attractive reports. This is so detailed. I wish I had a couple of days to just work through everything you’ve explained. We should turn this into a pdf…really great stuff Oskar!

  5. Profile photo of Jay EvansJay Evans

    Thank you for the write up. I created my first report, but the report is not showing in the Management Console. I did set the Visible to TRUE, and even set the Accessibility flag to public as well in the Authoring Console. The report still will not show up.

    Under the General tab for the report in the Authoring console, what should be the Target be if that matters? It was not mentioned above so it may not be needed, but I am grasping.

    Edit: Found another solution. Instead of copy/pasting the XML into the Authoring console, I just published the report to SCOM directly from Visual Studio using the deploy feature. I did have to use SQL authentication in the Data Source, but it works great. My first custom report woots!

  6. Profile photo of Javier_DK

    great, post.

    Are there any explaining how to add relative date pickers, etc… with a rpdl file?

  7. Profile photo of Arnaud PENSU

    hi,

    Can you please give us some elements on who to create a new report in Visual Studio 2012 as Authoring Console is now an additionnal composant of VS ?? (in order to Load Content from a file as you do late in this HOW TO).

    i’ve spend many ours, didn’t found nothing, nowhere !!

    i’m not very familiar with Visual Studio.

    Thank’s a lot  in advance.

    BR

Leave a Reply