April 17, 2019 at 4:41 pm
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.
April 17, 2019 at 6:51 pm
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.
April 18, 2019 at 12:49 pm
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.
April 18, 2019 at 1:59 pm
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)
MattF
April 18, 2019 at 4:04 pm
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. 🙂
April 18, 2019 at 5:57 pm
Never thought of that, thanks for the follow up!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply