Share via

Lesson 4: Adding a Multivalue Parameter with Select All

  • Article

New: 17 July 2006

In this lesson, you will change the SalesOrderID and DayoftheWeek parameters from single value parameters to multivalue parameters. Multivalue parameters allow you to select more than one value for a report parameter. To modify the report parameter SalesOrderID, you will change the query for the AdventureWorks dataset to test for @SalesPersonID in the set of selected values instead of equal to a single value, and check the report parameter multivalue property. To modify the DayoftheWeek report parameter, you will check the multivalue property, set the available values from a new dataset, and provide an expression for the default values. You will create a new dataset to provide available values for the DayoftheWeek parameter. Finally, you will add a text box to the report to display parameter values for the DayoftheWeek selections made.

To open the Sales Orders report

  1. In SQL Server Business Intelligence Development Studio, open the Tutorial Report Server project created in the previous lesson.

  2. In Solution Explorer, double-click the Sales Orders report. The report opens in Layout view.

  3. Click the Data tab.

To modify the dataset query to test for inclusion in a set of values

  1. From the Datasets drop-down list, choose AdventureWorks.

  2. Modify the WHERE clause of the existing query by changing the @SalesPersonID test from equality (= (@SalesPersonID)) to inclusion (IN (@SalesPersonID)).

    Replace the query string for this dataset with the following query.

    SELECT S.OrderDate, DATENAME(weekday, S.OrderDate) as Weekday,
    S.SalesOrderNumber, S.TotalDue, C.FirstName, C.LastName
FROM    HumanResources.Employee E INNER JOIN
        Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
        Sales.SalesOrderHeader S ON E.EmployeeID = S.SalesPersonID
WHERE
   (
   S.OrderDate BETWEEN (@StartDate) AND (@EndDate) 
   AND
   S.SalesPersonID IN (@SalesPersonID)
   )

  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

    @SalesPersonID

    286

  4. Click OK. The result set appears for the sales person Ranjit Varkey Chudakatil with SalesPersonID = 286.

To set the properties of the report parameter SalesPersonID

  1. On the Report menu, select Report Parameters. The Report Parameters dialog box opens.

  2. In the Parameters pane, select SalesPersonID.

  3. In the Properties section, check Multi-value.

  4. In the Prompt text box, type Select Sales Persons:.

  5. Click OK.

  6. Click the Preview tab. The report shows a drop-down list with 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.

Note

The Select All option was disabled in SQL Server 2005 Service Pack 1, but restored in SQL Server 2005 Service Pack 2.

To add a new dataset for available values for DaysoftheWeek

  1. On the Data tab, from the Datasets drop-down list, choose <New Dataset>.

  2. Set the data source to the AdventureWorks data source.

  3. In the Name field, type WeekDaysfromQuery.

  4. Paste the following query string in the query pane.

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

    DATEFIRST sets which day of the week you want to use as the first day. For example, SET DATEFIRST 7 changes the order of the days of the week so that Sunday is the first day. For more information, see SET DATEFIRST (Transact-SQL).

    In this tutorial, Monday is the first day of the week.

  5. Click OK.

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

To set the properties of the report parameter DayoftheWeek

  1. On the Report menu, select Report Parameters. The Report Parameters dialog box opens.

  2. In the Parameters pane, select DayoftheWeek. This parameter was created in Lesson 2.

  3. In the Properties section, check Multi-value.

  4. In the Prompt text box, type Filter on which days of the week:.

  5. In the Available values section, from the Datasets drop-down list, select WeekDaysfromQuery.

  6. From the Value field drop-down list, select Weekday.

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

    For the Default values, in the first Value text box, type Saturday in the second Value text box, type Sunday.

  8. Click OK.

    Before you can preview the report, you need to change the filter expression defined for the table data region to use the IN operator because the DayoftheWeek parameter is now multivalued.

To change the filter to use a multivalue parameter

  1. In Layout view, select the table. The table appears with a grey outline.

  2. Right-click on the table outline and select Properties. The Table Properties dialog box opens.

  3. Click the Filter tab.

  4. In the Expression column, from the drop-down list, verify the value is =Fields!Weekday.Value.

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

  6. In the Value text box, from the drop-down list, verify the value is =Parameters!DayoftheWeek.Value.

  7. Click OK.

    The filter for the table is now set to compare the value of the field Weekday with the value 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.

  8. Click the Preview tab. The report shows the report parameter DaysoftheWeek with default values Saturday and Sunday.

Note

A (Select All) value, with the exception as noted in a previous procedure, 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 text box to display parameter values

  1. In Layout view, select the table. Press the down arrow key several times to move the table down and expand the report height.

  2. From the toolbox, drag a text box above the table. Select one edge of the text box and expand its width to match the width of the table.

  3. Paste the following expression into the text box:

    ="From " & Parameters!StartDate.Value & " to " & 
Parameters!EndDate.Value & vbCrLf & "For the following " & 
Parameters!DayoftheWeek.Count & " days of the week: " & 
Join(Parameters!DayoftheWeek.Value,", ") & vbCrLf &
"First and last selected salespersons: " & 
Parameters!SalesPersonID.Label(0) & " and " & 
Parameters!SalesPersonID.Label(Parameters!SalesPersonID.Count-1)

    There are several things for you to note about this expression:

    • You can refer to the parameter label instead of the value. For example: Parameters!SalesPersonID.Label instead of Parameters!SalesPersonID.Value.
    • To address an individual member of a parameter collection, use the zero-based index. For example: Parameters!SalesPersonID.Label(0).
    • To address the entire collection, use no index. For example: Parameters!SalesPersonID.Value.
    • To find the number of values, use Parameters!SalesPersonID.Count.
    • You can also find out if a parameter is multivalue. For example: Parameters!SalesPersonID.IsMultivalue.
    • You can concatenate all the selected values in a multivalue parameter by using the Visual Basic Join function. For example, Join(Parameters!DayoftheWeek.Value,", ").

  4. Click Preview. Verify that when you click View Report, the text box shows the values you have selected in the parameters.

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 accommodate using a multivalue parameter collection. You have learned to use multivalue parameters in an expression. In the next lesson, you will learn to create a Boolean parameter to control how much detail to see in a report. See Lesson 5: Adding a Parameter to Control Row Visibility.

Change History

Release History

12 December 2006
Changed content:
  • Select All option restored in SP 2.

See Also

Tasks

Lesson 1: Creating a Report Server Project for the Parameter Tutorial

Other Resources

Working with Parameters in Reporting Services

Help and Information

Getting SQL Server 2005 Assistance