Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Condition for SSRS multi select parameters Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 7:31 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 270, Visits: 1,609
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.

Post #1451794
Posted Sunday, May 12, 2013 2:50 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 270, Visits: 1,609
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.
Post #1451937
Posted Monday, May 13, 2013 10:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:41 PM
Points: 1,208, Visits: 2,508
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
Post #1452209
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse