By: Mickey Stuewe | Comments (41) | Related: > Reporting Services Development
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 OFFStep 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.
- Right click on the stored procedure that needs the list of values and select Dataset Properties from the drop down list.
- Select Parameters in the left hand list of the Dataset Properties dialog box.
- 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.
- 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 JOIN expression 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.
Next Steps
- Review the tip on Recursive Queries using Common Table Expressions (CTE) in SQL Server
- To download the AdventureWorks2008R2 database, go to codeplex.com and search for the "AdventureWorks 2008 R2 database".
About the author
Mickey Stuewe is a Microsoft Certified Professional in Querying Microsoft SQL Server 2012. She has been with Microsoft development products like SQL Server, SSRS, Excel, and VB since 1995. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
