Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

How to limit prompt based on other selected prompt values(Single/Multiple)

Received Response
52
Views
8
Comments
user652652
user652652 Rank 4 - Community Specialist
edited Aug 13, 2024 5:58PM in Oracle Analytics Cloud and Server

Hi Experts,

I have data like this

 

CountryCurrnecy
INDIAINR
USAUSD
UAEDHM
THAILAND

BHT

We have two prompts 1. Country  2. Currency

When user select one country only(Either India/USA/..) the currency prompt will show respective currency code.- This I know we can achieve it by 'Limit by' Option.

But if user select multiple Countries or select 'All' option then currency code should be always 'USD'.

How we can achieve it in OBIEE.

Thanks,

Answers

  • Syedsalmancs110
    Syedsalmancs110 Rank 6 - Analytics Lead

    How many combination of Country and Currency we are talking about?

    If it is as less as 4 as mentioned in example then you can go for below approach.

    Step 1 :For Country Column Prompt define a Presentation Variable for prompt selected value to be stored in the defined presentation variable.

    pastedImage_0.png

    STEP 2: For Currency Column Prompt edit "Prompt For Column" and make it formula based as shown in below example.

    pastedImage_1.png

    STEP 3: Set rest of the Currency Column Properties as shown below

    pastedImage_2.png

    STEP 4: Save and Test the Prompts.

    a) With no country value selected

    pastedImage_3.png

    b) With one country value selected

    pastedImage_4.png

    c) With more than one Country value selected

    pastedImage_5.png

    pastedImage_6.png

    pastedImage_7.png

    Please note if there is a way we can find to count number of records within a Presentation Variable then we don't have to write formula as big as shown in STEP 2, instead a simpler case statement could be used in such a case. (I tried but din't find a way on how to count number of values within a Presentation Variable)

    Disclaimer the above explained solution could be more easily implemented in case of lesser number of countries.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Do you really need to prompt for the currency? Can't you remove it as prompt and simply use it as condition in the analysis?

    A "dirty" workaround to cover this need would be to store in a variable (presentation variable) the value of the country, and then to match it by counting the number of commas "," you have inside the variable. If you have 1 or more (assuming your countries are ISO codes so don't have any) it means the user selected more than 1 country and you can set currency to USD. You can also match the "all values" in the same logic and fix the currency to USD.

    Think in that direction, it can probably give you a possible workaround for this as, out of the box, you don't have the functionality you ask for in OBIEE (not in a simple GUI thing at least).

  • user652652
    user652652 Rank 4 - Community Specialist

    Thanks All,

    Is there any  possible way? Or can we count number of selected values in prompt/presentation variable?

  • user652652
    user652652 Rank 4 - Community Specialist

    Thanks,

    How we can count number of ',' in presentation variable?

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    Try something like below

    case when POSITION(',' IN '@{yourVariable}')>0 then 'USD' else yourcurrencycolumn end

  • Gianni Ceresa
    Gianni Ceresa Mod

    @asim cholas gave you the formula ...

  • Syedsalmancs110
    Syedsalmancs110 Rank 6 - Analytics Lead

    Did you get the output expected, following the mentioned step?

    If yes then close the thread marking all the Helpful and Correct answers.

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    He will be busy in fixing his issue. Hopefully he will come back and close the thread once he is done with his task ...