Working With Multi-Select Parameters for SSRS Reports

Problem

Multi-select parameters give your users control over their reports while reducing the number of reports they have to work with. In this example, I will demonstrate how to create a multi-select parameter list and pass the values to a stored procedure that will then populate the report. I will be working with the AdventureWorks2008R2 database to create a report which will list sales quotas and amounts for selected Sales Reps.

Solution

The key to this solution is a delimited list of values that can be passed from the report to a stored procedure. I will be using the comma as a delimiter, but any delimiter will work.

For this example I will be using two parameters. The first parameter will provide a list of Sales Territories. The second parameter will provide a list of Sales Reps based on the selected Sales Territories from the first list. The report will show sales quotas and amounts for the selected Sales Reps.

Step 1

Create a stored procedure that will return a list of Sales Territories. This stored procedure will be used by the first parameter of the report.

  CREATE PROCEDURE dbo.ListSalesTerritory_s 
  AS 
 SET NOCOUNT ON
 SELECT
    TerritoryID
    ,[Name] AS TerritoryName
 FROM
    Sales.SalesTerritory
 ORDER BY 
    [Name]
  
 SET NOCOUNT OFF
  GO

Step 2

Create a second stored procedure that will return a list of Sales Reps for 1 to N Sales Territories. This will be used by the second parameter of the report. The list of selected Sales Territories will be passed to the stored procedure as a comma delimited list of TerritoryIDs in a parameter called @TerritoryID. (If your list of values have commas, then you'll have to use a different delimiter.) The size of the parameter should be determined by the maximum list of values that might be sent to the stored procedure.

There are many different ways to break up a delimited list of values. They are usually referred to as Split Functions. I like to use a recursive CTE (Common Table Express) to split the values up. After the list of values are in a table structure, the table structure can be joined to the rest of the tables needed to return the list of Sales Reps.

CREATE PROCEDURE ListSalesRep_s (@TerritoryIDs AS varchar(100))
 AS 
   SET NOCOUNT ON;
 
   WITH CTE_Pieces
   AS 
   (
      SELECT
         1 AS ID
         ,1 AS StartString
         ,CHARINDEX(',', @TerritoryIDs) AS StopString
 
      UNION ALL
 
      SELECT
         ID + 1
         ,StopString + 1
         ,CHARINDEX(',', @TerritoryIDs, StopString + 1)
      FROM
         CTE_Pieces
      WHERE
         StopString > 0
   )
 
   ,CTE_Split
    AS
   (
      SELECT
         CONVERT(int,SUBSTRING(@TerritoryIDs, StartString,
                                             CASE 
                                                WHEN StopString > 0 THEN StopString - StartString
                                                ELSE LEN(@TerritoryIDs)
                                                END)) AS TerritoryID
      FROM
         CTE_Pieces 
   )    
   SELECT
         P.BusinessEntityID
         ,P.LastName +  ', ' + P.FirstName AS SalesRep
   FROM 
         CTE_Split AS S
         JOIN Sales.SalesPerson AS SP ON sp.TerritoryID = s.TerritoryID
         JOIN Person.Person AS P ON SP.BusinessEntityID = P.BusinessEntityID
 
   SET NOCOUNT OFF

Step 3

Create the stored procedure for the body of the report. In this example, it will also have a parameter, @BusinessEntityIDs that will contain a comma delimited list of selected Sales Reps.

  
  CREATE PROCEDURE dbo.RptSales_s(@BusinessEntityIDs AS varchar(100))
  AS 
    SET NOCOUNT ON;
    WITH CTE_Pieces
    AS 
    (
        SELECT
            1 AS ID
            ,1 AS StartString
            ,CHARINDEX(',', @BusinessEntityIDs) AS StopString
   
        UNION ALL
  
        SELECT
            ID + 1
            ,StopString + 1
            ,CHARINDEX(',', @BusinessEntityIDs, StopString + 1)
        FROM
            CTE_Pieces
        WHERE
            StopString > 0
    )
    ,CTE_Split
    AS
    (
        SELECT
            CONVERT(int,SUBSTRING(@BusinessEntityIDs, StartString, 
                                  CASE 
                                     WHEN StopString > 0 THEN StopString - StartString
                                     ELSE LEN(@BusinessEntityIDs)
                                     END
                                  )
                   ) AS BusinessEntityID
        FROM
            CTE_Pieces 
    ) 
    SELECT
        P.LastName +  ', ' + P.FirstName AS SalesRep
        ,ST.Name AS TerritoryName
        ,ST.CountryRegionCode
        ,SP.SalesQuota
        ,SP.Bonus
        ,SP.SalesYTD
        ,SP.SalesLastYear
    FROM 
        CTE_Split AS s
        JOIN Sales.SalesPerson AS SP ON s.BusinessEntityID = sp.BusinessEntityID
        JOIN Sales.SalesTerritory AS ST ON SP.TerritoryID = ST.TerritoryID
        JOIN Person.Person AS P ON SP.BusinessEntityID = P.BusinessEntityID
  
    SET NOCOUNT OFF
  GO

Step 4

Add the three stored procedures that were created in steps 1 through 3 to a new report. Then create the layout for the report. My report is laid out by grouping the Sales Reps by Territory.

 
Finished Report

Step 5

Set the Properties of both parameters by right clicking on the parameter and selecting Parameter Properties from the drop down list.

• Check the Allow multiple values checkbox.
• Select Available Values from the left hand list.
  • Select the Get values from a query option button.
  • Set the Dataset drop down list to the proper dataset.
  • Set the Value field drop down list to the proper field. This is the value that will be returned to the dataset that needs it.
  • Set the Label field drop down list to the proper field. This is the value that will be displayed to the user.

Step 6

The list of selected values need to be returned to the ListSalesRep_s and the RptSales_s stored procedures. This will be done using the JOIN expression.

1. Right click on the stored procedure that needs the list of values and select Dataset Properties from the drop down list.
2. Select Parameters in the left hand list of the Dataset Properties dialog box.
3. In this example, the delimited list of Territories need to be assigned to the @TerritoryIDs parameter. Click the function button to enter a function for the parameter value.
4. Add the following code for the expression. Make sure to use the Value property, not the Label property or the wrong list of values will be sent to the stored procedure.

 =Join(Parameters!TerritoryIDs.Value,",")

Step 7

It's time to run your report. When the drop down lists are pulled down, there should be check boxes to select only the rows you want. Each time the selected items are changed in the Territory IDs drop down, the Sales Rep list will automatically be regenerated when the Sales Rep list is pulled down.

BUT THAT'S NOT ALL...

I think all reports should display the values of the parameters used in the report. This way when someone brings up a concern about a report, you know exactly which parameter values were used.

If you'll notice in the sample below, I listed only the three Territories that were selected, but since all the Sales Reps for those territories are used in the report, the word ALL is used instead of listing each Sales Rep out individually. This can be done with an IIF expression in a Label control.

The first parameter of the IIF expression compares the count of how many items are in the parameter list, with how many that were selected. If the two values match, then the label ALL is used. If they don't match, then the JOINexpression is used again, but this time the Labels are joined together. Make sure to include a space after the delimiter so that the values don't run together.

Referred from MSSQLTIPS.COM