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

Adding sub-totals by hierachy levels in a pivot table with Oracle Analytics Cloud

Accepted answer
287
Views
13
Comments
2»

Answers

  • Junaid Zahid
    Junaid Zahid Rank 2 - Community Beginner

    I wanted to share a suggestion and request for your kind help regarding Hierarchy building and Session Variable support directly in Oracle Data Visualization (DV) on OAS 7.0.

    In our environment, we are trying to move towards more self-service dashboards using DV, especially for large Oracle ERP datasets. However, we’re facing challenges because our hierarchies and user-level security are currently defined in the RPD (Admin Tool) using Initialization Blocks and Session Variables.

    For example, we have variables like :USER, :GROUP, :REGION_ID, and :ORG_LEVEL defined in the RPD through init blocks. These control what data a user can see when they log in. So, when User A logs in, the hierarchy and level automatically change based on A’s access, and similarly for User B.

    The issue is that when we build dashboards in DV using PL/SQL query-based datasets, DV does not recognize these RPD session variables. That means we lose dynamic hierarchy and user-based filtering unless we go back to modify the RPD — which defeats the purpose of agile, self-service reporting.

    It would be a huge improvement if DV could:

    • Allow hierarchy building (Parent → Child → Level) directly within DV datasets.
    • Support RPD session variables or new DV-level session variables that respond to the user login context.
    • Optionally reuse RPD Initialization Block logic in DV so security and hierarchy logic stay consistent.

    Our goal is to create dashboards that change dynamically based on who logs in — without needing RPD edits every time.

    I’d really appreciate your guidance or any upcoming plans in this area.

  • Junaid Zahid
    Junaid Zahid Rank 2 - Community Beginner

    I really think Oracle Analytics Server (OAS) should give us full Hierarchy building and Session Variable capability directly inside Data Visualization (DV) — without depending on the RPD (Admin Tool).

    In big enterprise setups (like Oracle ERP integrations or large data warehouse projects), we usually have complex hierarchies and user-level security defined in the RPD using Initialization Blocks and Session Variables. For example, in the Admin Tool we define variables like :USER, :GROUP, :REGION_ID, or custom ones like :ORG_LEVEL or :MANAGER_ID through init blocks connected to database tables or LDAP. These session variables decide what data a user can see when they log in — the hierarchy and levels change automatically based on the logged-in user.

    But when we try to build dashboards directly in Data Visualization (DV) — especially using PL/SQL or SQL queries as data sources — DV doesn’t recognize or use those RPD session variables. It means user-level filtering and dynamic hierarchy control are lost unless we go back and modify the RPD model.

    This becomes a problem because we want to avoid RPD dependency for every new report or dashboard. Our users now demand self-service dashboards through DV, and maintaining hierarchies only in the RPD slows us down.

    What we need is:

    • The ability to define hierarchies (Parent → Child → Level) directly in DV datasets.
    • The ability to use RPD session variables or create new DV-level session variables that change dynamically on user login.
    • Optionally, a way to reuse RPD initialization block logic inside DV so that user-based filters and hierarchy rules stay consistent.

    In my case, I have OAS 7.0 with SSO and WebLogic groups. The users and groups are mapped correctly, but since the hierarchy and session variables are defined in RPD, DV can’t detect them. I want the DV dashboard to automatically adjust according to the user login (for example, when user A logs in, only A’s hierarchy level data should load; when user B logs in, the dashboard should refresh based on B’s hierarchy).

    It would be really powerful if Oracle adds native support for session variables and hierarchies in DV. This would remove the need to modify RPD for every new self-service dashboard and make OAS much more flexible for modern analytics teams.

    Has anyone found a practical workaround for using session variables or hierarchy-based filtering directly inside DV without touching RPD?

  • Gianni Ceresa
    Gianni Ceresa Mod

    @Junaid Zahid ,

    Did you consider posting an idea? https://community.oracle.com/products/oracleanalytics/categories/idealab-oracle-analytics-cloud-server

    That's how you could request a new feature to be considered for the product. Other users can then up-vote it and product management will be able to see if it is a highly requested feature and evaluate if it can be implemented.

    Or also, before to create an idea, look if there isn't already one asking the same, and if found, up-vote it.