November 6, 2007 at 9:54 pm
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.
November 8, 2007 at 8:36 am
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
November 16, 2007 at 8:17 am
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