Oracle Fusion AI Data Platform Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Fusion AI Data Platform Forum

Categories

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

Reporting Either Project Org Hierarchy or Task Org Hierarchy Within One Set of Dashboard Filters

Received Response
14
Views
3
Comments
User_WJ43I
User_WJ43I Rank 3 - Community Apprentice
in Oracle Fusion AI Data Platform Forum

I'm apologizing ahead of time for the length of this post. I'm sorry.

I am currently working on a Fusion ERP/HCM/FDI/OAC implementation project. We are not live yet but will be live soon. I am trying to build an OAC workbook visualization that shows Backlog Amount (Project Funding - Project Revenue). The issue I am having is that when we converted funding and revenue amounts for active projects from our legacy system, we loaded both amounts at the Project level. However, moving forward, post go-live, all new projects will be required to load both funding and revenue at the task level.

For this dashboard, the business wants to see backlog by Task Org Hierarchy levels 1-4. The problem is that for our converted active projects, there is no task on the funding or revenue lines due to the funding and revenue being loaded at the project level only. To solve for this problem, I wrote a view that calculates the org hierarchy values using a case statement that checks to see if the task on the funding row is -99999. If it is -99999 then it uses the Project Org hierarchy, if the task is not -99999 then it uses the Task Org Hierarchy. The issue is that when I join this view to the Funding fact, I have to join it by both the task ID and the project ID. When I do this, in my OAC workbook, the revenue fact is no longer outer joined to the Funding fact. So my funding amounts are correct, but the full revenue amount shows against each hierarchy level. I even wrote the same view for the revenue fact and joined it as well. It still will not outer join.

Has anyone else come across this requirement before? If so, how did you solve for it? Fusion allows you to load funding/revenue at either the task or project level, so I'm hoping our scenario/ask is not unique.

Any advice or help would be greatly appreciated.

Tagged:

Answers

  • Daniel Ryan
    Daniel Ryan Rank 3 - Community Apprentice

    Have you considered creating a new hierarchy that concatenates the child org and task at the detail level? There is a blog from Rohan Lobo that shows how to do this for cost centres. Maybe something similar could work in this area?

    Create a Custom Alternate Presentation Hierarchy in Oracle Fusion Data Intelligence | analytics

  • User_WJ43I
    User_WJ43I Rank 3 - Community Apprentice

    The issue isn't with creating the custom hierarchy. I can do that. The issue is that for the converted projects, their funding/revenue rows have task -99999, so in order for me to join the custom hierarchy to the funding/revenue rows, I have to join but both the task ID and the project ID. When I do that, OAC no longer allows to outer join funding and revenue. I cannot just join my hierarchy to task or only to project. I have to join it to both in order for the results to be accurate.

  • RVohra
    RVohra Mod

    @User_WJ43I , If I understood the question, the underlying issue is that your revenue fact at project level is being joined to task-level hierarchies, and when you join both facts using project_id+task_id, project-level revenue rows don’t have tasks to join, so the outer join fails.

    For this implementation you can try following and see if it works

    1. Create synthetic task IDs for project-level data (-99999 → project_id * 1000).
    2. Adjust your hierarchy view to map this synthetic task to project-level org hierarchy.
    3. Join funding & revenue facts by project + task surrogate.
    4. This ensures:
      • Outer joins work
      • Backlog is correct
      • Reporting logic stays consistent for future task-level projects