Pages

Tuesday, May 18, 2010

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 ;)

2 comments:

  1. hey good stuff!!! :) very nice!!

    ReplyDelete
  2. --------------
    http://bantalsilikon01.blogspot.com/2014/10/rekomendasi-untuk-cloud-dedicated.html
    http://kopiluwakliar01.blogspot.com/2014/07/kopi-luwak-liar-arabica-dari-dataran.html
    http://vvty.in/uncategorized/bantal-silikon-185-000-free-ongkir-seluruh-nusantara/
    ---------------
    http://marinirseo.blogspot.com/2014/10/perumahan-baru-di-tangerang-graha-raya.html
    http://bantalsilikon01.blogspot.com/2014/10/perumahan-baru-di-tangerang-graha-raya.html
    http://bantalsilikon01.blogspot.com/2014/11/bantal-silikon.html
    http://bantalsilikon01.blogspot.com/
    http://marinir-seo.blogspot.com/2014/10/marinir-seo.html
    http://kopiluwakliar01.blogspot.com/2014/10/perumahan-baru-di-tangerang-graha-raya.html

    lkmnprapto@gmail.com
    http://vvty.in/http://vvty.in/http://vvty.in/
    http://vvty.in/http://vvty.in/http://vvty.in/
    http://marinir-seo-konsultan-12a.blogspot.com/2014/10/aaa.html
    http://asdf-14.blogspot.com/2014/10/hhh.html
    http://vvtiin.blogspot.com/2014/10/iii.html
    http://marinir-seo-8.blogspot.com/2014/10/jjj.html
    http://bantaljkt1.blogspot.com/2014/10/sss.html
    http://dfes12.blogspot.com/2014/10/rrr.html
    http://seo-guru01.blogspot.com/2014/10/qqq.html
    http://jksbindo.blogspot.com/2014/10/ppp.html
    http://jkt-49.blogspot.com/2014/10/ooo.html
    http://seokonsultan1.blogspot.com/2014/10/nnn.html
    http://guru-seo-malang.blogspot.com/2014/10/mmm.html
    http://seo-aaa.blogspot.com/2014/10/lll.html
    http://seo-konsultasi-mudah.blogspot.com/2014/10/kkk.html
    http://bantal-silikon15.blogspot.com/2014/10/ggg.html
    http://agen-seo16.blogspot.com/2014/10/fff.html
    http://agen-seo17.blogspot.com/2014/10/eee_30.html
    http://agen-seo18.blogspot.com/2014/10/ddd.html
    http://agen-seo19.blogspot.com/2014/10/ccc.html
    http://agen-seo20.blogspot.com/2014/10/bbb.html
    http://bumbu-pecel01.blogspot.com/2014/10/xxx.html

    bumbupecel1.blogspot.com
    http://bantalasdf.blogspot.com/2014/10/aaa.html
    http://seo-malang-19.blogspot.com/2014/10/bbb.html
    http://agenseomalang18.blogspot.com/2014/10/ccc.html
    http://marinirseo17.blogspot.com/2014/10/ddd.html
    http://bumbupecel16.blogspot.com/2014/10/eee.html
    http://domainhosting15.blogspot.com/2014/10/fff.html
    http://bumbupecel15.blogspot.com/2014/10/ggg.html
    http://marinirseo14.blogspot.com/2014/10/hhh.html
    http://bantalsilikon12a.blogspot.com/2014/10/iii.html
    http://marinirseo12.blogspot.com/2014/10/jjj.html
    http://bumbu-pecel-hz.blogspot.com/2014/10/kkk.html
    http://marinir-seo-001.blogspot.com/2014/10/lll.html
    http://bantal-silikon-murah.blogspot.com/2014/10/mmm.html
    http://vvty-ine.blogspot.com/2014/10/nnn.html
    http://give-a-power.blogspot.com/2014/10/ooo.html
    http://bumbupecel-12.blogspot.com/2014/10/ppp.html
    http://marinir-seo-konsultan.blogspot.com/2014/10/qqq.html
    http://guru-seo-praktisi-123.blogspot.com/2014/10/rrr.html
    http://guru-seminar-online01.blogspot.com/2014/10/sss.html
    http://kopiluwakmalang11.blogspot.com/2014/10/ttt.html

    ReplyDelete