Filter based on prompt values

[sanchoniathon]

Hi to all,

I'm trying to apply the following logic with no success for now: Once the report runs, the user can eighter click on Yes or No and if Yes then the user can select on next prompt page if hr wants to add both codes WW and ZZ or just one of them to the default query FILTER.

This is the default query filter :
code in ('AA', 'BB', 'CC', 'DD', 'NN', 'OO', 'XX', 'YY')


case ?prompt_Add?
when 2 then ( code in ('AA', 'BB', 'CC', 'DD', 'NN', 'OO', 'XX', 'YY') )
when 1 then  ( IF (?prompt_Add_Values? ) in ('Z', 'W') then code in ('AA', 'BB', 'CC', 'DD', 'NN', 'OO', 'WW', 'XX', 'YY', 'ZZ')
                           ELSE IF (?prompt_Add_Values? ) in ('Z') THEN code in ('AA', 'BB', 'CC', 'DD', 'NN', 'OO', 'XX', 'YY', 'ZZ')
                          ELSE code in ('AA', 'BB', 'CC', 'DD', 'NN', 'OO', 'WW', 'XX', 'YY')

END

p.s
1= Yes
2 = No

[BigChris]

Ok, so instead of that you need something along the lines of:

Code Select Expand

?Prompt Add? = 'No' and Code in ('AA', 'BB', 'CC', 'DD', 'NN', 'OO', 'WW', 'XX', 'YY', 'ZZ')
or
?Prompt Add? = 'Yes' and ?Prompt_add_values? in ('Z', 'W') and code in  ('AA', 'BB', 'CC', 'DD', 'NN', 'OO', 'WW', 'XX', 'YY', 'ZZ')
or
?Prompt Add? = 'Yes' and code in ('AA', 'BB', 'CC', 'DD', 'NN', 'OO', 'WW', 'XX', 'YY')

[sanchoniathon]

Thank you BigChris for getting me well started on how the filter should lɔok like.

I think i'm almost there but cannot seem to quite finalise it as for withe the following refined logic it still always seems to assume we want ALL 2 codes to be added even i only selected 'W' to be added to the default  filter. The reason i know this is that i decided to add the "code" column to our list report to see what were the records beeing returned and i would of think that all the codes ! th 'W' code would be taken into consideration BUT it also seems to add the 'Z' code?

Any more suggestions anyone ?


?Prompt Add? = '2' and ?Prompt_add_values? is null and Code in ('A', 'B', 'C', 'D', 'N', 'O', 'X', 'Y') )

or ( ?Prompt Add? = '1' and ?Prompt_add_values? in ('W') and Code in ('A', 'B', 'C', 'D', 'N', 'O','W', 'X', 'Y') )

or ( ?Prompt Add? = '1' and ?Prompt_add_values? in ('Z') and Code in ('A', 'B', 'C', 'D', 'N', 'O', 'X', 'Y', 'Z') )

or ( ?Prompt Add? = '1' and ?Prompt_add_values? in ('W', 'Z') and Code in ('A', 'B', 'C', 'D', 'N', 'O', 'W', 'X', 'Y', 'Z') )


By the way, the main goal of this somehow complex filter is to allow the end user to ADD Codes 'W' and/or 'Z' to the default filter beeing Code in ('A', 'B', 'C', 'D', 'N', 'O', 'X', 'Y')

[BigChris]

So can I suggest a slightly different approach?

?Prompt to Use? = 'Default' and Code in ('A', 'B', 'C', 'D', 'N', 'O', 'X', 'Y')
or
?Prompt to Use? = 'Default + W' and Code in ('A', 'B', 'C', 'D', 'N', 'O', 'W', 'X', 'Y')
or
?Prompt to Use? = 'Default + Z' and Code in ('A', 'B', 'C', 'D', 'N', 'O', 'X', 'Y', 'Z')
or
?Prompt to Use? = 'Default + W + Z' and Code in ('A', 'B', 'C', 'D', 'N', 'O', 'W', 'X', 'Y', 'Z')

[sanchoniathon]

Thanks once again.

Are we gerting rid of 1 of 2 prompts i'm currently using in this logic ?

Regards !