Home > SSRS Expert > SSRS #57 – Alternatives to hide a parameter dynamically

SSRS #57 – Alternatives to hide a parameter dynamically

Hi,

I am making a couple assumptions from your question.

Goal

You’d like to have two parameters.

  • If users select CostCentre from the first parameter, you want to present users with a simple textbox for them to enter a number.
  • If users select CostCentreName from the first parameter, you want to present users with a dropdown list so they will make their choice from the list of available values.
    Another assumption is that you are doing all these on your reporting server (of cause on BIDS as well), not through a URL (such as an URL generated from a ASP page).

An Alternative

I’ll jump right into an alternative, rather than giving you an answer. The truth is I don’t have an answer to hide or show a parameter dynamically in RS.

Although you can set a parameter to either Visible or Hidden, you cannot do so dynamically in Report Manager.

image

Reading through some blogs online, there is good news if you are generating the URL from an ASP page by suppressing parameters in the URL.

Here is an alternative I’d recommend. That is to create two parameters, one drop down list for Cost Centre Names, and one textbox for the Cost Centre Number.

Optionally, you can still keep your parameter where users can choose either “Cost Centre Name” or “Cost Centre Number”. But I don’t think this is a must.

Use T-SQL (or a procedure) to deal with report parameters

What I am really recommending is to either use T-SQL (if the where clause is simple enough) or a SQL procedure to deal with users’ parameter input.

  • If there is an user input in the textbox for the Cost Centre Number, use the user input in the where clause to limit the return dataset to only that cost centre.
  • If there are values passed from the dropdown list, use then to query only those cost centres.
  1. Raghu
    June 17, 2011 at 6:26 am

    Many thanks for your quick reply and your suggestions, i will do so. Thanks again.

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: