Using Multi-value Parameters in SSRS Report

Often times we need to allow users to select multiple choices for a parameter from a SSRS report.

Create a dataset in the Visual Studio for the RDL file with stored procedure as data source.

And make sure the report parameter takes multi-value.

Then in the procedure, code it like this.

Here is the table-valued function ListToTable()

--==========================================
--CREATED DATE:    3/27/2009
--DESCRIPTION:    Takes a list and converts it to a two column table
--                For use when passing a list to a stored proc
--==========================================

ALTER FUNCTION [dbo].[ListToTable](@List as varchar(max), @Delim as varchar(10))
RETURNS @listTable table(Position int, Value varchar(8000))
AS
BEGIN
  DECLARE @myPos INT
  SET @myPos = 1

  WHILE charindex(@delim, @list) > 0
  BEGIN
    INSERT INTO @listTable(Position, Value)
    VALUES(@myPos, left(@list, charindex(@delim, @list) - 1))

    SET @myPos = @myPos + 1
    IF charindex(@delim, @list) = len(@list)
        INSERT INTO @listTable(Position, Value)
        VALUES (@myPos, '')
    SET @list = right(@list, len(@list) - charindex(@delim, @list))
  END

  IF len(@list) > 0
    INSERT INTO @listTable(Position, Value)
    VALUES (@myPos, @list)

  RETURN
END