How to pass multi-value parameters to a Stored Procedure datasource in Telerik Reporting

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:

=Join(",",Parameters.Department.Value)

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.