Allow user to select a parameter from drop down OR enter it manually

Question

Hello, 

I am trying to develop a report that will have 1 parameter, however, I would like the users to be able to either select this parameters value from a drop-down list, OR enter it manually if they wish to (but not do both).  Here is a screenshot of what I am trying to accomplish.  

So the user should be able to select a site from the drop down, or if they know which site they want to run the report for, they should be able to simply enter it into the text box and view the report.  I know how to get the 2 different styles of parameters to work individually, but how can I get them to work in the same report, using one OR the other?

Thank you.

Answer 1

create two parameters.

one simple parameter: for example..

select sitename from table where sitename = @sitename;

this will create text parameter. so user can write a site name in the box.

for second, create another parameter.for example:

select sitename from table where sitename IN (@sitename)

this will allow you the list box to choose multiple values.

now in second parameter's properties, set the property ALLOW MULTIPLE VALUES enabled.

and then you are good to go for selecting sitenames from the list as well can write a sitename in the box.

---

Regards, Chirag Patel (ETL Engineer @C-S-America)

Answer 2

Hi Treymendous,

Based on your description, you want to allow user to select a parameter value from drop-down list or enter a parameter value. In order to achieve you requirement, we can use an expression to decide which parameter value is used to filter report data.

For example, I have three fields in my dataset which are CalendarYear, BusinessType and SalesAmount. I want to filter my report data depends on the CalendarYear. In order to achieve this, please refer to the steps as follows:

1. Create a new dataset to provide values for the “SelectYear”parameter by using the following query:
   

SELECT NULL AS calendaryear  
    UNION
   SELECT DISTINCT CalendarYear
   FROM DimDate
   ORDER BY CalendarYear

2. Add the “InputYear” parameter to the report, input “InputYear” in both “Name” and “Prompt” properties. Then click the “Allow null value”checkbox.
3. Add a “Filter” in the dataset to filter report data. Set the filter like this:
    Expression: [CalendarYear]
    Operator: =
    Value: =IIF(IsNothing(Parameters!SelectYear.Value),Parameters!InputYear.Value, Parameters!SelectYear.Value)
(This expression is used to decide which parameter value is used to filter data)

After preview the report, if we select the “Null” checkbox, we can select the available value from the drop-down list of the “SelectYear” parameter to filter the report data; if we select the “Null” available value in “SelectYear” parameter and clear the “Null” checkbox, we can manually enter a parameter value to filter report data.

Furthermore, if we select a valid value for “SelectYear” parameter and enter a value for “Input” parameter, we still get report data filter by "SelectYear" parameter. To workaround this issue, we can use an expression to control the visibility of report item, and display prompt message by using a textbox. Please take the following expression as reference:
Using the expression for report item:

=IIF(IIF(isnothing(Parameters!SelectYear.Value),true,IIF((Parameters!SelectYear.Value=Parameters!EnterYear.Value),true,false)),false,true)

Using the expression for the textbox:

=IIF(IIF(isnothing(Parameters!SelectYear.Value),true,IIF((Parameters!SelectYear.Value=Parameters!EnterYear.Value),true,false)),true,false)

Regards,
Fanny Liu

---

Fanny Liu

TechNet Community Support

Answer 3

Hello and thanks for everyone's suggestions.  I was able to accomplish what I needed.  What I did was the following:

• In my dataset that I am using to populate the drop-down list for sites, I included a blank value
• I created another dataset that only returns a single blank value
• In the properties of my site parameter (for the drop-down box), I set it to allow blank values, set the available values to pull from my dataset in #1, and set the default values to pull from the dataset in #2 (so the default value will be blank)
• In the properties of my other site parameter (for the text box), I also set it to allow blank value, set available values to none, and set default values to no default value
• In my dataset that I'm using to actually display the report data, I used an expression on the site parameter to tell the report that if there is a value in the text box, use it, otherwise, use the drop-down parameter.  If the user doesn't use either parameter, the report will just display the text I have in the NoRowsMessage property of the tablix.

What this did for me was when I run the report now, both the drop-down box and the text box parameters are emtpy (which is what I wanted).  I can now choose a value from the drop down, while leaving the text box empty, and I can run the report.  I can then type a value into my text box and run it also.  If I want to choose a different value in the drop-down and re-run the report, I simply delete anything in the text box so it is empty and choose a new value in the drop-down, and the report runs.

Thanks!

Trey