Exclude all when no value is selected

[mria.ina]

Hi

I am facing the following request:
Prompt Page needs two boxes: Prompt A points to values from table A and Prompt B points to values from table B. The desired outcome is:

If values are entered for prompt A, apply filter. If not, exclude all results from table A
UNION
If values are entered for prompt B, apply filter. If not, exclude all results from table B

I have tried several appraches but nothing seems to work, can you please provide some help on the matter?

Thanks

[BigChris]

In your first query, you could try something like:

(?PromptA? is not missing and [FieldA] in ?PromptA?) or 0=1

and similarly in your second query
(?PromptB? is not missing and [FieldB] in ?PromptB?) or 0=1

Then union the two together. I haven't tested it myself, but it might be worth a try.

[cognos810]

Hello mria.ina,
Another approach could be, in the prompt boxes add a static choice as 'All' and make it the default selection.

Then in Table A query, add a filter with expression, ?promptA? <> 'All'
and in Table B query, add a filter with expression, ?promptB? <> 'All'

-Cognos810