SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Condition for SSRS multi select parameters


SQL Condition for SSRS multi select parameters

Author
Message
kiril.lazarov.77
kiril.lazarov.77
SSC Eights!
SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)

Group: General Forum Members
Points: 902 Visits: 2248
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
kiril.lazarov.77
SSC Eights!
SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)

Group: General Forum Members
Points: 902 Visits: 2248
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.
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2720 Visits: 2582
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
Blog: SQLSouth
Twitter: @SQLSouth
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search