SSRS, cascade parameters, how to make it work for multiple choice (WHERE in ...)

• mario17

  SSCertifiable

  Points: 6079

  More actions

  August 22, 2013 at 1:14 pm

  #282390

  Hi,

  I created cascade params for my report, it works fine for single selection, but when I check let say 2 params, (AAA, BBB) it doesn't work.

  So it's only for MULTI VALUES IN PARENT CASCADE PARAM, child is OK

  My dataset query:

  select subCategory from t1

  where t1.Category in @Category --works OK with = for single selection

  or @Category = 'All'

  --error

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

  in SSRS/Query Designer / Define QUery Parameters window:

  Parameter Value = AAA ===> runs fines,returns 100 rows

  Parameter Value = AAA,BBB ===> nothing

  Parameter Value = (AAA,BBB) ===> nothing,

  etc..

  and again: WHERE t1.Category in (@Category) ...

  Even MSN aknowledge this problem and tricky way to solve it, very bizarre!!!

  Is there anything else I have to add/change?

  I googled and found this on http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b42af8a-d84b-4224-891e-53043b7a0614/how-to-pass-multiple-values-to-a-parameter-while-cascading

  ... =split(Join(Parameters!XXX.value,","),",")

  But where to put this vbs?

  Tx

  M

• Koen Verbeeck

  SSC Guru

  Points: 259155

  More actions

  August 22, 2013 at 1:19 pm

  #1644102

  Unfortunately it is not straight forward to work with multi-value parameters.

  SQL Server Reporting Services Using Multi-value Parameters[/url]

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• mario17

  SSCertifiable

  Points: 6079

  More actions

  August 22, 2013 at 4:15 pm

  #1644178

  Eventually I made i work BUT only from Query Designer loading string delimited by ',' into table and use this table

  But I still can not select multi values on parent Parameter.