Passing Multivalue Parameter

  • How to pass Multivalue parameter in Reporting Services.

    In my Report DataSet, I am using statement like this.

    Exec SalesReport @SalesTerritory, @ProductName, @StartDate,

    @EndDate

    Here @SalesTerritoy and @ProductName are Multivalue parameters. And I am Selecting more than one value for the paramenters from the drop down.

    In sql Procedure I am splitting the comma separated values and putting to temp table.

    Thnx in Advance.

  • I'm not sure I understand your question, but it sounds like you need help with the sql procedure to make use of the multi-value parameter.

    I usually do this by making the multi-value parameter a varchar(max) then in the query use the IN operator to filter the results like this:

    select ...

    from Sales

    where ProductKey in (select convert(value,int) from dbo.Split(@ProductKeyList,',',default,default))

    The Split function I use is:

    --This UDF will split a delimited list into a table.

    ALTER FUNCTION [dbo].[Split]

    (

    @list NVARCHAR(4000)

    , @delimiter NVARCHAR(10) = N','

    , @include_null BIT = 0

    , @null_text NVARCHAR(10) = NULL

    )

    RETURNS @tableList TABLE(

    idx SMALLINT IDENTITY (1,1) PRIMARY KEY,

    value NVARCHAR(100) NULL

    )

    AS

    BEGIN

    DECLARE @value NVARCHAR(100)

    DECLARE @position INT

    SET @list = LTRIM(RTRIM(@list))+ @delimiter

    SET @position = CHARINDEX(@delimiter, @list, 1)

    IF REPLACE(@list, @delimiter, '') <> ''

    BEGIN

    WHILE @position > 0

    BEGIN

    SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))

    IF @include_null = 1 OR @value <> ''

    BEGIN

    IF @value = '' SET @value = @null_text

    INSERT INTO @tableList (value)

    VALUES (@value)

    END

    SET @list = RIGHT(@list, LEN(@list) + 1 - LEN(@delimiter) - @position)

    SET @position = CHARINDEX(@delimiter, @list, 1)

    END

    END

    RETURN

    END

  • Worked perfect for me, Elmer, Thank You!! 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply