In OBIEE, is it possible to maintain Denominator regardless of the Filters applied?

Question

Hi All,

We have a requirement where Numerator of a Column has to be Filtered by the Prompt Values butnot the Denominator.

Whenever a Product is selected in the Prompt from the List of Products , then the Column Logic will be  ---> Selected Product /Sum(All the Products)

As of now, whenever i select a Product from the Prompt, Column Logic is changed to Selected Product/Selected Product but i dont want the Filter to be passed to the Denominator.

I have applied the above column logic in a Union Report Result Column, so there are multiple limitations for me to restrict the Filter values passed to the Denominator.

Please help me in resolving the above issue.

Thanks

Suman

Thomas Dodds · Answer

What about a level based measure pinned at Product total content level?

Robert Angel · Answer

What I am suggesting is as per; -

TW OBIEE: Using a FILTER Function Instead of CASE Statements

So instead of applying your filter (as in where condition) in the normal way you use it in the USING .... clause, with your Denominator is either just the base measure or a static filter, depending on the complexity of whatever else is in your analysis.

As the Blog talks about 'Instead of CASE Statements' - sum(CASE when ... whatever then Measure else 0 end) / sum (Measure) is also an option, but I rarely get sound results with CASE in an analysis, due to the way OBIEE tries to aggregate as early as possible you often find CASE in the analysis gives spurious results.

You might also want to have a look at selection steps which can be useful when you want a % of a total population - https://www.youtube.com/watch?v=-pYGHevIqZA

In short there are a number of options here.

Robert Angel · Answer

Could you use nested Filter - the Filter created via formulas with the dyntax accesed from the function syntax palette to make one part static, the other half dynamically filtered?