Add a multi-value parameter to a Report

You can add a parameter to a report that allows the user to select more than one value for the parameter. You can also use the multi-value parameter to filter table data.

You can pass multiple parameter values to the report within the report URL. For a URL example includes a multi-value parameter, see Pass a Report Parameter Within a URL.

For information on how to pass multiple parameter values to a stored procedure, see Working With Multi-Select Parameters for SSRS Reports on mssqltips.com.

To add a multi-value parameter

1. In Report Builder, open the report that you want to add the multi-value parameter to.

2. Right-click the report dataset, and then click Dataset Properties

3. Add a variable to the dataset query by either editing the query text in the Query box, or by adding a filter by using the query designer. For more information, see Build a Query in the Relational Query Designer (Report Builder and SSRS).

   Important

   The query text must not include the DECLARE statement for the query variable.

   Important

   The text for the query variable must include the IN operator, as shown in the following example.

   WHERE
     Production.ProductInventory.ProductID IN (@ProductID)
   

   Important

   If you don’t include the parentheses around the variable as shown above, the report fails to render and the “must declare the scalar variable” error is displayed.

   A dataset parameter for an embedded dataset or a shared dataset is created automatically for the query variable. A report parameter is created automatically for the dataset parameter.

4. In the Report Data pane, expand the Parameters node, right-click the report parameter that was automatically created for the dataset parameter, and then click Parameter Properties.

5. In the General tab, select Allow multiple values to allow a user to select more than one value for the parameter.

6. (Optionally) In the Available values tab, specify a list of available values to display to the user.

   An available values list limits the choices a user can make to only valid values for the parameter. For multiple values, the top of list begins with a Select All feature so the user can select or clear all values with a single click. If you choose to get the available values for the report parameter from a dataset query, be sure to select a dataset that does not contain the query variable that is associated with the same report parameter.

   For more information, see Add, Change, or Delete Available Values for a Report Parameter (Report Builder and SSRS).

To filter table data using a multi-value parameter

1. Click in the table, and then right-click the edge of the table and click Tablix Properties.

   

2. In the Tablix Properties dialog box, click Filters in the right-hand pane, and then click Add.

   

3. In the Expression drop-down list box, select the field to filter.

   

4. In the Operator drop-down list box, select the In operator.

   

5. In the Value box, type the parameter as follows and then click OK.

   [@myParameter]

   

See Also

Tasks

Add Cascading Parameters to a Report (Report Builder and SSRS)

Add, Change, or Delete a Report Parameter (Report Builder and SSRS)