SSRS - Using a subreport for navigation

SSRS – Using a subreport for navigation

As I have been working on my SQL Server Health Reports for my presentation at SQL Saturday 114 in Vancouver on March 17th, I have changed the way that I do things several times. After attending a SSRS class “SSRS Beyond the Basics ” at SQL Saturday 108 in Redmond presented by @sqlbelle I learned a few tricks to help make this happen.

In a nutshell the SQL Server Health Reports project is a set of 11 reports to monitor some of the problems that creep into SQL Servers over time. They all worked fine independent of each other, but I wanted them all to function as one application. To do this I started building links from one report to another, then discovered that for each report that I add, I had to go back and edit all the previous reports to get them to link. So here is the solution.

First I removed the headers from the old reports. I discovered that you can’t put a subreport into a report header, so I just removed all the headers from the existing reports.

Then I set up a new report called Header.rdl shown here. At first it looks a bit odd with the blue “Change Database: fields overlapping other fields, I will get to that soon.

Then I added 4 parameters to the header.rdl.

Database Name Parameter

The first parameter called Database Name is used and passed from report to report to keep the currently selected database around so that you don’t have to continually select the database name every time you switch reports.

ReportName Parameter

Used to show the large font at the top of the report with the name of that report.

ReportPath Parameter

The path to use to link back to the current report after changing the database.

Server Parameter

The name of the SQL Server that we are connecting to. Currently the process only supports localhost, but I am working on expanding this.

Then in the header I implemented the ability to switch databases, which is very useful when browsing database metrics. Next to the “Database: master” there is a + which indicates that clicking it will expand something.

Then I added a section that lists all of the databases on the currently selected server. Which is initially not visible, but when you click the + it is shown, and it is shown over the current database name.

All of the database names are shown as links. Clicking one of those links changes the @DatabaseName parameter and links back to the current report @ReportPath with the required parameters. This is all accomplished on the action dialog on the settings page for each item in the list.

So clicking the link changes the database used in the report.

Next adding in the buttons with the action to switch between different reports. The buttons are just text boxes colored to sort of look like buttons. I added several gray buttons across the top of the report (yes I know that they need some UI work, gray buttons aren’t very interesting).

One button for each report in the project, with an action linking to that report passing along the required parameters.

And the action for each button looks like this for the DB Sizes button which links to the Database sizes report:

Then I added the header into each of the reports, and the the parameter for it.

From there it was just a matter of testing the linking between each of the reports, fixing any typo’s or other parameter errors, and getting it running.

Overall it took about an hour to set it all up and get it tested, but once it is set up it is now much easier to add a new report with a new link. To add this into a new report it takes about 1 minute to insert the sub-report and set the parameters for it.

Next step for another day, setting up a new SSRS template with a standard RDL for this project to save time for any new report that I add.

If you want to see it in action, or see the code behind it, download my SQL Server Health Reports project and take a look. I hope you find this useful in your SSRS endeavors.