SQL Condition for SSRS multi select parameters

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  May 10, 2013 at 7:31 pm

  #277375

  Hi All,

  I can't figure out the best way to implement this..so here is my simplified table:

  declare @tbl table(Id int identity (1,1), Para varchar(200), ParaType varchar(150))

  insert into @tbl

  select 'Paragraph1...', 'Type1,Type3,Type4' union all

  select 'Paragraph2...', 'Type1,Type3' union all

  select 'Paragraph3...', 'Type1,Type3,Type5' union all

  select 'Paragraph4...', 'Type3,Type4' union all

  select 'Paragraph5...', 'Type1,Type2,Type3,Type4' union all

  select 'Paragraph6...', 'Type3,Type5' union all

  select 'Paragraph7...', 'Type4,Type5' union all

  select 'Paragraph8...', 'Type3,Type5' union all

  select 'Paragraph9...', 'Type5' union all

  select 'Paragraph10...', 'Type1,Type5'

  select * from @tbl

  Each type is one check box (in a drop-down) on my SSRS report which lets the users select multiple types.

  So if user selects Type2 and Type4 I would expect to see strings that have Type2 or Type4 words in them:

  select 'Paragraph1...', 'Type1,Type3,Type4' union all

  select 'Paragraph4...', 'Type3,Type4' union all

  select 'Paragraph5...', 'Type1,Type2,Type3,Type4' union all

  select 'Paragraph7...', 'Type4,Type5'

  Used Jeff's splitter but not sure where to go from there. Thanks.

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  May 12, 2013 at 2:50 pm

  #1614464

  Got it right finally. Maybe not the best solution but here's what I did. Split up both the input SSRS string and the "Paratype" strings, joined the two result set and finally concatenated the types so that they look "Type1, Type2, Type3" on the report itself.

• Jason Wolfkill

  SSCrazy Eights

  Points: 9832

  More actions

  May 13, 2013 at 10:27 am

  #1614687

  clayman (5/12/2013)

  ---

  Got it right finally. Maybe not the best solution but here's what I did. Split up both the input SSRS string and the "Paratype" strings, joined the two result set and finally concatenated the types so that they look "Type1, Type2, Type3" on the report itself.

  This is going to get very unwieldy if your table gets much bigger than a few thousand rows (even then, it might bog down quite a bit). If it's possible for you to change the table definition to avoid storing multiple comma-delimited values in a single column, I would suggest that rather than splitting the comma-delimited values for EVERY row every time you run this query.

  Jason Wolfkill