Restricting the prompt value based on another prompt — Oracle Analytics

Oracle Analytics Cloud and Server

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

Restricting the prompt value based on another prompt

Received Response
333
Views
8
Comments
3018071
3018071 Rank 4 - Community Specialist

Hi,

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

For eg

YearMonth
2010jan
2010feb
2012

march

2012april

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.

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    3018071 Rank 4 - Community Specialist

    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
    Thomas Dodds Rank 8 - Analytics Strategist

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

  • 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,

    k11.jpg

    Create another column prompt variable for month:

    here select options.

    check the limit values by . YEAR..


    K33.jpg


    k22.jpg



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


    K44.jpg



    K55.jpg

    k66.jpg

    Pls try this, its working fine..

    Thanks & Regards,

    A.kavya.

  • 3018071
    3018071 Rank 4 - Community Specialist

    Thanks Kavya

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

  • 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,

    k77.jpg

    here 2012 does not have jan data

    k88.jpg

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

    can you send your prompts with screen shot

    Thanks & Regards,

    A.kavya

  • 3018071
    3018071 Rank 4 - Community Specialist

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

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