Odd behavior on OBIEE subtotals

Question

I have this column on an analysis.  The underlying formula being used is

CASE  WHEN ."Value1" >0

THEN "Value1"

ELSE "Value2"

END

Basically I have two numeric columns for each record.  I want to report whichever one is greater than 0.

The detail of the report is correct.  I have three lines with these values; 2, 5, 2.  The first "2" is from Value1; the "5" and other "2" are from Value2.

For this value column, I selected this aggregation rule: Default and Report-Based Total (when applicable).

The problem comes on the subtotals.  On the results tab, I selected to add Totals after each change in a date field.  When it prints the total for this date, It shows "7" (It should be 9: 2+5+2).

Any ideas why it's displaying the total incorrectly?

Thanks!

Dennis

[Deleted User] · Answer

Late to the party but as Robert said: this is something better left to the BI Server and not the analysis itself.

Push back to RPD for proper control of what happens when.

Keep in mind: pre-aggretation calculations will not yield the same results as post-aggregation calculations!

Robert Angel · Answer

Hi Dennis,

the problem comes in that OBIEE tries to aggregate on the highest level, and I have found that often with case statements this results in spurious results.

Two suggestions; -

Best - push your cash logic back into an rpd measure, make sure it is a physical calculation

Might work - use the server complex aggregate option - it sometimes 'does the trick', but cf above, that is a better way to go