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

Help with Multiple Parameter Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 1:01 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:28 AM
Points: 194, Visits: 1,148
I have a Multiple Select Drop down list in Report Builder (@UnKnowns) parameter. It passes the values into my WHERE statement however it only works if I select only one selection at a time. If I try to select more than one value I get a error executing the query. How do I get it to pass multiple values across correctly?

The Drop Down parameter List looks like this

Labels Values
-ALL- -99
Race 01
Ethnicity 02
Gender 03
.....ect .....ect



WHERE

AND (@Unknowns IN('01','-99') AND (r.race='Unknown'))

OR (@Unknowns IN('02','-99') AND (r.Ethnicity='Unknown'))

OR (@Unknowns IN('03','-99') AND (r.Gender='Unknown'))

OR (@Unknowns IN('04','-99') AND (r.Religion='Unknown'))

OR (@Unknowns IN('05','-99') AND (r.Language='Unknown'))

OR (@Unknowns IN('06','-99') AND (r.SFFamilySize='0'))

OR (@Unknowns IN('07','-99') AND (r.DateOfBirth IS NULL))

OR (@Unknowns IN('08','-99') AND (r.AddressLine1 IS NULL))

OR (@Unknowns IN('09','-99') AND (r.HomelessStatus IN ('Unknown','Missing'))

OR (@Unknowns IN('10','-99') AND (r.HomelessBox='NOT CHECKED'))

OR (@Unknowns IN('11','-99') AND (r.PostalCode IS NULL))

OR (@Unknowns IN('12','-99') AND (r.PreferredProvider IS NULL))

OR (@Unknowns IN('13','-99') AND (r.VeteranStatus ='Missing'))

OR (@Unknowns IN('14','-99') AND (r.MaritalStatus ='Missing'))

OR (@Unknowns IN('15','-99') AND (r.EducationStatus ='Missing'))




***SQL born on date Spring 2013
Post #1522855
Posted Friday, December 13, 2013 5:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:19 PM
Points: 1,796, Visits: 5,799
What you need to know is that SSRS formats multiple selections in a parameter as a comma separated list, like this:

"01,-99"

So, when your query runs you are effectively saying

WHERE ('01,-99' in ('01','-99') and ...)

which is never going to evaluate as true.

I imagine that when you get this working, performance will be terrible because of all the "OR" clauses, but here is a quick way to get it working, and then you can work on performance.

Tally OH! An Improved SQL 8K “CSV Splitter” Function
By Jeff Moden, 2012/12/28 (first published: 2011/05/02)


Read the article above and you will find a lovely function for splitting small strings "DelimitedSplit8K".

You can use that here to split the parameter string back out into individual items and then your query will work like this:

SELECT ...
FROM ...
cross apply ssc.dbo.DelimitedSplit8KB(@Unknowns,',')

WHERE

AND ([Item] IN('01','-99') AND (r.race='Unknown'))

OR ([Item] IN('02','-99') AND (r.Ethnicity='Unknown'))

OR ([Item] IN('03','-99') AND (r.Gender='Unknown'))

OR ([Item] IN('04','-99') AND (r.Religion='Unknown'))

OR ([Item] IN('05','-99') AND (r.Language='Unknown'))

OR ([Item] IN('06','-99') AND (r.SFFamilySize='0'))

OR ([Item] IN('07','-99') AND (r.DateOfBirth IS NULL))

OR ([Item] IN('08','-99') AND (r.AddressLine1 IS NULL))

OR ([Item] IN('09','-99') AND (r.HomelessStatus IN ('Unknown','Missing'))

OR ([Item] IN('10','-99') AND (r.HomelessBox='NOT CHECKED'))

OR ([Item] IN('11','-99') AND (r.PostalCode IS NULL))

OR ([Item] IN('12','-99') AND (r.PreferredProvider IS NULL))

OR ([Item] IN('13','-99') AND (r.VeteranStatus ='Missing'))

OR ([Item] IN('14','-99') AND (r.MaritalStatus ='Missing'))

OR ([Item] IN('15','-99') AND (r.EducationStatus ='Missing'))



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1522899
    Posted Tuesday, December 17, 2013 2:25 PM


    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Yesterday @ 1:28 AM
    Points: 194, Visits: 1,148
    Thank you so very much, I also imagine that performance will suffer some and I will try to work on that. Luckily this wont be run often, more of a monthly spot check. I knew it would probably be some type of split list function I just had no idea how to implement it. I will give it a go tomorrow when I get to the office. Thanks again for your help. I hope to get enough experience and well versed enough to be able to help others instead of being a SQL leech as I currently am.



    ***SQL born on date Spring 2013
    Post #1523890
    Posted Tuesday, December 17, 2013 5:54 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 4:19 PM
    Points: 1,796, Visits: 5,799
    thomashohner (12/17/2013)
    Thank you so very much, I also imagine that performance will suffer some and I will try to work on that. Luckily this wont be run often, more of a monthly spot check. I knew it would probably be some type of split list function I just had no idea how to implement it. I will give it a go tomorrow when I get to the office. Thanks again for your help. I hope to get enough experience and well versed enough to be able to help others instead of being a SQL leech as I currently am.


    Hey, you are welcome and I'm sure you are not a "leech" - no-one would be helping out here if they didn't want to


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1523913
    Posted Thursday, January 2, 2014 9:02 PM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Saturday, May 24, 2014 10:58 PM
    Points: 79, Visits: 74
    Hi Thomas,

    You can try similar logic for splitting the coma delimited value to rows.

    ---
    DECLARE @delimitedstring varchar(8000) = '-ALL- -99,Race 01,Ethnicity 02,Gender 03'
    DECLARE @xmlvalue xml
    set @xmlvalue = N'<root><r>' + replace(@delimitedstring,',','</r><r>') + '</r></root>'

    select
    parameterlist.value('.','varchar(100)') as [delimited items]
    from @xmlvalue.nodes('//root/r') as t(parameterlist)
    ----

    As you mentioned, you can convert this in to a function which returns the table value:

    CREATE FUNCTION splitparameterlist
    (
    @delimitedstring varchar(8000)
    )
    returns @parameterlist table
    (
    parametervalue varchar(100)
    )
    AS
    BEGIN
    DECLARE @xmlvalue xml
    set @xmlvalue = N'<root><r>' + replace(@delimitedstring,',','</r><r>') + '</r></root>'

    insert into @parameterlist(parametervalue)
    select
    parameterlist.value('.','varchar(100)') as [delimited items]
    from @xmlvalue.nodes('//root/r') as t(parameterlist)
    RETURN
    END

    ---Sample Select statement--
    Select parametervalue from splitparameterlist('-ALL- -99,Race 01,Ethnicity 02,Gender 03')

    Reference link: http://www.kodyaz.com/articles/t-sql-convert-split-delimeted-string-as-rows-using-xml.aspx
    Post #1527340
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse