Sorting & Limit Value by Dashboard Prompts — Oracle Analytics

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

Sorting & Limit Value by Dashboard Prompts

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

Hi All,

I have a dashboard prompt whose selection is limited to its previous prompt and it should also sort in descending order.

Prompt1:  Region Name (Can, Ind, UK,..)

Prompt2: Can 2003

                Ind 2017

                UK 2012

                Can 2007  

Prompt 2 should sort by Year in descending order. However when I try using SQL Results I don't get Limit Value by option.

Can any one please help on how to achieve Sort as well as Limit value by previous prompt ?

Thanks,

LonaD       

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    You would have to set a presentation variable with the first prompt and right logic in the section to use it to filter the results, this could be fiddly if your first prompt allows multiple selections and it is a text type.

    Not clear on your sort requirement, doesn't sort by 1 desc on your second prompt achieve it?

  • 986359
    986359 Rank 4 - Community Specialist

    I did try using presentation variable and its not working as expected, In here the 2nd prompt have to be mandatory field if i use presentation variable.

    My requirement is

    Prompt1   Prompt2

    Can          Can 2003

    IND          Can 2018

    UK           Can 2001

                      IND 2000

                      IND 2012

    So Once a region Can is selected in Prompt1, Prompt values should be related to Can followed by Year and the year should be in decreasing order , the expectation is like below:

    Promp1   Prompt2

    Can         Can 2018

                    Can 2003

                    Can 2001

    By default it gets sorted by ASC, i want it to be DESC for the year to show latest on top and the prompt2 selection is limited by Prompt1.

    Thanks,

    LonaD

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Hi,

    you can do {PresentationVariable}{DefaultValue} and use the default value in a case statement to do the equivalent of value = value when the default value is the result.

    On sort descending, you did try order by 1 desc  on the end of your sql statement?

  • 986359
    986359 Rank 4 - Community Specialist

    Thanks Robert,

    I tried something like below using presentation variable set to Prompt1 and using it in Prompt2 it works, But in this case when a value is selected in Prompt1 it will not reflect in Prompt2 unless clicked on Apply button and the report starts loading data before Prompt2 gets applied.

    SELECT distinct "Calendar"."Month Num"  FROM "Test" where  "Calendar"."Month " = '@{m1}'

  • ArijitC
    ArijitC Rank 6 - Analytics Lead 
    986359 wrote: "But in this case when a value is selected in Prompt1 it will not reflect in Prompt2 unless clicked on Apply button "

    You can avoid by removing apply and remove button.

    pastedImage_2.png

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Beat me to it!! - there was another very like this on the boards very recently...

  • ArijitC
    ArijitC Rank 6 - Analytics Lead 
    986359 wrote:SELECT distinct "Calendar"."Month Num" FROM "Test" where "Calendar"."Month " = '@{m1}'

    Try this for desc order...

    SELECT distinct "Calendar"."Month Num" saw_0 FROM "Test" where "Calendar"."Month " = '@{m1}' ORDER BY saw_0 DESC