SSRS - Issues with multi-select parameters started about 6 months ago.

Question

I use Visual Studio 2015 with SSDTs installed. We have used this tool for several years but starting about 6 months ago, we started having issues with Parameters that allow multiple selections. If you select one value, the report runs just fine. As soon as you choose more than one value, you start getting the error:

"An expression of non-boolean type specified in a context where a condition is expected, near ','."

There where clause is where smpl_lvl in (@smpl_lvl)

This only happens when you choose more than one value. The datatype of the column is char(8). I would not think this would cause any issues with a string return.

Any suggestions. This issue is costing me many hours each time it arises.

Answer 1

Hi @Clark, Michelle ,

your paramter is named "smpl_lvl ",right? have you select the multiple value for the parameter?
and what is the smpl_lvl data type ? varchar or nvarchar?

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Yes, I checked "Allow multiple values" and the parameter name is smpl_lvl; the data column name is smpl_lvl. The datatype of the parameter is text and the datatype of the data column is char(8).

Answer 3

we have included in the where clause, OR @smpl_lvl = '' as well as a variation OR LEN(@smpl_lvl) < 2. Neither work.

Is there a way to test what is actually contained in the parameter that is being passed to the query? I've tried adding a textbox with the parameter in it but get #ERROR displayed. I thought of using an expression with the JOIN function which gives me the values but I want to see the values and commas just as it is passed.