Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Hiding parameters and using default parameter values in SSRS reports


By:   |   Last Updated: 2011-01-19   |   Comments (1)   |   Related Tips: > 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


Last Updated: 2011-01-19


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

May i know how to open Report manager?


Learn more about SQL Server tools