Hiding parameters and using default parameter values in SSRS reports

By:   |   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.

 the reader has working knowledge of SSRS and consider it as granted that the reader has already created a parameterized report

select "Manage" from the options available for the report and move to the "Parameters" tab

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.

check the option to hide the parameter

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.

this solution should work with SQL 2005 and SQL 2008

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Siddharth Mehta 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



Comments For This Article




Friday, January 24, 2014 - 10:35:57 AM - Saravan kumar Back To Top (28216)

May i know how to open Report manager?















get free sql tips
agree to terms