Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

SUM BY Logic in OBIEE RPD

Received Response
420
Views
1
Comments
Ebin Cherian
Ebin Cherian Rank 5 - Community Champion
edited Aug 13, 2024 5:58PM in Oracle Analytics Cloud and Server

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.

pastedImage_0.png

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

pastedImage_1.png

pastedImage_5.png

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 !

Answers

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    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.