Home > SSRS Expert > Using Multi-value Parameters in SSRS Report

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.

image

And make sure the report parameter takes multi-value.

image

Then in the procedure, code it like this.

image

Here is the table-valued function ListToTable()

image

–==========================================
–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

Categories: SSRS Expert
  1. Santiago
    February 10, 2011 at 2:50 pm

    is it possible that the @Business parameter (when multiple options are selected) comes with its values separated by a comma (,) and then the function call thinks I’m sending so many parameters instead of a list of values???

    thanks!

    Like

  2. Sherry Li 李雨
    February 15, 2011 at 3:38 am

    No it will not. execute usp_myProc ‘bus loc 1, bus loc 2’ will not be mistaken as execute usp_myProc ‘bus loc 1’, ‘bus loc 2’.

    The first is recgonized as one parameter with a list of values. The second will see two parameters, with simple string in each.

    Like

  3. Santiago
    February 15, 2011 at 11:27 am

    well I can tell you that it happened to me. I fixed it not using the function but calling the IN like this

    WHERE SUPCCRID IN (@@SUP)

    @SUP is a list of values that comes from the combobox with checkboxes

    I use SQL 2005 and Visual Studio BI, and Reporting Server 2005

    hope this helps

    Like

  4. Sherry Li
    February 16, 2011 at 3:30 am

    I did use IN instead of = in the procedure. The function ListToTable turns the comma-delimited string into rows.

    Like

  1. March 9, 2011 at 9:03 pm
  2. April 29, 2011 at 1:14 am
  3. April 29, 2011 at 1:15 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: