Monday, February 8, 2010

Creating Dashboards Using SQL Server Reporting Services 2008.

I have found that SSRS 2008 is great for creating dashboards so we can view what is happening in SCOM at all times. We have mounted 4 23” monitors with dashboards that can cycle pages to help us know what is going on in our SCOM environment at all times. The dashboards run against the warehouse and are pretty much real time.

To get this right I used SSRS 2008 embedded in an inline frame and then run IE in Kiosk mode. If you want to check this out below is an example of SSRS Dashboard that you can download.
The file contains everything mentioned in this blog.

Just change the Shared Data source to point to your OperationsManagerDW and publish it. You can do this by double clicking on DataSource1.rds under Shared Data Sources in the Solution Explorer.

If you can’t see the Report Data window on the left click on “View” and select “Report Data”. Then if you right click on DataSet1 or Num Alerts and select query you can view the SQL used.

The example dashboard I have provided displays a graph that shows the alert trends over the last 48 hours. It also shows the top 5 alerting Rules in the last hour. It also offers the ability to drill into things to get more detail. You can click on the graph to get detail about that point in time and click on the gauges to get more detail. I find it useful for letting me know what is currently happening in SCOM.

Once you have your reports working you are going to want to strip some SSRS stuff out like the toolbar. You also want to ensure that the reports refresh properly and do not used a cached page. You are also going to want to strip out the scroll bars which I do using an inline frame. The IFrame is also my way of being able to add java script and custom HTML to SSRS pages.

To strip out the SSRS toolbar use the following in your URL: rc:Toolbar=false

To stop caching use: rs:ClearSession=true

I have provided an example an HTML file, called AlertsDashboard.html, that will call the URL’s in an inline frame, strip out the unwanted tools bars, refresh them properly and cycle through them. I also call a page that acts as a screen saver. You could publish the AlertsDashboard.html page to an IIS server or just run it from a share like I do.

You will need to modify the AlertsDashboard.html file so it points to your Report Server. Find the following code in the file and replace the highlighted code with you SSRS Server Name.

dashboards: [
{url: "http://YourReportServer/ReportServer/Pages/ReportViewer.aspx?%2fSCOM+Dashboard%2fAlerts+Dashboard&rs:Command=Render&rc:Toolbar=false&rs:ClearSession=true", time: 600},
{url: "file:///C:/SCOMSites/ScreenSaver.htm", time: 60}
{url: "http://YourReportServer/ReportServer/Pages/ReportViewer.aspx?%2fSCOM+Dashboard%2fAlerts+Dashboard&rs:Command=Render&rc:Toolbar=false&rs:ClearSession=true", time: 600}
The time specified after the URLS is the time to wait in seconds before calling the next page.

Finally to load the dashboard, create a shortcut that points to the html file something like this:

"C:\Program Files\Internet Explorer\iexplore.exe" -k "C:\SCOMSites\AlertsDashboard.html"

The –K opens up IE in Kiosk Mode. If you have multiple monitors and want to move IE windows around so you have one on each, use “Shift”, “Windows Key” and left arrow to move the IE windows to the various monitors.

It’s as easy as that.

1 comment: