1. Working with Oracle Smart View for Office
  2. Smart View and EPM Cloud
  3. Accessing Ancestor Members in Ad Hoc Grids

Accessing Ancestor Members in Ad Hoc Grids

In ad hoc grids and Member Selection, Smart View users can access ancestor members, including shared members, to which they otherwise would not have access, when the Service Administrator configures the application setting, Default Ancestor Access.

Data source types: Enterprise Profitability and Cost Management, Financial Consolidation and Close, FreeForm, Planning, Planning Modules, Tax Reporting

Starting in 24.03, Service Administrators use the Default Ancestor Access option to automate access assignment. Depending on the configuration, EPM Cloud users on the web and in Oracle Smart View for Office can zoom out to ancestors and see the hierarchy in a tree view, whereas previously, they wouldn’t have had access to the members at the parent level.

Additionally, enabling Default Ancestor Access facilitates the sharing of Excel workbooks between users having different levels of access to members. When access is configured, and users click Refresh in Smart View, cells to which they have access will come back with actual data values (or #NoAccess) and the hierarchy structures are maintained.

The available Default Ancestor Access options are:

The results of setting Default Ancestor Access to the above options are described in more detail in the sections that follow. In the ad hoc grid examples are from ad hoc on the web, but the principles demonstrated also apply to Smart View

Service Administrators: See Setting the Default Ancestor Access Option in the Application.

None

This is the default. Users see all their shared members as siblings in, for example, ad hoc grids or the Member Selector, rather than in hierarchies, when they do not have read or write access to those ancestors. There is no change from the previous behavior (24.02 and earlier).

Consider the following example, where we have States in the Entity dimension using mostly shared members, and a user with security defined.

Figure 22-9 Web ad hoc grid showing a hierarchy structure that includes shared members


Web ad hoc grid showing a hierarchy structure that includes shared members

The user has access to New Jersey, but does not have access to the parent of New Jersey and Florida so those members are listed at the top of the tree instead under their parent member. You can see the same situation for Arizona, California, and Nevada throughout. Access to parent members is preventing users from viewing the entire tree structure and can make data entry difficult.

In the Member Selection dialog in Smart View, you see the same structure:

Figure 22-10 Member Selector dialog in Smart View, showing hierarchy structure that includes shared members


Member Selector dialog in Smart View, showing hierarchy structure that includes shared members

Read

By setting the Default Ancestor Access option to Read, users can view the hierarchies to which they have access, including members and shared members, and view data on the grid, even in the cells of ancestors to which they would not otherwise have access. That is, if the user has access to a member, then all ancestors to which they do not have access will have Read access instead. The user is able to view data for cells to which they do not have access, but cannot enter data in those cells.

In the following example, with access set to Read, users can now see the entire tree and all the hierarchies: Entity, Alt Entity, and Alt Entity2, and so on. Before, with access set to None, only Alt Entity2 was viewable.

Figure 22-11 Web ad hoc grid showing the full hierarchy structure to which the user has access


Web ad hoc grid showing the full hierarchy structure to which the user has access

The Member Selection dialog in Smart View also reflects the change. For example:

Figure 22-12 Member Selector in Smart View showing the full hierarchy structure to which the user has Read access


Member Selector in Smart View showing the full hierarchy structure to which the user has Read access

Write

With the Default Ancestor Access option set to Write, users can view the hierarchies to which they have access, including members, shared members, and data. Additionally, they can save data to the cells of ancestors to which they have access. That is, if the user has access to a member, then all ancestors to which they do not have access will have Write access instead.

When granted Write access, users would see the same hierarchy structures in both the web and Smart View, and in the Member Selection dialog, as shown in Figure 22-11 and Figure 22-12. Additionally, they can save data in cells to which they normally do not have access.

Display

When Display is selected in Default Ancestor Access, users can view the hierarchies to which they have access, including members and shared members. They can view data in cells to which they have access. In ancestor cells to which they do not have access, then #NoAccess is displayed in the cell, but the ancestor member name is still displayed (for example, it is not filtered off the grid as it would be if the user had None access).

With Display access, users are still able to see the hierarchy tree structure, but would see #NoAccess in cells to which they do not have access. For example:

Figure 22-13 Ad hoc grid with Define Ancestor Access set to Display, showing full hierarchy structure and #NoAccess cells


Ad hoc grid with the Define Ancestor Access set to Display, showing full hierarchy structure and #NoAccess cells

Notice the same hierarchies are still present (just as when Read or Write was selected). The hierarchy according to the user's access is presented: East, West, USA, Total Entity, Alt Entity, Alt Entity 2, Post Load, and Entity. Note this is not the entire hierarchy, but all the ancestors in which the user has access to at least one member. The parent members, such as East, were brought in as #NoAccess; whereas West, to which the user did have access, remains readable. The same applies to Alt Entity2, which was present before (when set Default Ancestor Access was set to None) and is present now with a value of 300. Alt Entity1, which the user did not have access to before, is present so the hierarchy is formed, but the user cannot see the data (the cell contains #NoAccess).

And the same display applies in Smart View. Starting with the initial ad hoc grid:

Figure 22-14 Initial Smart View ad hoc grid with California as the only row member


Initial Smart View ad hoc grid with California as the only row member

In the Member Selection dialog, notice the full tree that the user has access to, which includes Total Entity, USA, West, East:

Figure 22-15 Member Selector in Smart View, showing the full hierarchy structure to which the user has access


Member Selector in Smart View, showing the full hierarchy structure to which the user has access

Consider that the user never had access to the USA member. There is now an easy-to-navigate tree view in the Member Selection dialog. If the user selects USA for their sheet, those cells will display as #NoAccess, instead of an error. For example:

Figure 22-16 Smart View ad hoc grid with the USA member showing #NoAccess cells


Smart View ad hoc grid with the USA member showing #NoAccess cells

Setting the Default Ancestor Access Option in the Application

Service administrators: Use the Default Ancestor Access option to specify default user access to the ancestor members of members with access other than None, including shared members. The Default Ancestor Access is listed under System Settings in Application Settings for supported business processes.

The access option specified in the Default Ancestor Access setting enhances existing member security to provide a choice of default access to ancestor members.

This enables users to view the members that they have access to in context of the subhierarchies that they are part of in, for example, ad hoc grids or the Member Selector.

Note:

Users will not see subhierarchies unless they have access to at least one descendant member.

The available Default Ancestor Access options are:

  • None—By default, users are not able to see the ancestor members of members that they have access to unless they are explicitly granted access to them. When this option is selected, the system works the same way it did prior to the 24.03 update.
  • Read—By default, users are granted Read Only access to the ancestor members of members that they have access to unless they are explicitly granted Write or Display access, which would override this default.
  • Write—By default, users are granted Write access to the ancestor members of members that they have access to unless they are explicitly granted Read or Display access, which would override this default.
  • Display—By default, users are granted Display Only access to the ancestor members of members that they have access to unless they are explicitly granted Read or Write access, which would override this default.

    Note that members with Display Only access will be displayed, but instead of data values in the cells associated with those members, users will see #NoAccess.

Note:

  • Default Ancestor Access applies to ancestors after shared member security is evaluated. This allows it to work correctly both whether the Include Shared Members in Cube Refresh option is enabled or disabled.

  • Service administrators can set the default access for ancestors to None, Read, Write, or Display access in the application. They can also grant Read, Write, or Display access to one or more ancestors in, for example, the Dimension Editor. In these cases, the explicit security assignment made in the Dimension Editor takes precedence, unless access in the Dimension Editor is set to None, in which case the Default Ancestor Access setting takes precedence.

See the following topics for information on setting the Default Ancestor Access option in your business process: