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.

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.