SQL Server Thoughts from the Field

Rob's SQL Server & BI Blog

Hosting the Performance Dashboard Reports in SSRS

Hosting the Performance Dashboard Reports in SSRS

  • Comments 8
  • Likes

I blogged a while back about modifying the SQL Server 2005 Performance Dashboard reports to run on SQL Server 2008. I’ve since been working with several customers who use these reports for performance troubleshooting, but who would like to host them on their Reporting Services platform so they can be viewed online instead of within SQL Server Management Studio. So over the past few days I’ve been killing time on flights and trains doing just that. So here goes…

It’s actually very simple to get the reports up and running on Reporting Services. All you have to do is install the Performance Dashboard reports, create a new Report Server project in BIDS (or Visual Studio) and import the .rdl files from the directory they were installed to (right-click the solution name in Solution Explorer –> Add –> Existing Item).

Capture1

They use a shared data source, so you need to update this to point to the SQL Server instance you want to monitor (making sure that you have enabled the instance for use with the Performance Dashboard reports first), deploy the reports and data source to your Report Server and you’re ready to roll.

Capture2

However, this means that you can only look at one server. To monitor multiple servers, you would would need to repeat the process and host a separate copy of the reports for each one… not very scalable !

The solution I’ve come up with (and which you can download in the attached .zip file) requires you to publish the reports only once and use a parameter to dynamically determine which server we want to connect to in our data source. This parameter is set in the Performance_Dashboard_Main.rdl file when you first launch the report and is used as an expression in the report data source to dynamically build the connection string.

Capture5

Capture3

We need to modify the existing data source to be an embedded data source as using expressions in connection strings is not supported with shared data sources.

Capture4

Capture6

This parameter is then passed through to subsequent linked reports to build the dynamic data source connection for those reports as well.

Capture7

I’ve created 2 versions of the Performance_Dashboard_Main report (Performance_Dashboard_Main.rdl and Performance_Dashboard_Main_CMS.rdl) which I’ve included in the attached solution file. The first one uses a free-text field to enter the server name and the second one uses the new Central Management Server (CMS) functionality in SQL Server 2008 to dynamically populate a drop-down list of servers you have already registered on your CMS… very cool !

These reports have been designed and tested to work on SSRS 2008, however you can use the same technique to host them on SSRS 2005 or 2008. The data source for the reports can also either point to SQL Server 2005 or 2008 instances (as long as you’ve followed the instructions for modifying the reports for SQL Server 2008 first). As I’ve mentioned before, these reports are not a replacement for the fantastic new Management Data Warehouse functionality of SQL Server 2008, but can provide another valuable tool to help DBA’s analyse performance issues.

Download the reports, have a play and let me know what you think !

Attachment: Performance Dashboard Reports.zip
Comments
  • Hi Rob,

    Great tip. I do not see in my BIDS in SQL Server 2005 SE the option to modify existing shared data source into an embedded data source, the option embedded connection does not exist in the data source properties. A limitation in Standard Edition?

  • Hi Rob,

    I could not open performance_dashboard_main.rdl or cms version in BIDS 2008, it throws the following error 'Deserialization failed: Requested value 'Integrated' was not found', full error at the end

    It is getting stuck in the data source part part of the rdl, data source rds file was not included in your zip file.

     <DataSources>

       <DataSource Name="DataSource1">

         <ConnectionProperties>

           <DataProvider>SQL</DataProvider>

           <ConnectString>="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=master"</ConnectString>

           <IntegratedSecurity>true</IntegratedSecurity>

         </ConnectionProperties>

         <rd:SecurityType>Integrated</rd:SecurityType>

         <rd:DataSourceID>a8081d97-2893-4445-b0db-614213592923</rd:DataSourceID>

       </DataSource>

     </DataSources>

    Error thrown

    <html><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8"></head><body><DIV class="DetailsStyle" width="100%"><table border="0" width="100%" id="table1" height="100%" cellspacing="0" cellpadding="0"><tr><td height="40" bgcolor="#FF9999" width="8%" style="letter-spacing: 0" align="center"><img border="0" src="C:\Users\somebody\AppData\Local\Temp\tmpF4F7.tmp" width="35" height="35"></td><td height="40" bgcolor="#FF9999" width="91%" style="vertical-align: top; letter-spacing: 0" align="left"><b><font size="2" face="Arial" fontsize="70%">One or more errors encountered while loading the designer.  The errors are listed below.</font><font size="2" face="Arial"></font></b></td></tr><tr><td colspan="2" align="left" valign="top"><DIV id="div1" class="ErrorStyle" width="100%"><span style="font-weight: 600"><br>Deserialization failed: Requested value 'Integrated' was not found. Line 2696, position 36. </span><span style="font-weight: 400"><p></p><div a href="" id="task0" style="cursor: hand; text-decoration:underline; color=#0000FF">Edit code</a><p></p></div></span></DIV></td></tr></table></div></body></html>

  • A big thank you!!

  • thanks Rob.. !!

  • I am pointing to machine A to access the published report and in the ServerName field, I point to B and I got this error? What am I missing here?

       An error has occurred during report processing. (rsProcessingAborted)

           Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)

               For more information about this error navigate to the report server on the local server machine, or enable remote errors

  • I tried to do this for the 2012 Performance Dashboard reports but could only get the main to work, it wouldn't run any of the sub reports.

    Anyone got this working yet?

  • in

    select @ts_now = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info

    to:

       select @ts_now = cpu_ticks / convert(float, (cpu_ticks/ms_ticks)) from sys.dm_os_sys_info

    or:

       select @ts_now = ms_ticks from sys.dm_os_sys_info

    blogs.technet.com/.../performance-dashboard-reports-for-sql-server-2008.aspx

  • Nick

    In the sub report change the datasource name default is datasource1, change it some name like MyDataSource

    then in the Report Server; Report DataSource ; A custom  datasource, ; Credentials stored securely in the report server ;  add your custom credentials.  

    This should fix sub report issue

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment