Export (0) Print
Expand All

Add a multi-value parameter to a Report

SQL Server 2012

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 note Important

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

    Important note 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 noteImportant

    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.

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

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

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

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

    [@myParameter]

    Value field

Community Additions

ADD
Show:
© 2014 Microsoft