Multi valued parameter is passing multiple values as one string

Question

Post reply

Multi valued parameter is passing multiple values as one string

craig.bobchin

SSCrazy

Points: 2298
More actions
November 5, 2022 at 5:03 pm

#4109088

So I've got this report that has two date parameters and a multi choice var char parameter. When I choose 1 option it runs fine, but when I select more than one it failes because the parameter is being passed as a single string with no commas seperating the values.

I tried adding an expression to the parameter that adds a , to it but then get an error message "The Value expression for the query parameter ‘@PlaceofService’ contains an error: Operator '&' is not defined for type 'Object()' and string ",". (rsRuntimeErrorInExpression) I got the same if I use the + instead of the &.
Does anyone have any idea what might be causing this behavior?

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

pietlinden SSC Guru Points: 63503 More actions · Answer 2

where's your multi-valued parameter defined? In the stored procedure or in the report? I would try using IN() with the report multi-valued parameter and see if that fixes it.

craig.bobchin SSCrazy Points: 2298 More actions · Answer 3

pietlinden wrote:

where's your multi-valued parameter defined? In the stored procedure or in the report? I would try using IN() with the report multi-valued parameter and see if that fixes it.

I'm using an In() as part of the where clause.

I have the parameter defined in the report. I have the dataset parameters set as well. From the report I can see that the parameter is passing the multiple values as a string without any commas between them.

There's 4 possible selections defined in the parameter 21, 22, 31, 61. If I choose one the report runs fine. if I choose more than one say 21 and 22, it passes 2122 instead of 21, 22.

What would cause this behavior?

Jo Pattyn SSC-Dedicated Points: 32344 More actions · Answer 4

Do you work with the "Join" function in SSRS for passing multiple values?

https://devinknightsql.com/2010/10/08/ssrs-join-function/

craig.bobchin SSCrazy Points: 2298 More actions · Answer 5

Jo Pattyn wrote:

Do you work with the "Join" function in SSRS for passing multiple values?
https://devinknightsql.com/2010/10/08/ssrs-join-function/

I haven't yet done so. I've never need to. All my other multi choice parameters have passed the selections as a coma seperated string in teh past. I'll give it a look though.

craig.bobchin SSCrazy Points: 2298 More actions · Answer 6

Jo Pattyn wrote:

Do you work with the "Join" function in SSRS for passing multiple values?
https://devinknightsql.com/2010/10/08/ssrs-join-function/

Join didn''t work either.

One thing I noticed however that struck me as strange, when I execute the dataset query/SP from within the report in Visual Studio, it is passing the multivalued parameter as a single tring as well.

What I typed in:

What it thinks I typed in: