Today I came across the stumbling block of passing multi-value parameters to a Stored Procedure. After some trial and error, I have discovered a way to do this. I had checked the Telerik Forum and found no suggestions for a solution. In fact the Telerik documentation here states it is not possible. In this example scenario, the report is to display information on sales people grouped by their department. The report parameters include one that allows the user to select a collection of departments to include in the report.
The key is to pass the parameter’s multiple values as a comma (or other character) delimited string. To do this, configure your parameter (using a parameter named @department as an example) in the “Configure data source parameters” step of the data source configuration wizard to join the selected values together using the syntax:
Now we need a table-valued function in the database to convert the comma delimited list string to a table of values. Here is what I used:
CREATE FUNCTION splitstring ( @strToSplit VARCHAR(8000) ) RETURNS @returnList TABLE ([Param] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE CHARINDEX(',', @strToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(',', @strToSplit) SELECT @name = SUBSTRING(@strToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @name SELECT @strToSplit = SUBSTRING(@strToSplit, @pos+1, LEN(@strToSplit)-@pos) END INSERT INTO @returnList SELECT @strToSplit RETURN END
Now you can utilize the passed parameter in your main report data source SQL query by using the following syntax in your WHERE clause:
WHERE @department IS NULL OR @department='' OR @department='All' OR salesman.department IN (SELECT [Param] FROM splitstring(@department))
In this example I’m allowing for a supplied NULL, empty string or ‘All’ value to denote selecting all departments.
Hope this helps some of you out.