SUM BY Logic in OBIEE RPD

Question

Hi All,

Oracle Business Intelligence 12.2.1.1.0

I am trying to implement a sum by using a level based hierarchy. I will try to explain the scenario in the best way I can.

I need to group the Amount(Column 3) by Ledger Type(Column 1). I used SUM(Amount by Ledger Type) in analysis and it gives the correct result. (Column 5). I need to push the same logic to the RPD. Also the SUM BY Amount should change with filters applied on the answers.

Fact Table LTS Content Tab

Tried setting the level of Ledger Type to detail and all other dimension to Total level at the column level. Then in analysis, numbers are getting group by Ledger Type only. But the issue is if I apply global filter on any other columns in report, the numbers are not changing.

Please let me know how can I implement the SUM BY logic in OBIEE RPD.

Facing the same issue as mentioned in this blog post.

https://www.rittmanmead.com/blog/2009/12/oracle-bi-ee-10-1-3-4-1-level-based-measures-grand-totals/

Thanks !

Ebin Cherian · Answer

I was able to achieve the required output using EVALUATE_ANALYTIC. EVALUATE gives "window function not allowed here" error if we have total enabled on the report. With EVALUATE_ANALYTIC, the report works fine.

EVALUATE_ANALYTIC('SUM(%1) OVER (PARTITION BY %2)', "Accounts Payable"."AP Metrics"."Amount" , "Accounts Payable"."Ledger Type"."Scenario" )

Credit to @Sherry George for this idea.