limiting prompt values on variable prompts

user12863665

Hi, i have 2 variable Prompts with below names

Variable Prompt Names:

1) Prompt 1

2) Prompt 2

'Prompt 1' has static values like A,B,C,D.

'Prompt 2' has static Values Like W,X,Y,Z.

My Requirement is if i select value 'A' in 'Prompt 1' it should display only 'W' and 'X' in 'Prompt 2'.

But When i select value 'B' in 'Prompt 1',  i want to display All values(W,X,Y,Z) in 'Prompt 2'.

To get this fixed, i created Presentation variable on 'Prompt 1' with the name 'PV_A' and write the SQL Statement like below in 'Prompt 2' SQL statement section

Case when '@{PV_A}' = 'A' THEN 'W' END FROM SUBJECT AREA

UNION

Case when '@{PV_A}' = 'A' THEN 'X' END FROM SUBJECT AREA

UNION

Case when '@{PV_A}' = 'B' THEN 'W' END FROM SUBJECT AREA

UNION

Case when '@{PV_A}' = 'B' THEN 'X' END FROM SUBJECT AREA

UNION

Case when '@{PV_A}' = 'B' THEN 'Y' END FROM SUBJECT AREA

UNION

Case when '@{PV_A}' = 'B' THEN 'Z' END FROM SUBJECT AREA

But with this logic 'Prompt 2' is not updating when i select 'Prompt 1' values.

Anyone suggest me how can i get this fixed and also suggest me is there is any simple way to get this solved?

Thanks in advance.

ArijitC

I took your query modified (added select after union) a bit and it is working...

SELECT Case when '@{PV_A}' = 'A' THEN 'W' END FROM "SUBJECT AREA " 

UNION  SELECT Case when '@{PV_A}' = 'A' THEN 'X' END FROM "SUBJECT AREA" 

UNION  SELECT Case when '@{PV_A}' = 'B' THEN 'W' END FROM "SUBJECT AREA"  

UNION  SELECT Case when '@{PV_A}' = 'B' THEN 'X' END FROM "SUBJECT AREA"  

UNION  SELECT Case when '@{PV_A}' = 'B' THEN 'Y' END FROM "SUBJECT AREA"  

UNION  SELECT Case when '@{PV_A}' = 'B' THEN 'Z' END FROM "SUBJECT AREA" 

Two more thing to remember...

1. Add a valid subject area .

2. uncheck show apply / reset button

Christian Berg-0racle

Beaten by Arijt. @user12863665 your code was just wrong

Christian Berg-0racle

@user12863665 Can you please close this thread? The community lives or dies with active participation and leaving threads as open orphans doesn't help other users. Thank you.