• Article

Lesson 3: Adding Parameters to Select Multiple Values in a List (Report Builder 2.0)

In this lesson, you will learn how to change a parameter to accept multiple values. These are known as multivalue parameters. By default, a parameter accepts a single value. The parameters that you created in the previous lesson, BusinessPersonID and DayoftheWeek, accepted single values. To modify a parameter to take multiple values, you must know whether the parameter was created automatically from a query parameter, like BusinessPersonID, or whether you created it manually by using the Report Data pane, like DayoftheWeek.

To modify a report parameter to accept multiple values, you just need to set a report parameter property. If the report parameter was based on a query parameter, you must also change the query. If you use the parameter in a filter, you must change the filter operator to specify a set of values instead of a single value.

To replace the existing dataset

  1. In the Report Data pane, right-click the dataset DataSet1, and then click Query.

    The text-based query designer opens.

  2. Replace the text with the following query into the text box:

    SELECT
  SH.OrderDate
  ,DATENAME(weekday, SH.OrderDate) as Weekday
  ,SH.SalesOrderNumber
  ,SD.OrderQty
  ,SD.LineTotal
  ,P.Name AS [Product]
  ,PS.Name AS [Subcategory]
FROM Sales.SalesPerson SP 
  INNER JOIN Sales.SalesOrderHeader AS SH 
      ON SP.BusinessEntityID = SH.SalesPersonID
  INNER JOIN Sales.SalesOrderDetail AS SD 
     ON SH.SalesOrderID = SD.SalesOrderID
  INNER JOIN Production.Product AS P
   ON SD.ProductID = P.ProductID
  INNER JOIN Production.ProductSubcategory AS PS
   ON PS.ProductSubcategoryID = P.ProductSubcategoryID
  INNER JOIN Production.ProductCategory AS PC
   ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE PC.Name = 'Clothing' 
   AND (SH.OrderDate BETWEEN (@StartDate) AND (@EndDate))
      AND SH.SalesPersonID IN (@BusinessPersonID)

    This is the same query as before, except that the condition has been changed from equality = to inclusion IN:

    AND SH.SalesPersonID IN (@BusinessPersonID)

  3. Click the Run (!) button. When prompted for the query parameters, use the following table to enter values. The query designer does not support testing multivalue parameters.

    @StartDate

    20010101

    @EndDate

    20030101

    @BusinessPersonID

    290

  4. Click OK. 

    The result set appears for the sales person Ranjit Varkey Chudukatil with BusinessPersonID = 290.

To edit the BusinessPersonID report parameter to accept multiple values

  1. In the Report Data pane, expand Parameters, double click the BusinessPersonID parameter.

  2. Select the Allow multiple values option.

  3. Click OK. 

  4. Click Preview. The report runs automatically. A drop-down list for BusinessPersonID shows all sales person names.

Note

A (Select All) value is provided as the first value in an available values drop-down list for a multivalue parameter. Use this check box to select all or clear all values.

To add a new dataset to populate the valid values for a report parameter

  1. Switch to Design view.

  2. In the Report Data pane toolbar, click New, and then click Dataset. The Dataset Properties dialog box opens.

  3. In Name, type WeekDaysfromQuery.

  4. In Query type, verify Text is selected.

  5. Click Query Designer, and then click Edit As Text.

  6. In the Query pane, paste the following query:

    SET DATEFIRST 1;
SELECT DISTINCT 
   DATEPART(weekday, S.OrderDate) as WeekdayNumber,
   DATENAME(weekday, S.OrderDate) as Weekday
   FROM Sales.SalesOrderHeader S
Order by WeekdayNumber

  7. Click the Run (!) button on the query designer toolbar. The result set shows ordinal numbers and days of the week.

  8. Click OK twice to exit the Dataset Properties dialog box.

    The dataset WeekDaysfromQuery appears in the Report Data pane.

To edit a parameter to accept multiple values, default values, and valid values

  1. In the Report Data pane, expand Parameters, and double-click DayoftheWeek. The Report Parameter Properties dialog box opens.

  2. Select Allow multiple values.

  3. Click Available Values.

  4. Select Get values from a query.

  5. In Dataset, from the drop-down list, select WeekDaysfromQuery.

  6. In Value field, from the drop-down list, select WeekdayNumber.

  7. In Label field, from the drop-down list, select Weekday.

When the user selects parameter values, they select from the labels, but the report uses the values. This is important when you set default values or a filter. The values must be the integer values for the WeekdayNumber field.

  1. Click Default Values.

  2. Select Specify values.

  3. Click Add.

  4. In Value, replace Friday with 6. Because the values come from a dataset that supplies both a weekday number and a weekday name, the default value must specify the weekday number. The value 6 represents Saturday.

  5. Click Add.

  6. In Value, type 7. This value represents Sunday.

  7. Click OK. 

    Before you can preview the report, you must change the filter expression for the table to use the IN operator because the DayoftheWeek parameter accepts multiple values.

To change a filter to use a multivalue parameter

  1. In the Report Data pane, right-click DataSet1, and then click Dataset Properties.

  2. Click Filters. There is already a filter that has been added for DayoftheWeek from Lesson 1.

  3. In Expression, from the drop-down list, verify the value is [Weekday].

  4. Change Operator from the equal sign (=) to In.

  5. In Value, type [@DayoftheWeek.Label].

    Specify the parameter label so that the name of the week day is compared against the label field, not the numeric field for weekday.

  6. Click OK. 

    The filter for the table is now set to compare the value of the field Weekday with the label of the parameter DayoftheWeek using the In operator. When you choose multiple values for the report parameter, the filter will test each row of the table to see if the Weekday field exists in the DayoftheWeek collection.

  7. Click Run to preview the report. The report shows the report parameter DaysoftheWeek with default values Saturday and Sunday, which are the labels for the default values that you specified. Use the drop-down list to select multiple values for the DayoftheWeek parameter.

Next Steps

You have successfully changed report parameters properties from single valued to multivalued. You have made the necessary changes in a query, a filter, and an expression to use a multivalue parameter collection. You have learned to use multivalue parameters in an expression. In the next lesson, you will learn how to create parameters whose values are populated conditionally based on a selected value from a previous parameter. See Lesson 4: Adding Cascading Parameters (Report Builder 2.0).