Reporting on General Ledger Balances Cubes Reports with Account Hierarchies

General Ledger balances cubes are differentiated by unique combinations of a chart of accounts and an accounting calendar.

Balances cubes maintain summarized account balances for all trees and tree versions, and for all chart of account dimensions that are published to those balances cubes. The detail account balances for those published trees and tree versions are summarized according to defined account hierarchy rollups.

Trees and tree versions are defined in the application to represent date effective versions of account hierarchies. They’re organized for different purposes, for example, for statutory versus management reporting, with each tree version being assigned an effective date range.

However, this date effectivity isn’t an actual attribute in balances cubes. Instead, it’s an implied characteristic. Users need to know the significance of each tree and tree version that they select and which effective date of the organization’s rollup those tree versions represent. This helps facilitate performing year-over-year comparisons of financials results.

For example, this user-determined control allows organizations to report on fiscal year 23 versus fiscal year 22 financial results using the same tree version rollup for parent account values on both sets of numbers so that there's a consistent comparison between them.

A user can have a choice of reporting on both years' results using last year's tree version rollup for parent account values, or both using the current year's tree version rollup. They can even report on fiscal year 22 results using the fiscal year 22 rollup with the fiscal year 23 results using the fiscal year 23 rollup, if that's what’s required.

Segment Value Security Rules Based on Account Hierarchies

You can refer to account hierarchies in segment value security by business function policies as an efficient way of granting access to secured account values.

Use the hierarchical operators Is descendant of and Is last descendant of, along with a specified tree, to allow references to parent account values in a security rule condition. This means that a grant provides access to that parent value and all its descendants (with the Is Descendant of operator) or to that parent and its’ detail values descendants (with the Is last descendant of operator), according to the account hierarchy that’s defined for the applicable tree code and tree version.

Note: The trees that you specify in a rule must be flattened. This contrasts with trees that are published to balances cubes, which don’t need to be flattened. If your security rules refer to trees that are published to the balances cubes, then the trees must be flattened. Otherwise, security enforcement won’t work in balances cube-based reports and queries.

While a distinct tree code is associated with each segment in a chart of accounts, as specified through the Default Hierarchy value in the chart of accounts structure setup, a security rule can refer to any tree (hierarchy) that’s defined for the secured value set.

The tree version that you specify in a rule is just for the purposes of determining which parent values you can select from to then attach to the security rule condition. At runtime, the application applies date effectivity to identify the version of the tree referenced in the rule whose effective dates intersect with the current system date. Based on this tree version, the application derives the list of accessible descendant account values for the referenced parent value of that tree that are granted by that hierarchical security rule assignment.

For inquiring and reporting in balances cubes, these security grants apply across all versions of the specified hierarchy, as well as all hierarchies associated with the same value set of the specified hierarchy that are published to the GL balances cube. Even though a security rule has a specific tree and tree version for a parent value, the derived set of account values granted by that rule will equally apply to all other published tree and tree versions for where that same parent value is cited and as indicated in GL balances cube-based inquiries and reports.

This example highlights that the grant for segment value security rules based on hierarchies will apply the current effective tree version when deriving the list of accessible values. This contrasts with references to tree and tree versions in balances cube inquiries and reports, where a user controls exactly which tree and tree version they want to use for a rollup.

This table shows the hierarchies, hierarchy versions, parent value, and parent value descendants for this example.

Hierarchy	Hierarchy Version	Parent Value	Parents and Detail Value Descendants
Management	2023	100	Detail 101 Parent 200, with detail values 201, 202, 203 Parent 300, with detail values 301, 302
Management	2024	100	Parent 200, with detail values 101, 201, 202 Parent 300, with detail values 301, 302
Geographical	2023	100	Parent 200, with detail value 201 Parent 300, with detail values 301, 302 Parent 400, with detail values 401, 402

Let’s say a rule is defined with these attributes.

Operator: Is descendant of
From Value: 100
Tree Code: Management
Tree Version: 2023

Here’s how the application determines the secured values granted.

The application derives the hierarchy (tree) version that’s in effect based on the current system date, which is January 1, 2024. In this example, that’s version 2024.
The application then applies the rule condition to get the list of secured values. In this example, the secured values granted are 100, 200, 101, 201, 202, 300, 301, and 302 (parent value 100 and all its descendants in tree version 2024.)
This list of values will be accessible across all versions of the Management hierarchy and across all hierarchies associated with the value set for the segment. This means the list of values will be accessible if choosing to report against any version of the Management or Geographical hierarchies.
Note: Value 203 in Management hierarchy version 2023 and values 400, 401, and 402 in Geographical hierarchy version 2023 won't be accessible.

For the purpose of data entry, this date-effective list of accessible descendant segment values is available to the user for that given point of time.

For the purpose of inquiry and reporting with the balances cube, the same list of accessible descendant segment values is available to the user for all published trees and tree versions for the secured value set of the chart of accounts segment or dimension. Based on the tree and tree version the user selects, the balances data for the accessible descendant segment values of the referenced parent account will be displayed accordingly. A user can also be granted access to a specific parent value. In that case, the summarized balance shown for that parent value will be consistent with the rollup for the selected tree and tree version for reporting purposes, regardless of whether the user has been granted access to all the descendants involved with that particular rollup.

Note: No chart of accounts security is applied during the selection process of account values for the inquiry or report. The user can freely select any dimension member or account values from a secured value set. Security is applied only when retrieving the results for that query or report, and only the balances data of the dimension member or accounts to which the user has access will be displayed.

The advantage of the application dynamically applying date effectivity to security rules is that the rule would be self-maintaining. This methodology works well when it comes to creating transactions or financial data, which in most cases is a real-time exercise.

For comparative reporting, such as with year-over-year financial reporting, previous tree versions might be used as the basis of the rollup to compute summarized balances. This might result in some discrepancy in the access to the descendant accounts for that parent based on the previous tree version’s rollup as referenced in the inquiry or report. A potential solution would be to use a different hierarchy in the security rule that encompasses all the account values the user will need to inquire or report on, across the different trees and tree versions.

Considerations When Using Parent Account References in Hierarchical Rules for Balances Cubes

Ensure users get the appropriate secured values grants to view the financial data that they need.

A good practice is to define hierarchical rules that sync to parent values according to the tree as referenced in balances cube inquiries and reports such as Account Monitor, Smart View, and Financial Reporting.

Access to the descendant accounts of a specific parent value should be in sync with the descendant accounts that roll up to the summary account balance of that parent value based on another tree. Otherwise, the parent value will show the appropriate summary balance for the other tree, but the detail breakdown shown on the report of its descendants’ balances might not sum up consistently because of lack of access to those descendant values.

This concern applies even when the tree referenced in inquiries and report definitions is the very same one used in the security rule with parent value references. This is because there can be a difference in the dynamic date-effective version rollup that's applied when determining the grants for the rule assignment. Moreover, it's even possible to use a different tree in inquiries and report definitions than the one used in the rule, which can result in even more pronounced disparities.