Ledger Cell Value Entry and Aggregation

Designer Administrator
This manual is in pilot operation.

(The following explains the cell values of ledgers other than Note Ledgers. For cell values of Note Ledgers, please see Note Ledgers section).

Entering and Aggregating Numeric Data

If the data type of a cell is numeric (flow and balance values), data is entered into leaf cells (ledger cells specified by a combination of leaf members) and aggregated along the dimension member tree. Cells that are not leaf cells are aggregate cells. That is, an aggregate cell is a cell for which at least one of the members specifying that cell is an aggregate member. For aggregate cells, the values of the underlying leaf cells (cells specified by the combination of individual members under those aggregate cells) are aggregated, and only references are possible. However, there are the following exceptions:

(1) Exceptions for Relative Period Dimensions

In relative period dimensions, it is possible to enter data for higher periods. Data entered for a higher period is reflected in the data for the last leaf period within that period. For example, if you enter data for the first quarter (Q1), the data for March (M3) will be changed (if the last leaf period of the first quarter is March).

(2) Exceptions for View Dimensions

Although there are no parent-child relationships in views, entering data in the Assured View updates all other views in sync. No input is allowed in views other than the Assured View.

(3) Exceptions for Change Dimensions

In the Change Dimension, the possibility of input and calculation method is determined according to the characteristics of each change account. Please refer to "Input Allowance and Calculation Conditions for Changes by Data Type".

(4) Permitting Numeric Data Entry/Editing in Aggregate Cells

Turning the scenario attribute "Allows editing aggregated numeric values" ON allows data entry/editing in aggregate members within that scenario. For details, please see the following "Entering and Editing Numeric Data in Aggregate Cells".

Entering and Editing Numeric Data in Aggregate Cells

Turning the scenario attribute "Allows editing aggregated numeric values" ON allows data entry/editing in aggregate members within that scenario.

(1) Entering and Editing Numeric Data in Aggregate Cells

To enable data entry/editing in an aggregate cell, the following conditions must be met for each aggregate member composing the cell’s key:

  • The member type of the aggregate member is not "System Reserved",

  • The "Is Active" status of the aggregate member is "Used", and,

  • There is a corresponding "Adjustment Member" for the aggregate member.

Here, an "Adjustment Member" refers to the last descendant member of the aggregate member, fulfilling the requirement of being a leaf member. However, a single member cannot become an adjustment member for multiple aggregate members. If a member meets the above two conditions for multiple aggregate members, 1) if those aggregate members are in an ancestor-descendant relationship, the descendant member, 2) otherwise, the member appearing earlier in the member tree becomes the adjustment member.

For example, in the member tree diagram below, if "Total" and "Tokyo District" are active aggregate members, their respective adjustment members are "Total Adjustment" and "Tokyo District Adjustment". "Total Adjustment" and "Tokyo District Adjustment" must be leaf members. Otherwise, the third condition mentioned above is not met, making "Total" and "Tokyo District" non-enterable.

  Total
    +---- Tokyo District
          +---- Roppongi Store
          +---- Daikanyama Store
          +---- Tokyo District Adjustment
    +---- Chukyo District
          +---- ...
    +---- Total Adjustment

In the diagram above, the adjustment members are directly under their corresponding aggregate members, but this is not a requirement. For example, placing "Total Adjustment" under "Other Districts" still satisfies the third condition mentioned above, making "Total Adjustment" the adjustment member for "Total" (assuming "Other Districts" is set to "Not Used"; otherwise, "Total Adjustment" would be the adjustment member for "Other Districts").

  Total
    +---- Tokyo District
          +---- Roppongi Store
          +---- Daikanyama Store
          +---- Tokyo District Adjustment
    +---- Chukyo District
          +---- ...
    +---- Other District
          +---- Total Adjustment

For multiple dimensions specifying an aggregate cell (excluding relative periods), the above conditions must be met for each.

