Bill Ramos, Principal Program Manager, SQL Server Manageability

Up until this point, I’ve walked through step by step how the MDW reports could be recreated to use SQL Server Reporting Services 2008 R2 and edit them in Report Builder 3.0.

At last week’s SQL PASS, I announced in my Wednesday session that I would release the source to the RDL files that I demonstrated.

At the bottom of this post, you’ll find a zip file that contains the RDL files that we converted up to this point. I say we because this was a collaboration with Bart Duncan – author of  Query Hash Statistics, Rachna Agarwal from our Microsoft IT department in Hyderabad India and myself.

Download Query Hash Stats

The first think you will want to do is download and install the SQL Server Query Hash Statistics project on MSDN Code Gallery. This includes a new version of the Query Statistics collection set – the old one – you’ll want to turn off – and a set of reports that can run inside of SSMS as a custom report. I’ve included a slightly modified version of Bart Duncan’s reports that work in SSRS.

May the Source Be With You

You will want to refer back to my post “What are all these reports anyways? – MDW Report Series Part 5” that lists of the MDW reports and there relationship to each other. These reports assume that you have created shared data source called MDW in your Reports manager filder. Just unzip the reports and upload them to your Reporting Services server. Start with the new MdwOverviewCustom.rdl file and you should be good to go.

Here is a list of the reports you will find at the bottom of this posting in MDW RDL Files.zip.

MdwOverviewCustom.rdl – Bart’s new an improved Overview report

mdw_overview.rdl – The original one as part of the blog series

QueryHashStatsReportsDetail.rdl – From Query Hash Stats that includes a query to get the execution plan.

QueryHashStatsReportsTopN.rdl – The new and inproved Query Statistics report from Query Hash Stats. You’ll never want to look back after seeing this report.

server_activity.rdl – From the blog series.

sql_active_requests.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_activity.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_disk_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_memory_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_waits_blocking_chain_detail.rdl – See “What are all these reports anyways? – MDW Report Series Part 5” This report needs some debugging.

sql_waits_blocking_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5” This report needs some debugging.

sql_waits_generic_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

sql_wait_stats_snapshot.rdl – See “What are all these reports anyways? – MDW Report Series Part 5”

Send Your Comments Via Twitter

That’s it!  Have fun with the source. Let me know what you think about them on http://twitter.com and include my @billramo address and the hash tag #MDWReports.

One Little Gotcha

The source reports point to a shared MDW datasource on my old machine at Microsoft. You'll most likely end up with the following error:

• The report server cannot process the report or shared dataset. The shared data source 'MDW' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)

The workaround is pretty simple, create your own MDW shared data source and then after uploading the report, open it up in Report Builder 3.0 or BIDS. Simply go the MDW data source, right click and select properties. Click on the Browse button to find your version of the MDW data source and click OK. Save the report,, and you should be good to go.