Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Passing Multivalue Parameter Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2007 9:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, August 16, 2014 12:26 PM
Points: 12, Visits: 59
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.



Post #419425
Posted Thursday, November 8, 2007 8:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 9:25 AM
Points: 977, Visits: 250
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
Post #420110
Posted Friday, November 16, 2007 8:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 16, 2007 9:01 AM
Points: 1, Visits: 2
Worked perfect for me, Elmer, Thank You!! :)
Post #423082
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse