Expression in the detail filter

[cognosun]

I have a multi-select checkbox prompt 'p1' with 2 static values (a,b), and i need to write this logic in main query (without using if or case conditions)

?p1? = a and column1 = 'T'
or
?p1? = b and column1 = 'F'
or
?p1? in (a,b) and column1 = 'T'

Parsing error is occurring at third line, can anyone help me with a better way to code it ?

Many thanks in advance.

[BigChris]

Just checking...is that a typo, or is "columne1" correct?

[cognosun]

it's a typo :-)

it's column1, same as above lines.

[Lynn]

If you need it to be multi-select then you have to use "in ?P1?" syntax. I assume first situation is when ONLY a is selected, second for ONLY b, and third for both selected. Not sure if that is a correct assumption or not but maybe this will help you get going. Notice the parentheses are important to group the items for each scenario.

I'm sure there are other ways to do it so perhaps others will offer alternatives.

Code Select Expand


( 'a' in ?p1? and 'b' not in ?p1? and [column1] = 'T' )
or
( 'b' in ?p1? and 'a' not in ?p1? and [column1] = 'F' )
or
( 'a' in ?p1? and 'b' in ?p1? and [column1] = 'T' )


[cognosun]

first 2 lines are giving correct output, but third line is giving no data ( neither Y or N)

( 'a' in ?p1? and 'b' in ?p1? and [column1] = 'T' )

[MFGF]

first 2 lines are giving correct output, but third line is giving no data ( neither Y or N)

( 'a' in ?p1? and 'b' in ?p1? and [column1] = 'T' )

What is contained in ?p1? - have you verified? Try adding a layout calculation with a ParamValue() function to see...

MF.

[cognosun]

if I select 'a' ?p1? will contain 'a'
if I select 'b' ?p1? will contain 'b'
if I select 'a' & 'b' ?p1? will contain 'a','b'

[MFGF]

if I select 'a' ?p1? will contain 'a'
if I select 'b' ?p1? will contain 'b'
if I select 'a' & 'b' ?p1? will contain 'a','b'

Have you verified this or are you assuming?

MF.

[cognosun]

I've verified :-)

[Lynn]

Do you have different use and display values?
What do you mean by "neither Y or N" when you describe your no data situation?
This is a relational package, correct?
Can you share your complete expression or other details that will help troubleshoot?

Attached is a spec against the GO Sales (query) package and it works exactly right for me, so perhaps there is more you havent' mentioned?

[cognosun]

Lynn...here are few more details:

Do you have different use and display values? // no both use and display values are same


What do you mean by "neither Y or N" when you describe your no data situation?  // I mean I'm getting no data available, when both a and b are selected//


This is a relational package, correct? // it is relational


But just to clarify...if we select both a and b, obviously we get both the values on report right, unless we filter them in the detail section.

[Lynn]

Lynn...here are few more details:

Do you have different use and display values? // no both use and display values are same


What do you mean by "neither Y or N" when you describe your no data situation?  // I mean I'm getting no data available, when both a and b are selected//


This is a relational package, correct? // it is relational


But just to clarify...if we select both a and b, obviously we get both the values on report right, unless we filter them in the detail section.

I gave you a working example spec in my last post that does exactly what you specified in your original post, as I understood it. My understanding is illustrated in my first response to you.

Your prompt responses of a or b are static values in the prompt control, not values in the data as you've described things. Selecting a AND b in the prompt will restrict the data to [column1] = 'T' which is exactly the same as when only option a is selected.

Look at the example spec I provided in my last post and you may figure out where what you said you want departs from what you actually need.