SSRS Multi-Select parameter weridness

  • Kimberlin

    Mr or Mrs. 500

    Points: 502

    i'm not an SSRS guru but in the past the behavior of making a parameter multi-select would pass a comma delimited string.

    ie. selecting red and blue and green would give you 'red,blue,green' causing one to have to use some form of string split.

    However, I am working with a report that is passing the multi-select parameter as N'red',N'blue',N'green' which doesn't work in my string split function but can be used with a simple IN (@parameter).

    I haven't seen this before and i can't see anything wonky in the report definition that would make me think this was somehow intentionally done. Anybody have any ideas as to why or how this is happening?

     

    edit: the developer thinks it might be due to his newer version of reportbuilider/BIDS/VS but I haven't seen anything about a change to feature online.

    • This topic was modified 3 months ago by  Kimberlin.
  • Kimberlin

    Mr or Mrs. 500

    Points: 502

    ok now i feel like i'm crazy. is this the default behavior? i could have sworn when working with SSRS a couple years ago we had to run all those multi-select parameters through string split functions to get them to work.

  • x

    SSC-Insane

    Points: 23349

    Yes, its the default behavior. You have to take an extra step to send that parameter as a comma delimited string to your data source (which would be a stored procedure).

    In the dataset that would accept that parameter, you need to go to the "parameters" tab in the dataset's "properties" dialog box and then enter the expression to produce the comma delimited string, here's an example from one I did just last week, this is in the right hand text box that's for the "parameter value":

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

    where "MyParameter" is the one you set in the reports parameter settings to accept multiple values.

    • This reply was modified 2 months, 4 weeks ago by  x.
  • MattF

    SSCrazy

    Points: 2280

    You use the string split function in the report itself to display the multi-select parameter values. In a stored procedure you would use do something like the following to assign the parameter string to a variable and handle the comma separation:

    --allow for multi-value parameters by inserting single quotes between comma separated values

    SET @strParameter = Char(39) + Replace(@ParameterPassedFromReport,',',Char(39)+','+Char(39)) + Char(39)

     

  • Kimberlin

    Mr or Mrs. 500

    Points: 502

    Thanks so much for the replies. So I discovered something. If the query is text in the dataset it passes it as 'red','blue','green' but if tick the radio button for stored procedure and have the parameter of the SP defined as a varchar/nvarchar it passes it as 'red,blue,green' . in my last job every report result set was an SP for easy changes so that is the behavior I am used to. at this job they do all their queries as text directly in the SSRS dataset. So i'm not completely crazy. 🙂

  • x

    SSC-Insane

    Points: 23349

    Never thought of that, thanks for the follow up!

     

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

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