Pages

Monday, April 19, 2010

Report selection in parameter toolbar

The MSDN forum has been the inspiration for most of my blogs, but in all those cases, I was sure that I could attain the end result and my blogs were usually a graphical step by step depiction of what needs to be done, so that I could post it as an answer to the question. But this blog is quite different, I almost wrote a no saying that it is not possible. And that was when I decided to give it a try before replying No :)

To give a background of the problem, the user wants to display the list of reports in a dropdown list and on pressing the view report button, the selected report must be displayed. On reading this question, I started typing – no, it is not supported by design in SSRS and suddenly I thought whether I am doing any value-addition to the post by typing this answer, I might as well as not write it and wait for some expert to confirm it. So I just thought of trying it while some expert gives his answer, and by chance, I stumbled upon a workaround.

To implement this, follow the steps below:-

1) Create a report parameter (say RP) and in the report parameter properties, specify the default values. The labels should contain the report names and in the values, use integers as shown in the figure below

RP Properties

2) Create a matrix in the layout and for each report add a row. In this example there are 3 reports hence add 3 rows.

3) For each of the row, drag and drop a subreport  from the toolbox into the data cell. Then right click and select the subreport properties.

Subreport properties

In the General tab, select the required report from the dropdown list which contains the list of reports (as shown in the figure above). Press OK.

4) Once you have modified the properties of all the subreports, select the entire row and right click on it.

Row visibility

Select Row Visibility option.

5) Select the Show or hide based on an expression radio button

Setting row visibility expression

For the first row, enter the following expression:-

=iif(Parameters!RP.Value="1",False,True)

This expression will ensure that the first row will only display if the parameter value is 1, else the row will be hidden.

6) Repeat steps 4 and 5 for each row. Substitute the number 1 by the appropriate value in the expression specified in the 5th step.

7) In the matrix column header, use the below expression to display the name of the report

=Parameters!RP.label

8) Save and deploy the reports. Now you can preview the report in internet explorer, and when you select the report name from the parameter dropdown and click on view report button, the appropriate report will be displayed.

order count by product

Image visibility

Rich text

This technique can be extended to pass other report parameters also by passing the required parameters to the subreports. Don’t you think I deserve a pat on the back for this? ;)

4 comments:

  1. U deserve a pat on ur back...for sure.
    article is too good..:)

    ReplyDelete
  2. Excellent... I admire the idea

    ReplyDelete
  3. Thanks, Jason. This is the exact solution I was looking for. This part of my project will complete in 1hr instead of 1 day!

    I nominate you for Greek Hero of the Day!

    ReplyDelete