Multiple Parameter Values

Question

Post reply

Multiple Parameter Values

Canazei65

SSCommitted

Points: 1696
More actions
August 27, 2012 at 1:46 pm

#261002

Hi
I have 4 multiple value parameters being passed to my report each based on it's own embedded dataset. I am able to select single or multiple values within the the first three parameters, but for some reason when I select more than one value in the fourth parameter, no data is returned. It works fine if only one value is selected.
The dataset is just:
SELECT 'Item1' AS Code, 'Group1' AS CodeDesc
UNION ALL
SELECT 'Item2' AS Expr1, 'Group2' AS Expr2
Please can anyone offer a reason as to why this is happening only to this parameter, when all the parameter datasets are set up in the same way and how can I cure it?
Many thanks.
Canazei65

Viewing 7 posts - 1 through 6 (of 6 total)

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

cporteous SSC Eights! Points: 966 More actions · Answer 1

Hey,

I'm not sure I follow here - are the text parts in your SELECT's meant to be the parameters?

Because the SQL you posted will just output the below with no input from the parameters.

Code | Code Desc

Item1 | Group1

Item2 | Group2

I think we need some more info. Ta

Canazei65 SSCommitted Points: 1696 More actions · Answer 2

Hi

Sorry about that, maybe this will be clearer. I've got the same problem with selecting multiple values from a parameter in another report.

The SQL for the query on which the parameter is based is :

SELECT 'Inner City & East Bristol' AS CCGLocality, 'CON_ICE' AS CCGCode

UNION ALL

Select

'North & West Bristol' as Expr1,'CON_N&W' as Expr2

union all

Select

'North Somerset' as Expr1,'CON_NS' as Expr2

union all

Select

'Not Engaged in PBC' as Expr1,'CON_NON' as Expr2

union all

Select

'South Bristol' as Expr1,'CON_STH' as Expr2

union all

Select

'South Gloucestershire' as Expr1,'CON_SGL' as Expr2

union all

Select 'Unknown' as Expr1, 'Unknown' as Expr2

and results in the output:

CCGLocality CCGCode

Inner City & East BristolCON_ICE

North & West BristolCON_N&W

North Somerset CON_NS

Not Engaged in PBCCON_NON

South Bristol CON_STH

South GloucestershireCON_SGL

Unknown Unknown

The available values are then:

Valuefield : CCGCode

Labelfiield : CCGLocality

Again, I am only able to select a single value - multiple-values return no data.

I guess it's something to do with how the dataset text strings are being processed, but I'd like to know what's happening. Has SSRS got a bug here?

Cheers

Canazei65

cporteous SSC Eights! Points: 966 More actions · Answer 3

I think i'm catching on now.

So that SQL is the dataset used for your parameter?

Ok, so what does the query that uses the parameter look like?

it should be something like this (although i dont know your data etc)

SELECT col1, Col2, Col3

FROM Table

WHERE CCGCode IN (@ParameterValue)

You need to use IN. This will let you pass a single value or multiple values from the parameter.

Also, (although it sounds like you already have it covered) you need to set the parameter to "Allow multiple values"

Are you at this stage already or does that help?

Canazei65 SSCommitted Points: 1696 More actions · Answer 4

Hi

Yep, that's exactly the setup and yes it does help (at least I know I've not done anything obviously wrong) Now I'm stuck though and wondering about steps to resolve it....

Cheers

Canazei65

cporteous SSC Eights! Points: 966 More actions · Answer 5

Hmm.

Ok So we have it like this...

@Param1 - multiple values

@Param2 - multiple values

@Param3 - multiple values

@Param4 - multiple values

Do they all feed into a single dataset or is it 4 seperate datasets? (or a combination)

ie.

SELECT col1, Col2, Col3

FROM Table

WHERE CCGCode IN (@Param1)

AND Col2 IN (@Param2)

AND Col3 IN (@Param3)

AND Col4 IN (@Param4)

Are all the other 3 parameters based on Union selects like below?

Have you tried selecting single values for the others & a multiple for the 4th? Does that have the same issue?

Also, if your parameter dataset for this 4th troublesome one is fairly static and as short as the list you detailed before you could try a few other options - create a static table listing them all and just reference the table in the parameter dataset (see if that helps)

Or, choose the "Specific Values" option and define them one by one.

Its a bit of trial & error with those things but it will narrow down where the trouble is.

Another one, if you can, is strip out the other parameters and just run it with the troublesome one. See if its the data rather than an SSRS bug. Same idea would be to just pop the main dataset into a query directly against the database in SSMS (if you have access) and manually type the parameters into the query ie IN ('CON_NS','CON_NON')

ta

Canazei65 SSCommitted Points: 1696 More actions · Answer 6

Cheers mate - some good suggestions there, I'll give them a bash and see how it goes.

I'll report back later.

Thanks again.

Canazei65