9 Replies Latest reply on Jul 21, 2016 7:44 PM by Christian Berg

    TopN based on results of another analysis - sub-analysis with filters based on prompts

    Robert Angel

      I have a business need to base a filter condition on another query, to give me the equivalent of the following piece of pseudo SQL; -

       

       

      select various_dimension_fields, measure_value

      from some_tables outa

      where inna.code in &Param1

      and inna.year in &Param2

      and inna.period in &Param3

      and inna.etc = &Param4

      and    exists

      (select topN(sum(amount))

      ,         inna.some_field

      from    item_consumption inna

      where  inna.some_field = outa.some_field

      and  inna.code in &Param1

      and inna.year in &Param2

      and inna.period in &Param3

      and inna.etc = &Param4

      group by

                     inna.some_field)

       

       

      The problem I have is that the sub-query does not seem to catch the parameters from the dashboard that the outer query is in, is there anyway to do this with wholly dynamic prompts driving the inner and outer analysis results and coordinating the two?

       

       

      n.b. I need to apply the sub-query to various outer analyses, and it needs to be truly dynamic.

       

       

      thanks for your input,

       

      Robert.