Tuesday 7 September 2010

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:


2 comments:

Faiz said...

Good job :)

Anonymous said...

Exactly what is required! Under 2016 there is now a function to evaluate the number of entries within a prompt, therefore negating the need for a separate query driven parameter