It is possible to create custom reports, using SQL Server 2008 Reporting Services, against the System Center 2007 R2 data warehouse.
Reports can be designed in SQL Server 2008 Reporting Services to provide the end user with a better representation of the data since the built-in System Center reports are not ideal.
Fortunately the OperationsManagerDW database schema is documented on MSDN http://technet.microsoft.com/en-us/library/gg508713.aspx along with code samples which makes the task of creating a custom report a little easier.
Stored procedures were implemented to return the data from the OperationsManagerDW schema. I prefer this approach as it provides more control over the code rather than embedding logic in the report itself. Incidentally, the stored procedures were hosted in a separate database to avoid support issues embedding these in OperationsManagerDW database.
An example of the stored procedure logic to query the OperationsManagerDW is below:
The design time report is shown below. The line graph shows specific counter values over the period of a month e.g. processor utilisation, web service requests etc. whereas the tablix controls display critical events, uptime, hits and so on for each server in the farm.
A datetime parameter was implemented in order to allow the user to select a date from the calendar control
The uptime field displays the result of a custom counter which is the number of seconds since the service was started. I implemented a vb function (shown below) on the report body to transform this into days, hours, minutes which is more readable for the end user.
The expression references the custom code using the following syntax:
The Visual Basic function to format the seconds as days, hours, minutes is below:
The report can be executed directly by passing parameters in the URL as documented here http://msdn.microsoft.com/en-us/library/ms155391(v=SQL.100).aspx. This was useful to test the report execution. I used two parameters, an example of this URL structure is shown below:
This can be achieved both in native and SharePoint integrated mode.
The fact that the OperationsManagerDW schema is documented has made the whole process of creating a custom report a lot easier and the flexibility of Reporting Services provides a better representation of the data to the end-user.