D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Tuesday, 20 July 2010

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, ", ")))


2 comments:

Dom Horton said...

To add a'Null' to the available multi-valued parameter list, look here

Dom Horton said...

An updated post that alters and improves the efficiency of the main dataset query if the user checks 'Select All' is here