Lesson 3: Adding Parameters to Select Multiple Values in a List (SSRS)
In this lesson, you will change the BusinessPersonID 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 BusinessPersonID, you will change the query for the AdventureWorks2008R22008 dataset to test for @BusinessPersonID 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 replace the existing dataset
In the Report Data pane, right-click the dataset AdventureWorksDataset, and then click Dataset Properties.
In Data source, verify that AdventureWorks_Ref is selected.
In Query type, verify that Text is selected.
Click the Query Designer button to open the query designer.
Replace the text with the following query into the text box:
SELECT soh.OrderDate AS [Date], DATENAME(weekday, soh.OrderDate) as Weekday, soh.SalesOrderNumber AS [Order], pps.Name AS Subcat, pp.Name as Product, SUM(sd.OrderQty) AS Qty, SUM(sd.LineTotal) AS LineTotal FROM Sales.SalesPerson sp INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID INNER JOIN Sales.SalesOrderDetail AS sd ON sd.SalesOrderID = soh.SalesOrderID INNER JOIN Production.Product AS pp ON sd.ProductID = pp.ProductID INNER JOIN Production.ProductSubcategory AS pps ON pp.ProductSubcategoryID = pps.ProductSubcategoryID INNER JOIN Production.ProductCategory AS ppc ON ppc.ProductCategoryID = pps.ProductCategoryID GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, pps.Name, pp.Name, soh.SalesPersonID HAVING ppc.Name = 'Clothing' AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate)) AND soh.SalesPersonID IN (@BusinessPersonID)
This is the same query as before, except that a condition has been changed from equality to inclusion.
AND soh.SalesPersonID IN (@BusinessPersonID)
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.
The result set appears for the sales person Ranjit Varkey Chudukatil with BusinessPersonID = 290.
To edit the BusinessPersonID report parameter to accept multiple values
In the Report Data pane, expand Parameters, double click the BusinessPersonID parameter.
Select the Allow multiple values option.
Click Preview. The report runs automatically. A drop-down list for BusinessPersonID shows all sales person names.
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. By default, all values are selected.
To add a new dataset to populate the valid values for a report parameter
Switch to Design view.
In the Report Data pane, right-click AdventureWorks_Ref, and click Add Dataset. The Dataset Properties dialog box opens.
In the Name field, type WeekDaysfromQuery.
In Query type, verify Text is selected.
In Query, type or paste the following query string:
SET DATEFIRST 1; SELECT DISTINCT DATEPART(weekday, S.OrderDate) as WeekDayNumber, DATENAME(weekday, S.OrderDate) as Weekday FROM Sales.SalesOrderHeader S Order by WeekDayNumber
Click the Run (!) button on the query designer toolbar. The result set shows ordinal numbers and days of the week.
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
In the Report Data pane, expand Parameters, and double-click DayoftheWeek. The Report Parameter Properties dialog box opens.
Select Allow multiple values.
Click Available Values.
Select Get values from a query.
In Dataset, from the drop-down list, select WeekDaysfromQuery.
In Value field, from the drop-down list, select Weekday.
In Label field, from the drop-down list, select Weekday.
Click Default Values.
Select Specify values.
(Optional) Select the existing value Friday and click Delete.
In Value, type Saturday.
In Value, type Sunday.
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 accepts multiple values.
To change a filter to use a multivalue parameter
In Design view, right-click in the table, and then click Tablix Properties. The Tablix Properties dialog box opens.
Click Filters. There is already a filter that has been added for DaysoftheWeek from Lesson 1.
In Expression, from the drop-down list, verify the value is [Weekday].
Verify that Text is selected.
Change Operator from the equal sign (=) to the In operator.
In the Value text box, from the drop-down list, verify that the value is [@DayoftheWeek].
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.
Click Preview. The report shows the report parameter DaysoftheWeek with default values Saturday and Sunday. Use the drop-down list to select multiple values for the DayoftheWeek parameter.
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 how to create parameters whose values are populated conditionally based on a selected value from a previous parameter. See Lesson 4: Adding Cascading Parameters (SSRS).