Help with Optional prompt filter

[pcog]

Hi all,

I have a report which has the below OPTIONAL filter. When I enter ?p1? parameter and don't enter for the second parameter, I'm getting all the results for all the values (not only the parameter value I selected, but everything from COLUMN1)

( [COLUMN1] in ?p1? and [COLUMN2] = 'X')
OR
( [COLUMN1] in ?p2? and [COLUMN2] = 'Y')

The result set is giving all the values, but I want only the values I selected? If I make the prompt as required it is working, but not with optional.
Is it even possible with filters or should I create a separate query for each filter and union it? I really hope there should be some other ways to get it done.

Thanks in advance.

[teresa.danna@gmail.com]

Could you walk us through an example of what you would like to see and what you are entering into ?p1? and ?p2? ? I suspect the issue is that you only need one parameter, but I'm not sure.

[Lynn]

Adding to Teresa's question, can we assume that users should only enter a choice for p1 or p2, but not both? It is difficult to determine what you are trying to achieve.

As a totally blind shot in the dark, I'd say split the two expressions into separate filters and make each one optional. Since you aren't providing both p1 and p2 I suspect the filter is getting ignored when it is optional because you haven't provided values for both parameters referenced in the expression. Splitting them up will allow them to be evaluated properly. But if the user could legitimately enter a value for both p1 and p2 and expect a result then the filters would be put together as AND conditions, not OR.

If you provide better detail on your requirements and possible scenarios it could improve the advice you get.

[pcog]

Could you walk us through an example of what you would like to see and what you are entering into ?p1? and ?p2? ? I suspect the issue is that you only need one parameter, but I'm not sure.

?p1? and ?p2? are values from COLUMN1. Ex: 100, 100, 101, 102 etc so, this column may have multiple same values and each value has different COLUMN2 values Ex. X or Y

COLUMN1 COLUMN2
100   X
100   Y
101   X
102   X

Even I thought like what you said, only one parameter..but, since the COLUMN1 values may be same and user wants to select 100 for only Y. in this case because of the filter we get both, but what i want is 100 and Y only not X.
Hope i'm not confusing :(

[pcog]

Adding to Teresa's question, can we assume that users should only enter a choice for p1 or p2, but not both? It is difficult to determine what you are trying to achieve.

As a totally blind shot in the dark, I'd say split the two expressions into separate filters and make each one optional. Since you aren't providing both p1 and p2 I suspect the filter is getting ignored when it is optional because you haven't provided values for both parameters referenced in the expression. Splitting them up will allow them to be evaluated properly. But if the user could legitimately enter a value for both p1 and p2 and expect a result then the filters would be put together as AND conditions, not OR.

If you provide better detail on your requirements and possible scenarios it could improve the advice you get.

Thanks for your reply Lynn.
User have a choice to select only p1 or p2 or both.
I tried split the filter in separate, but it is not working either. It will only work either of the prompt selected, but if we select both we are getting nothing.

[teresa.danna@gmail.com]

I think the issue is that you are trying to make two selections from column 1 and make it sync with column 2. What you really need is 1 parameter for column 1 and one parameter for column 2.

I agree with Lynn to split them up. If you split them up and make them optional on the prompt page, then you can get around this whole thing. IE: two filters:
[Column1] in (?P1?)
[Column2] in (?P2?)
If you make these optional then the user could select which they want, nothing at all, or just one, not the other.

If this doesn't sound appropriate for you though, you could also try the following as a required filter:

(?P1?='Y' and ?P2?=100) or (?P1?='X')

I used P1 as values from column 1, and P2 as values from column 2. This would get you either 100 and Y, or anything associated with the X value in column 1.

[MFGF]

I agree with Lynn...

That's a very smart move! The alternative is, well, unthinkable! ;)

Uh oh. Did I say that publicly? Now I'm in big trouble :)

[pcog]

I think the issue is that you are trying to make two selections from column 1 and make it sync with column 2. What you really need is 1 parameter for column 1 and one parameter for column 2.

I agree with Lynn to split them up. If you split them up and make them optional on the prompt page, then you can get around this whole thing. IE: two filters:
[Column1] in (?P1?)
[Column2] in (?P2?)
If you make these optional then the user could select which they want, nothing at all, or just one, not the other.

If this doesn't sound appropriate for you though, you could also try the following as a required filter:

(?P1?='Y' and ?P2?=100) or (?P1?='X')

I used P1 as values from column 1, and P2 as values from column 2. This would get you either 100 and Y, or anything associated with the X value in column 1.

Thanks Teresa for your reply.
The solution you gave
[Column1] in (?P1?)
[Column2] in (?P2?) working with fine with one issue. As I mentioned in my earlier posts COLUMN1 has same values for COLUMN2. and if user selects 100 for COLUMN1 prompt and wants to see only the 'X' along with other values from COLUMN1-101 which is for COLUMN2 'Y'. So, they MUST select X & Y for COLUMN2 prompt while running the report. In this case we get X & Y for 100, but we want only X for 100.

[Lynn]

So the question becomes how much of the filtering is going to be driven by user prompts and how much is going to be driven by hard coded values in your filter.

There really isn't any way to know which values they might select for P1 correspond to which values they might select for P2 given the information you have indicated so far.

Are the values you provide a sample of a much larger set of data points or is there a limited set of combinations? Can you concatenate column1 and column2 and use a multi select prompt to let them choose the specific combinations they need? That might get complicated since you want each to be optional.

Perhaps go back to your original approach which worked for you except when you make it optional. To get around that problem, provide a default value for both of your prompts that is some value that could never happen in reality. Then alter your expression to address that scenario. Not sure I've got that exactly right, but the intention is to ignore the condition if the default value is present.

Code Select Expand


( [COLUMN1] in ?p1? and [COLUMN2] = 'X' and ?p1? <> 'JUNK' )
OR
( [COLUMN1] in ?p2? and [COLUMN2] = 'Y' and ?p2? <> 'JUNK' )


[Lynn]

That's a very smart move! The alternative is, well, unthinkable! ;)

Uh oh. Did I say that publicly? Now I'm in big trouble :)

Oh very funny muppet!! I know where you live, but if you find a flaming bag of poo on your doorstep one day that doesn't mean I put it there ;)

[MFGF]

Oh very funny muppet!! I know where you live, but if you find a flaming bag of poo on your doorstep one day that doesn't mean I put it there ;)

Hee hee. The perfect house gift! ;)