Edit

Add a multi-value parameter to a paginated report

  • Article

Applies to:  Microsoft Report Builder (SSRS)  Power BI Report Builder  Report Designer in SQL Server Data Tools

You can add a parameter to a paginated report that allows the user to select more than one value for the parameter.

You can pass multiple parameter values to the report within the report URL. For more information, see Pass a report parameter within a URL.

For information on how to pass multiple parameter values to a stored procedure, see Work with multi-select parameters for SQL Server Reporting Service Reports on mssqltips.com.

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 select 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).

    WHERE  
  Production.ProductInventory.ProductID IN (@ProductID)

    Important

    • The query text must not include the DECLARE statement for the query variable.
    • The text for the query variable must include the IN operator, as shown in the previous example.
    • Be sure to include the parentheses around the variable as shown previously. Otherwise, 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 select 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 by selecting a single box. If you choose to get the available values for the report parameter from a dataset query, be sure to select a dataset that doesn't 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).

Related content

Add cascading parameters to a report (Report Builder)
Add, change, or delete a report parameter (Report Builder)