Problem in Report Parameter for Passing Values

Question

Post reply

Problem in Report Parameter for Passing Values

abhitkumar

SSC Veteran

Points: 267
More actions
May 4, 2010 at 3:33 am

#234647

Hello All,
Currently my sql query is as follow for taking the selection paramtere value:-
Where Series_Name in (@Series)
For that i have created one ReportParameter as "Series".
In Label Name i have entered "AMCC" and entered the multiple values as AMCC8,AMCC9,AMCC10. In second label i have entered as "AMCN"
and entered values as AMCN8,AMCN9,AMCN10.
So now in Report view paramtere series displaying two value 1) AMCC 2) AMCC9
Now if i select both AMCC and AMCC9 then all the values should be passed to my sql query and returns data.
Please let me know how to do this?
Thanks,
ABHI

Viewing 5 posts - 1 through 4 (of 4 total)

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

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

Hi Abhit,

Could you please re-phrase your question and provide some brief snapshots about your requirements?

Raunak J

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Hi friend,

Here what you have to do is your WHERE condition is to be written as

WHERE Series_Name IN (select * from SplitList(@Series))

Here SplitList is a function which will return you the values as table. So hen you pass the AMCC8,AMCC9,AMCC10 the function will return a table with the rows as :

AMCC8

AMCC9

AMCC10

So your WHERE condition will work fine.

Now the problem is how @Series need to be passed. In the data set, click the parameter tab, in the value field give the expression as

=JOIN(Parameters!Series.Value,",")

Below I have give the function SplitList.

CREATE FUNCTION SplitList(@SERIES VARCHAR(500))

RETURNS @T TABLE

(

CODE VARCHAR(30)

)

AS

BEGIN

DECLARE @SERIESVALUE VARCHAR(30)

WHILE (LEN(@SERIES) > 0 )

BEGIN

IF (PATINDEX('%,%',@SERIES) > 0 )

BEGIN

SELECT @SERIESVALUE = SUBSTRING(@SERIES,1,PATINDEX('%,%',@SERIES)-1)

END

ELSE

SET @SERIESVALUE = @SERIES

INSERT INTO @T

VALUES (@SERIESVALUE)

SELECT @SERIES = SUBSTRING(@SERIES,LEN(@SERIESVALUE)+2,LEN(@SERIES))

END

RETURN

END

-- TEST SCRIPT

-- select * from SplitList('AMCC8,AMCC9,AMCC10')

Thanks & Regards,
MC

abhitkumar SSC Veteran Points: 267 More actions · Answer 3

Thanks for the reply,

I would check your case.

Also in the mean time i did this for my above question.

I have put where condition as below:-

WHERE ',' + @Series + ',' LIKE '%,' + T3.SeriesName + ',%')

and =JOIN(Parameter!Series.Value,",")

And currently its work.

Can you please look it and give me your feedback that i have done correct or not?

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

Hi friend,

I don't think it is correct.The WHERE condition should be how I have specified in the above section.

Thanks & Regards,
MC