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.
- Open the Report Parameters dialog: Layout tab, right click anywhere on the canvas that is not the report, select Report Parameters
- Select the parameter (cities in this case), from the list on the left
- 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.