Please note the following points:

  • If the value of an aggregate cell is changed, the difference in change is allocated to the leaf cell (adjustment cell) related to the adjustment member. Even if the "Is Active" status of the adjustment member is "Not Used", if the usage status of the aggregate member is "Used", it is possible to change the value at the aggregate member level, potentially altering the value of the adjustment member.

  • The value entered in an aggregate cell is not stored in the database. Changing the data value of an aggregate cell automatically reflects the difference in the adjustment cell value. Therefore, changing the composition of descendant members of an aggregate member in the member tree will accordingly change the value of the aggregate cell.

  • If multiple dimensions used in a single ledger allow data entry at the aggregate level, the amount of data to be maintained by the ledger could significantly increase. Limit the data-entry capable aggregate members, etc., to control the increase in data volume.

(2) Behavior of Aggregate Cell Numbers When Entering and Editing Numbers in Cells Subordinate to an Input-Enabled Aggregate Cell

When entering and editing values in cells subordinate to an input-enabled aggregate cell, normally, the numerical value of that aggregate cell changes.

For example, consider a case where sales budgets for stores are aggregated by region using a three-level member tree of "Total - Region - Store" for the "Store" dimension. The member tree is as follows:

  Total
    +---- Tokyo District
            +---- Roppongi Store
            +---- Daikanyama Store
            +---- Tokyo District Adjustment
    +---- Chukyo District
            +---- ...
    +---- Total Adjustment

In this case, increasing the sales budget of the Roppongi store by 10 million yen also increases the sales figure of the Tokyo District (its aggregate value) by 10 million yen. This is normal aggregation processing based on the member tree. Even if the sales budget of the Tokyo District is editable, this behavior remains the same.

However, there may be cases where, despite making the Tokyo District’s sales budget editable, you wish to prevent adjustments to the Roppongi Store’s sales budget from affecting the overall sales budget of the Tokyo District. This might be the case when the regional sales budget amounts are already fixed, and adjustments to individual store sales budgets should not modify the regional sales budgets.

In such cases, turning the scenario attribute "Preserves aggregated values when descendant values change" ON ensures that adjusting the Roppongi Store’s sales budget does not affect the Tokyo District’s sales budget. The adjustment amount is absorbed by the "Adjustment Member". In the above case, increasing the Roppongi Store’s sales budget by 10 million yen would decrease the sales budget of "Tokyo District Adjustment", the adjustment member for Tokyo District, by 10 million yen, keeping the total sales budget for Tokyo District unchanged.

Please be aware that adjusting the value of an adjustment cell does not trigger the above process. For example, increasing the sales budget of "Tokyo District Adjustment" by 10 million yen, with no possibility of adjustment, would increase the sales budget of the Tokyo District by 10 million yen. If this is inconvenient, as mentioned in (1), setting the usage status of the adjustment member to "Not Used" is permissible to prohibit direct input to the adjustment cell. Note, as with (1), that even with the adjustment member set to "Not Used", the value may change as described above.

If multiple dimensions used in a single ledger allow data entry at the aggregate level, changing numbers at the dependent level while preserving aggregate level numbers will alter many adjustment cell values. This may affect processing time, so please be cautious.

Entering and Aggregating Non-Numeric Data

If the data type of a cell is "None", data entry is not allowed. For other non-numeric data types (textual values, boolean values, enumeration), data is not aggregated. Instead, data can be entered not only in leaf cells but also in aggregate cells.

However, as with regular ledgers, in relative period dimensions, the data value for an aggregate period is considered identical to the data value for the last leaf period of that aggregate period.

For example, in a standard definition of relative periods consisting of 12 months / 4 quarters, notes for the third quarter are considered notes for the last leaf period of that quarter, which is September.

Input Restrictions Due to Disabling Dimension Members

By setting the "Is Active" status of a dimension member’s property to "Not Used", data entry related to that member can be prohibited.

Input Restrictions via Ledger Access Control Function

Even cells that would normally be enterable following the above rules can be prohibited from entry through the settings of Ledger Access Control.