Creating navigation panel for reports

Recently, I was asked to design a set of reports for a dashboard in SQL Server 2008 which would all have the same report parameters passed to them. Also, the users wanted to switch between the reports frequently and hence needed a navigation panel on the left hand side preferably. The moment I heard about navigation panel on the left side, the first thing that came to my mind was document maps (yes, this is the post that I said I would come up with in my previous post). But then the conservative within me was trying to stifle out this idea saying that it can not be used, and even if you are able to use, it would be a big performance issue as all the reports would have to be rendered within the same report at the same time. Another five minutes of brainstorming and I shut the <beep> out of the conservative in me. Before we start, it is good to know that the main concepts we are going to apply in this post are Document Maps and On Demand Report Processing. So if you are not familiar with these concepts, please go through How to add a Document Map to your reports and On-Demand Report Processing in Reporting Services 2008. Once you are set, follow the below instructions:- 1) Suppose you have 3 reports and you want to have a welcome page with a navigation panel. First, design your home page in the layout of the report. 1 Home Page section  2) Then, make a matrix below and drag & drop a subreport from the toolbox to the cell section of the matrix. Right click on the subreport and select subreport properties. 2 Subreport Properties  3) Select the subreport name that you want to display from the drop down list. 3 Add the subreprot name  Do steps 2 and 3 for each of the report that you need to display in the navigation panel. 4) Right click on each of the tablix, select the tablix properties and tick the ‘add a page break before’ option. Also make sure that there is a dataset name associated with the tablix, else it would throw an error when we preview. Just make a dummy dataset for this purpose if it is not already there. 4 Tablix properties Note that I have deleted the header row of the matrix. Even though this is not necessary, I did it to reduce the spacing between the start of the page and the subreport.  5) Make sure that border lines of the tablix have been made invisible. 6) Select each tablix and press F4 to open the properties panel. Write the name that you want to see in the navigation panel to the DocumentMapLabel property 5 Adding document map label to tablix  7) Now we are all set to preview the report. 6 Home Page  On clicking the first report, we get the following page Order Count by Cat Report  On clicking the second report, we get the following page Rich text report  On clicking the third report, we get the following page Subcat sales by Prod report  This technique can be extended to passing the same report parameters to all the subreports and hence, creating a feeling of having a single parameter toolbar. And there would be no nasty green processing symbol also 🙂 P.S. : This report has been done in SQL Server 2008 and even though it can be replicated in SQL Server 2005, it is not advisable unless the reports are very small. The reason is that SSRS 2008 has a new feature called On Demand Report Processing, because of which processing can be delayed until a user navigates to a particular page showing that data region or hitting an expression that references information from those other datasets. Hence, in effect, the time taken to display the report would be equal to the time taken to display the subreport in that page (as there is only one subreport in one page). But in 2005, it would be equal to the sum of the times of all the subreports and the initial load time of the report can become too high. Anyways, it is worth a try in SSRS 2005, and if it works for you, give me a party 😉

Posted by SQLJason


hey good stuff!!! 🙂 very nice!!

In case there are two Subreports
SR1, SR2 – If I click on SR1 – SR1 will be displayed and If I scroll Down after SR1 finishes then SR2 will be displayed. How to Stop this? I mean If i click on SR1 then Only SR1 should be visisble and On finish, Scrolling should not be possible.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.