August 25, 2011 at 1:10 pm
Hi ,
In Report, I have dropdown for "color" to select multiple values which uses split fucntion .
I have split function which will seperate multiple values by delimeter and pass that values to that function and returns single values.
I tested the function and works well.I have cretaed storeprocedure something like below.
ALTER PROCEDURE [dbo].[ProductList]
@Color varchar(20)
AS
BEGIN
SELECT * from Production.Product
WHERE Color IN ( @color)
OR Color IN (Select Color FROm dbo.Split(@Color, ','))
END
So in the report dataset properties -> paramters , I have written an expression which will join
all the parameters values in dropdown by given delemiter and pass that to my function in storedproc.
=JOIN(Parameters!Color.Label,",")
so my issue is when I choose colors: black and white , I not only see black and white color records but also other colors.
I am confused where i am doing wrong.Any help is appreciated 🙂
Thanks,
Komal
August 25, 2011 at 1:16 pm
komal145 (8/25/2011)
=JOIN(Parameters!Color.Label,",")
The above is your problem. By default, Reporting Services will take all the selected values from the multi-select dropdown and concatenate them. If you pass this into a stored proc as parameter, you will already have a comma-delimited string of all selected values.
What you are doing with the above line though, is taking ALL values in the dropdown and concatenating it into a string...so you are passing ALL values from the dropdown to the proc.
Pass the parameter as it is, without any formatting.
August 25, 2011 at 2:11 pm
I tried Remving Join () and pass multile values to paramter.It is still not working.
My Splitfunction :
ALTER FUNCTION [dbo].[SplitParameterValues]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT
SET @Iterator = 1
DECLARE @FoundIndex INT
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData))
SET @Iterator = @Iterator + 1
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
END
INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData))
RETURN
END
Thanks,
Komal
August 25, 2011 at 2:16 pm
Try this:
create function [dbo].[Split_Values]
(
@String varchar(4000)
,@Delimiter varchar(5)
)
returns @Split_Values table(String_Value varchar(200))
as
begin
declare @Split_Length int
while len(@String) > 0
begin
select @Split_Length =case charindex(@Delimiter,@String)
when 0 then len(@String)
else charindex(@Delimiter,@String) - 1
end
insert into @Split_Values
(
String_Value
)
(
selectsubstring(@String,1,@Split_Length)
)
select @String =case (len(@String) - @Split_Length)
when 0 then ''
else right(@String,len(@String) - @Split_Length - 1)
end
end
return
end
Usage will be as follows:
selectString_Value
fromDWH.dbo.Split_Values(@Store_Code_List,',')
August 25, 2011 at 5:32 pm
I strongly recommend not using any split function that has a WHILE Loop in it. Please see the following article:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2011 at 12:18 pm
I used other split function.But still not working.Is there any way to pass multiple values in dropdown..and get data for the selected values in paramter??
August 26, 2011 at 12:21 pm
komal145 (8/26/2011)
I used other split function.But still not working.Is there any way to pass multiple values in dropdown..and get data for the selected values in paramter??
I'm not sure that I understand 100% what the problem is. Can you please post some test data and expected results.
November 17, 2011 at 2:01 pm
Hi Komal,
Martins method: DWH.dbo.Split_Values(@Store_Code_List,',') should be used in stored proc's where clause (not in from clause).
Whenever using SplitList / multivalue parameter function, call it from your where clause like -
WHERE Country IN (DWH.dbo.Split_Values(@Store_Code_List,','))
In SSRS, select Country parameter properties, click on multivalue.
November 17, 2011 at 4:29 pm
kevin_nikolai (11/17/2011)
Hi Komal,Martins method: DWH.dbo.Split_Values(@Store_Code_List,',') should be used in stored proc's where clause (not in from clause).
Sorry, but no. Martin's method uses a WHILE loop and an mTVF. Use the method I posted as a link in one of my previous posts on this thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2011 at 6:47 am
Jeff Moden (11/17/2011)
kevin_nikolai (11/17/2011)
Hi Komal,Martins method: DWH.dbo.Split_Values(@Store_Code_List,',') should be used in stored proc's where clause (not in from clause).
Sorry, but no. Martin's method uses a WHILE loop and an mTVF. Use the method I posted as a link in one of my previous posts on this thread.
Jeff is right...and besides...this thread has been dead for a while and the OP hasn't asked for any further information.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply