Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

Posted by mister.magoo on 17 June 2013

Thanks for sharing. Wouldn't this be so much easier if the  Parameters collection was accessible as a collection, though - why Microsoft had to make this so hard is beyond me.

As a side note, you can also use a dataset for this - write a query that you pass all of your parameters and it SELECTs back one row for each parameter that you want to display, then you pop that dataset in a table on the report and hide it if it has zero rows....

SELECT 'Customer' as Title,@Customer as Selection

WHERE @Customer NOT LIKE 'ALL,%'

UNION ALL

SELECT 'Item', @Item

WHERE @Item NOT LIKE 'ALL,%'

etc

Posted by Charles Kincaid on 17 June 2013

I've been putting parameters onto reports since before Crystal Reports.  Largely this was self defense to fend off those "why did this report not include my..." (fill in the blank).  Typically it was because of conflicting parameters being specified.

Your post will make that task much easier.  Great job.

Posted by jshahan on 17 June 2013

I typically only put the simplest parameters (date ranges, etc.) at the top of a report but it is only a matter of time before I get a spec that this post resolves.  

I also appreciated the intro to the JOIN function.  That is going to be very handy as well.  I thank you.

Posted by KWymore on 17 June 2013

Thanks for the article. I've been doing this in SSRS since 2005 or so.  However, recently I got stuck trying to return parameters on a report when using an SSAS datasource. You can still return them with syntax like Parameters!Customer.Value however the output is MDX and is not pretty. I would like to see an article on how to handle this more eloquently.

I agree with others that it doesn't make sense why returning parameter values on a report is still a hack-job. With all the other upgrades performed in 2008 and 2012, this should have been included.

Posted by Steve Turner - SQL Managed on 17 June 2013

KWymore, try Parameters!Customer.Label instead of .Value to get the text visible in the parameter instead of the ugly stuff.

Leave a Comment

Please register or log in to leave a comment.