Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

limiting prompt values on variable prompts

Received Response
2
Views
3
Comments
Rank 6 - Analytics Lead

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • Rank 6 - Analytics Lead

    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

    pastedImage_0.png

    pastedImage_1.png

  • Rank 2 - Community Beginner

    Beaten by Arijt. @user12863665 your code was just wrong

  • Rank 2 - Community Beginner

    @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.

Welcome!

It looks like you're new here. Sign in or register to get started.