Dela via

  • Artikel

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

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

  2. In Data source, verify that AdventureWorks_Ref is selected.

  3. In Query type, verify that Text is selected.

  4. Click the Query Designer button to open the query designer.

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

  6. 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

    1/1/2001

    @EndDate

    1/1/2003

    @BusinessPersonID

    290

  7. 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. By default, all values are selected.

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, right-click AdventureWorks_Ref, and click Add Dataset. The Dataset Properties dialog box opens.

  3. In the Name field, type WeekDaysfromQuery.

  4. In Query type, verify Text is selected.

  5. 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

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

  7. 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 Weekday.

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

  8. Click Default Values.

  9. Select Specify values.

  10. (Optional) Select the existing value Friday and click Delete.

  11. Click Add.

  12. In Value, type Saturday.

  13. Click Add.

  14. In Value, type Sunday.

  15. 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 accepts multiple values.

To change a filter to use a multivalue parameter

  1. In Design view, right-click in the table, and then click Tablix Properties. The Tablix Properties dialog box opens.

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

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

  4. Verify that Text is selected.

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

  6. In the Value text box, from the drop-down list, verify that the value is [@DayoftheWeek].

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

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