Select All option with multi-valued parameters (Part 2) - SSRS

Following on from my previous post on the shortcomings of the 'Select All' option with multi-valued parameters, another workaround is to assess whether the user actually Selects All. If so, then deal with this in the main query code.
Firstly, create an internal parameter (called EstabCount in this case) that counts the number of values held in multi-valued parameter list, with the dataset query simply getting a count of values.

The EstabCount parameter is given the following properties:

The below code simply populates the mult-valued parameter list:

Once the two parameters are set up the next stage is to alter the main dataset query. Select the Parameters tab and create an IIF statement that returns a predetermined string , "Select All" if the count of the number of parameters selected equals or is greater than EstabCount parameter value, i.e. if the 'Select All' option is checked in the drop down box.

=iif(Parameters!EstablishmentParameter.Count>=Parameters!EstabCount.Value, "Select All", Parameters!EstablishmentParameter.Value)

Then we need to alter the WHERE clause in the main dataset query:

WHERE (('Select All' IN (@EstablishmentParameter)) OR (View_ActiveEstablishment.EstablishmentName IN (@EstablishmentParameter)))

So, if the user checks 'Select All' in the drop down the IIF statement returns the string 'Select All' and the WHERE clause evaluates to true and returns a full selection thus negating the need for a potentially massive IN clause being used in the query. Likewise, if the user selects multiple parameter values then these are treated as expected. Unfortunately this may still result in a cumbersome IN clause if the user decides to select all but a few of the parameter values!

A quick play with SQL Server Profiler shows the 'Select All' database query before and after the alteration:

Select All option with multi-valued parameters - SSRS

A major short coming with multi-valued parameters in SQL Server Reporting Services is that there is no way to disable the 'Select All' option. This may have massive performance issues if 'Select All' is checked and large numbers of records are pulled from the DB. A work-around is to create another parameter and by using the IIF function limit the number of parameters available for selection.
















The first stage is to create a new parameter, in this case, called LimitEstablishmentParameter.















I've decided to limit the number of parameters available for selection using the following code in Default Value for the LimitEstablishmentParameter.



=iif(Parameters!EstablishmentParameter.count<=20,Parameters!EstablishmentParameter.value,"xxx")












Where xxx is a value that will return zero records from the dataset.

Change your dataset to refer to your new 'limiting' multi-valued parameter (LimitEstablishmentParameter in this case).






It's also worth putting a textbox near the top of the report with an expression similar to the following, informing the user of the selection limit:

=iif(Parameters!LimitEstablishmentParameter.Value(0)="xxx","The selection is limited to 20 establishments.",(Join(Parameters!EstablishmentParameter.Value, ", ")))