Blog Post

Printing the Report Parameters on SSRS

,

 

I'm surprised there isn't an option in the Report Manager for allowing you to include on the print out of the report the report parameters you used. If there is, someone please set me straight.
Quick background, this report I was working on is on SQL 2012, I'm not using Sharepoint, and it's using an analysis services cube data source. It has 13 report parameters: a date parameter and 12 multi-value parameters. A lot of my users only read printed versions of the reports that someone runs for them. I needed a way to print the report parameters that were used but not print the parameters that were left alone (i.e. the default "All" was selected). I printed the values selected using the Parameters Collection References and a join statement, and controlled the visibility of each row with an IIf statement, checking for All as the first selection. Here's what I did to achieve this:

First, to print multiple values from the parameter I used this expression in a table field:
="Customer: " + Join(Parameters!Customer.Label, ", ")
The Join statement gives you a comma separated list of the Customers selected. The string "Customer: " at the beginning is just a label. Noticed that I used Customer.Label and not .Value.

Second, to control the visibility of this field I used the following IIf statement.
=IIf(Parameters!Customer.Label(0)="All",True,False)
If the user doesn't change the default value of a parameter it's going to have "All" selected, on my report anyway. And if All is selected the first label of the Parameter will be "All" so I can test for Label(0) equal to "All". And if it is I set the Hidden property of this row to True. And if not, I set the hidden property to False. Why bother with this? Because I don't want to print 300 customer account numbers at the top of the report.

Third, I needed to give this section of the table a label that shows when I have parameters to display and not show when I don't. In other words, if the user doesn't filter by anything, I don't want the "Report Filters" label at the top of the report. To do this I used a SWITCH statement in the expression that controls the Visibility of this field. SWITCH is the same as a case statement. The syntax is like this:
=SWITCH(
Parameters!Customer.Label<>"All",False,
Parameters!Item.Label<>"All",False,
True,True)
I had twelve conditions to test for but I've shortened it for this example down to two. The True, True at the end is the Else statement you would use in your typical case logic. True always evaluates to True. So if none of the proceeding statements evaluate to true it will use this last value, the second "True", which hides the field.
This site has a good intro to the SWITCH statement.http://www.sql3d.com/archive/2011/01/avoid-nested-iifs-statements-in-reporting-services-expressions-by-using-the-switch-function/
It's what I found as a reference today anyway.

Well that's it. As always, if you were trying to do something similar, and google led you here, and finding this post saves you time, I'm glad I took the time to write and post. Cheers.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating