Multi valued parameter is passing multiple values as one string

  • 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?

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

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

  • 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.

  • 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?

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

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

  • 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.

  • 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:

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

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