Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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.
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.
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
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
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
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.
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.
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.
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.
- 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".
Last Update: 2013-01-07
About the author
View all my tips