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

Restricting the prompt value based on another prompt

3018071

Hi,

I am trying to restrict a dashboard prompt based on the values selected in another dashboard prompt.

For eg

Year	Month
2010	jan
2010	feb
2012	march
2012	april

So let year and month both are different prompts.And if i select 2010, than in the month prompt should only show me

jan and feb.

I have tried 'limit values by' option but it didnot work out.

Any help will be appreciated

Thanks.

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

Where do you want to store and administer the rules of which months get shown for which years ... this doesn't seem like the appropriate place (in a prompt) to be driving that. You need a separate logical column in which your CASE is run - use that column in conjunction with the year to get the results you want.

3018071

Regards Thomas

As both the columns are directly picked from the source table,so no need to apply any rule i guess.|
If u can elaborate on your reply,It can be better understood.

I am again explaining my query in a better manner.The customer requirement is such that If i click 2010 from the year prompt,than i should see jan and feb values in the month drop down and also i can select my desired moth from the list .to display the particular data.

For example

Let I select 2010 in the year drop down so i should see jan and feb in moth drop down.

And if i click jan,than i must see the data for 2010-jan only.
If u you help me out on this .....

Thomas Dodds

I get the requirement ... I'm asking where is the relationship between the YEAR and the MONTHS to be displayed? That relationship is not in the time dimension, as all years have 12 months. So ... is the relationship arbitrary or is it from your knowledge of what is in the fact table (you know there is only data for January and February in 2010). If the latter is the case, then you can use a logical query in the prompt for Months to filter based on fact_amount not equal to zero and not null (or something similar). If the relationship is not in the data, then you in effect must 'create' that data and relationship. You can do it the following ways:

1) create a table to hold the special intersection of years and months (will perform the best)

2) create a logical column that has a big case statement that builds the relationship (might not perform too well; any relationship changes require RPD mods)

3) create a logical sql statement for use in the prompt that has the big case statement (will not perform well; any relationship changes require prompt mods (ALL prompts in which it is implemented)

1 is better than 2, 2 is better than 3, 3 is not the way to do it ever ...

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

I have 2012,2013, 2014, 2015 year data..

2012 year did nt have jan, dec month data..

If i select 2012, i am getting feb to novem data only. it doest show jan , dec month.. bcoz there no data for 2012 jan, 2012 dec data.

i have done like this

create column prompt variable for year:

Lable :YEAR,

Create another column prompt variable for month:

here select options.

check the limit values by . YEAR..

Its working fine.i am getting.. see the below screen shot..

Pls try this, its working fine..

Thanks & Regards,

A.kavya.

3018071

Thanks Kavya

But if we do like this
then only after clicking apply button it will show the month values for the particular year.

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

Year, month are different prompts..

If i select year from prompt, months values are showing for that perticular year in month prompt. finally we click apply button. we will get data based on year , month.

for example like this,

here 2012 does not have jan data

If this is not your requirement, can you explain ryour requirement ..

can you send your prompts with screen shot

Thanks & Regards,

A.kavya

3018071

This is exactly my requirement..
for this did u create a hierarchy for time dimension in the rpd???

ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

Hi,

i did nt create hierarchy for time dimention...

i have taken only business date. i have calculated year, month using business date....

Thanks & Regards,

A.kavya.