Efficient way of using ALL as a parameter value in multivalued parameter

Most of the time, we came into a situation where we need to pass ALL values of a multivalued parameter to our stored procedure/T-Sql and generally we pass it as a comma separated value to our query. But passing a long set of values to our SP/T-Sql under IN(..,..,..) clause decreases the performance of the query because in this case query engine needs to look into all set of available values in IN(..,..,..) clause against every record. Let’s see step by step that how we can overcome from this issue.

Step 1: Add a Select statement like below in your dataset query that you are using for the parameter binding.

Select -1 as Employee_ID,'ALL' as Employee_Name /*Change the field names as per your requirement*/

Union All

<Your T-Sql Statement>

Here we are passing -1 as a value for ALL. You can choose any other value as per your requirement but do not use NULL. Because if there will be NULL in the multivalued parameter list, you will experience an error in 2008 or below versions whereas R2 will exclude NULL records from the parameter list. This SSRS behavior makes sense because if we concatenate any value(s) with NULL, resultant will be NULL.

In support of this step, you can refer “Employee_Parameter” dataset in attached sample.

Step 2: Pass the selected multivalued parameter in your details dataset’s parameter like following expression =IIF(Parameters!Employee_ID.Value(0)=-1,Nothing,Join(Parameters!Employee_ID.Value,","))

In support of this step, you can refer the expression of the @Employee_ID parameter in “Details_Dataset” dataset in attached sample.

Step 3: Now you need to modify the <where> - clause of your resultant query in such a way that will maximize the response time of your output query like following where condition.

Select <Columns List>

From

<Tables with required joins>

Where

( (@Employee_ID IS NULL) OR (Employee_ID in (<Multivalued parameter values in comma seperated form>)) )

In support of this step, you can refer the where clause of the Details_Dataset” dataset in attached sample

You can download the sample report from following link.

Use of ALL parameter value report sample

You need to only modify the datasource of the sample report.

Remarks

If you are using this concept in your report, you need to convey your end user that they will see all the possible records on the report if they select other available values with ALL.