By: Siddharth Mehta | Comments (1) | Related: > Reporting Services Parameters
Problem
Many times in a parameterized report, we may come across situations where we need to execute a report using a fixed parameter value depending upon the users group. In such cases, displaying a list of parameters to the user would not make sense. Also a parameterized report can be used as a model to generate different flavors of the same report, varying on the parameter values. In such a case, hiding the parameter value would be the primary design requirement. In this tip we will take at look at how to implement this solution.
Solution
To emulate the problem in question and see the solution in action, we would need a parameterized report. I will go with the assumption that the reader has working knowledge of SSRS and consider it as granted that the reader has already created a parameterized report.
For demonstration purposes, I have a simple table as shown in the below screenshot and a report which displays records based on the value of a City parameter as shown in the next screenshot.
Say our requirement is to show records having the City parameter value "Mumbai", and we do not intend to prompt users to select the parameter values. To implement this, navigate to reports manager, select "Manage" from the options available for the report and move to the "Parameters" tab.
Below the parameters and options are displayed and you can adjust the options available for each parameter. As our requirement is to display records for the parameter value "Mumbai", add this in as the default parameter value for the report. Also check the option to hide the parameter as shown in the below screenshot.
Now try executing this report and you will not get the parameter prompt and the report will execute for the parameter value of "Mumbai". Using parameterized reports with proper security configuration and appropriately tuned parameter values can cater to a lot of common reporting requirements that vary partially based on parameter values.
Next Steps
- Create multiple parameters in a report and try hiding a few of them in the way we did in this tip.
- Try changing the display text of parameters and check the effects in the report.
- This solution should work with SQL 2005 and SQL 2008
- Read these other Reporting Services tips
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
