Post reply

SSRS help: non-queried parameters / multiple values

  • July 27, 2009 at 7:39 am

    #208682

    I have a sales report that has a multi-valued parameter labeled 'State'. It is a non-queried value, but I could create a data set if I need to. I was hoping not to. values are below

    label value

    IL IL

    KS KS

    OH OH

    MI MI

    Territory 1 IL,KS,MI

    For Territory 1; the states include IL,KS,MI. how can I create a label and value to reflect this??

    I have tried setting the value of label Territory 1 to IL,KS,MI <<>>

    In the sql statement I have: State in (@state)

    Everthing works if I individually select IL,KS,MI....but not if I select the label "Territory 1"

    Any ideas, would be a great help. PLEASE, I am stuck

  • July 28, 2009 at 11:16 am

    #1031196

    If you use a stored procedure instead it will send the parameter as a delimited string. You can then parse the string in TSQL (into a temp table, for example) and join it with your results (or filter with an "IN" and a select statement). There are several ways to do this, but I would recommend using a tally table "http://qa.sqlservercentral.com/articles/T-SQL/62867/)[/url]

  • July 28, 2009 at 11:22 am

    #1031201

    I will check out the link and hopefully test it tonight, Thanks for your reply..what is the easiest method?

  • July 28, 2009 at 12:19 pm

    #1031233

    Doesn't really matter as far as difficulty. What you need to be concerned with is performance. My guess (in other words check for yourself) is that given as small as you list is IN might perform better... but again, I wouldn't know without testing.

  • July 28, 2009 at 1:48 pm

    #1031301

    I would guess it's not working the way you have it currently as the target datatype for the parm is a varchar, so SSRS is likely stringing the selected parm values for you. So selecting IL really returns 'IL' to the where clause, making it "... state IN ('IL') ....." So with the comma separated list, it's likely returned as 'IL,KS,MI' which obviously isn't going to match (ie .....state in ('IL,KS,MI').....).

    You could try making the tri-state value something like IL','KS','MI (so leave of the opening and closing quotes) but this would only work if we assume SSRS doesn't parse the contents and replace every ' with '' .

    I like Tim's idea of using a proc and parsing it out 🙂

    Steve.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply