7 Replies Latest reply on Jan 30, 2018 5:26 PM by ArijitC

    Sorting & Limit Value by Dashboard Prompts

    986359

      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       

        • 1. Re: Sorting & Limit Value by Dashboard Prompts
          Robert Angel

          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?

          1 person found this helpful
          • 2. Re: Sorting & Limit Value by Dashboard Prompts
            986359

            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

            • 3. Re: Sorting & Limit Value by Dashboard Prompts
              Robert Angel

              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?

              • 4. Re: Sorting & Limit Value by Dashboard Prompts
                986359

                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}'

                • 5. Re: Sorting & Limit Value by Dashboard Prompts
                  ArijitC

                  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.

                  • 6. Re: Sorting & Limit Value by Dashboard Prompts
                    Robert Angel

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

                    • 7. Re: Sorting & Limit Value by Dashboard Prompts
                      ArijitC

                      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