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 ?

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.

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.		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…
Select Report and give it a Friendly Name and click Add.		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.		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.
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.		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

Main Dataset Query

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

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)) .		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!
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 ;-)		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.
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.		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.

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!