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.

Robert Angel

Ok, I found a fix to this.

1. Ignore the analysis idea it is limited

2, Write the equivalent of the nested analysis, but solely to retrieve its SQL from the advanced tab

3. Turn the filter in your outer query into sql and then paste the inner sql to be the basis of your IN condition

4, You will need to put your parameters back in, and you will have to use @{MyParameter} type syntax for it  - unless anyone can tell me how you can create an IS Prompted filter in SQL??

Hope this helps someone else.

[Deleted User]

Presentation variables are the way to go for that.

Robert Angel

Hi Christian,

yes - I am using presentation variables but what I am struggling with is trying to get the presentation variable to default ALL values when nothing is selected, the same way that Is Prompted would, is there any workaround for this - as with that final piece of the puzzle I would be home and dry....

thanks for your input,

Robert.

Gianni Ceresa

Hi Robert,

You can manage the case as the presentation variable has a value if "all records" is selected (can't remember exactly if it's "all values" or something else, just display its value and you get it).

So by adding a piece of logic you can end up with something like : 1 = 1 (if all values selected) or your condition (if the variable has a real single value)

[Deleted User]

Gianni beat me to it and I haven't gotten a running sys right now but in terms of XML what's stored for "All values" in customizations et al is *)nqgtac(*

Gianni Ceresa

Too slow Christian

Still discovering places for dinner?

[Deleted User]

Naahh hotel room and Sprüngli, that's it.

Robert Angel

Many thanks to all.

I thought Sprüngli was something you got at a certain nordic flat pack furniture outlet?!

[Deleted User]

HERESY O_O

(No, it's actually chocolate;))