OK you reporting geniuses - Here's my quandry. I've got a report where I want the select options to suppress items from the drop down list that have already been selected in other criteria. I'm using this syntax and it works:
kc <> <<p3>> and kc <> <<p5>>
However I'd like to be able to make <<p3>> and <<p5>> multi select, and this does not work for that. I've tried
kc not in(<<p3>>, <<p5>>)
but I got an error for bad syntax. Anyone know if it is possible to do what I'm trying? Is there syntax that would deal with that?
Hope this makes sense. Any suggestions would be great!
Hi Brian,
Since the parametes for the where are multi selects you will have to do something like the following:
not charindex(',' + convert(varchar,season)+',',','+<<p1>>+ ',' + <<p2>>+',')>0
Both parameters are comma delemited so I combine the two into one comma delimited string then do a charindex on it.
Hope this helps.
Jon
Thanks Jon. This is definitely getting me in the right direction. My data is a string so this is what my where clause for <<p6>> looks like:
not
charindex(','+'"'+kc+'"'+',',','+<<p4>>+ ',')>0
then for <<p8>>:
charindex(','+'"'+kc+'"'+',',','+<<p4>>+ ','+<<p6>>+',')>0
but I run into issues when I get to the third instance for <<p10>>:
not charindex(','+'"'+kc+'"'+',',','+<<p4>>+ ','+<<p6>>+','<<p8>>+',')>0 Begining with this one, the first value selected in <<p8>> is still available in the drop down list when I get to <<p10>>. So <<p4>> looks like this:[ "111","112","113" ] and those three values are not available in <<p6>>. <<p6>> is [ "114","115","116" ] and none of those six values are available in <<p8>>. <<p8>> is [ "117","118","119" ], but when I get to <<p10>>, "117" is available in the drop down, though "118" and "119" along with the six others are not. So I'm scratching my head on this one. And of course, this goes on up through <<p30>> (!) with the first value selected in any <<pN>> not being suppressed from subsequent <<pN>> after <<p8>>. So that's the little rabbit hole I've dug myself into...
charindex(','+'"'+kc+'"'+',',','+<<p4>>+ ','+<<p6>>+','<<p8>>+',')>0
Begining with this one, the first value selected in <<p8>> is still available in the drop down list when I get to <<p10>>.
So <<p4>> looks like this:[ "111","112","113" ] and those three values are not available in <<p6>>. <<p6>> is [ "114","115","116" ] and none of those six values are available in <<p8>>. <<p8>> is [ "117","118","119" ], but when I get to <<p10>>, "117" is available in the drop down, though "118" and "119" along with the six others are not.
So I'm scratching my head on this one. And of course, this goes on up through <<p30>> (!) with the first value selected in any <<pN>> not being suppressed from subsequent <<pN>> after <<p8>>.
So that's the little rabbit hole I've dug myself into...
Not sure it seems like it should work can you post what you have for p10. Also you said that you have alot of parameters. The where clause for gooesoft_report_parameter is only 255 characters.
Another option if we don't get this working is to create a web report/app. Have tesstura lauch your own web based parametrs page. IN here you could code your own logic for the parametrs.
maybe post the setup xml for the report and I could take a look.
Yeah, I was worried about the lenght of the clause, but I was relieved it fit even in the last criteriaThe last line (p30) looks like this:
not charindex(','+'"'+kc+'"'+',',','+<<p4>>+ ','+<<p6>>+','<<p8>>+','<<p10>>+','<<p12>>+','<<p14>>+','<<p16>>+','<<p18>>+','<<p20>>+','<<p22>>+','<<p24>>+','<<p26>>+','<<p28>>+',')>0
Here's the xml
Fixed the attachment. Also realized the clause was unreadable at the end. Here's another try on that:
not charindex(','+'"'+kc+'"'+',',','+<<p4>>+ ','+<<p6>>+','<<p8>>+','<<p10>>+','<<p12>>+','<<p14>>+','<<p16>>+','<<p18>>+','<<p20>>+','
<<p22>>+','<<p24>>+','<<p26>>+','<<p28>>+',')>0
Oh good grief. I started skipping a leadin [+] with p8. Fixed that, and now it works like a charm.
Thanks again Jon - your prescription was spot on.