nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

user652652

Hi Experts,

I have data like this

Country	Currnecy
INDIA	INR
USA	USD
UAE	DHM
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,

Find more posts tagged with

Accepted answers

All comments

Syedsalmancs110

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.

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

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

STEP 4: Save and Test the Prompts.

a) With no country value selected

b) With one country value selected

c) With more than one Country value selected

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

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

Thanks All,

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

user652652

Thanks,

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

asim cholas

Try something like below

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

Gianni Ceresa

@asim cholas gave you the formula ...

Syedsalmancs110

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

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