SQL Server Reporting Services - Set default value for multi-value report parameter

Question

I have a report in SSRS and one of the parameters I use is Cities. The user can select from a list of cities to pull the report for that location, or multiple locations. My datset is simply a select * from tblCities. When I run the report, I do see that one of the options is "Select All." But, I'm wondering - is there a way I can get this "Select All" option as the default value, so that by default all cities are selected?

And, along those lines, but a separate question - is it possible to make this drop-down optional? I have tried the allow NULLS checkbox, but for multi-value parameters, it seems SSRS does not allow this.

FYI - I have only begun using SSRS; know very little about it.

Ambala

Answer 1

is there a way I can get this "Select All" option as the default value, so that by default all cities are selected?

Yes you can.

1. Open the Report Parameters dialog: Layout tab, right click anywhere on the canvas that is not the report, select Report Parameters
2. Select the parameter (cities in this case), from the list on the left
3. Select the appropriate default setting in the default section, lower righthand corner

One option is where you can statically define a value. IE: =0 or whatever the value is for the Select All option. FYI: I've found that what works in the Visual Studio preview doesn't work when live.

Another option is if the list of cities comes from a stored proc, you order the output of the sproc so Select All is at the top of the list. Then you select the radio button under the static value one (can't remember, not at work to check ATM) - you'll have to select the dataset the sproc is associated with, then the column that the value comes from.

is it possible to make this drop-down optional?

When you say "multi-value", are you actually able to select multiple values from the list? IME, all you get is a drop down & can only select one of the options available.

Allowing null is just an accepted value - the optionality is really handled in the query so that if a sentinel value is provided then the criteria isn't included in the query. IE:

AND (@cities IS NULL OR t.city = @cities)

That's quick & literally dirty. ORs are poor performance.

Turbellarian

In step 3 above, you say "Select the appropriate default setting". But in SSRS/BIDS 2005, there is not an appropriate default setting. Setting the Default values option to 'Non-queried' with a value of '=0' doesn't seem to work. – Variola

Answer 2

Make these changes to the specified report parameter:

In order to have all fields selected, make the [dataset] and [valuefield] at "Available values:" equal to the [dataset] and [valuefield] at "Default Values" (assuming you have a query for determing this)

Unifoliate

Answer 3

In reports when we want to default the multivalue parameter to 'Select All' following are the steps.

1. Open the Report parameter window from the Report menu.
2. Select the Report parameter from the left handside of the window.
3. Select 'Multi-value' checkbox and appropriate 'Available values'.
4. Under default values select 'From Query' radio button'.
5. Select the appropriate 'Dataset'.
6. Select appropriate 'Value Field'.
7. Save the Report and select Preview Tab. You will find all the items selected in the multivalue parameter list and the result displayed for all the selected items.

Hoebart

Answer 4

• Go to the either the Data tab or the Layout tab.
• From the Report menu, select Report Parameters
• Select the desired parameters, in this example, cities
• In the lower right hand region of the screen, set the Default values radio button.
• Set the Dataset and Value field drop down lists to the exact same options as the DataSet and Value field sections from the Available Values settings above.

This assumes that you are using the "From Query" option under "Available values" If you are using the "Non-queried", see the answer by OMG Ponies.

Variola