Robert, our new consolidation consultant, wants to:
- Understand the functionality of aggregation mode.
- Acquire knowledge about the different insertion modes, their purposes, and functionalities.
- Understand how to use automated conditional formatting features for changing the background and/or font color depending on cell values.
- Get into the creation of sum function formulas.
Aggregation Mode
For each dimension, you can itemize the values. You can also choose to display an additional total row or column either before or after the values, or just display the sum of the itemized members.
Insertion Mode
For each dimension with itemized values (with or without totals), you can choose one of the following insertion modes:
- Display Dimension Values with Amounts:
The system inserts and displays data stored in the Consolidation Journal Entries (ACDOCU) table (based on the filter mode). You cannot add dimension values.
- Display All Dimension Values:
The system inserts and displays dimension values based on the filter mode, and displays data stored in the Consolidation Journal Entries (ACDOCU) table.
- Display and Add Dimension Values:
The system inserts and displays data stored in the Consolidation Journal Entries (ACDOCU) table (based on the filter mode). You can add dimension values.
When the form is run using the Enter Package Data app, values are inserted according to the selected insertion mode.
Note
The system interprets 0 as if there is no amount entered. When the form is run using the Enter Package Data app, the 0 amount does not display when you choose Display Dimension Values with Amounts or Display and Add Dimension Values.
The figure, Aggregation Mode "Itemized with Total After", describes the selection of Aggregation Mode "Itemized with Total After".
If you select Itemized with Total After or Itemized with Total Before, total rows or columns add to display the total of the itemized rows or columns.
Total rows or columns are formatted in blue.
Note
When entering data in the Enter Package Data app, the total values only refresh when saving the transaction data to the ACDOCU table since the Totals are calculated by the backend.
If you choose Cell Details for a Total data cell, you will get the information "Read-Only Reason: Aggregated data".
The figure, Aggregation Mode "Sum", describes the selection of Aggregation Mode "Sum".
Aggregation mode "Itemized with Total After" and "Sum" have the following in common:
- The Totals and the Sum rows or columns are both formatted in blue.
- As described in the figure, Aggregation Mode "Sum", you can create a title for the Totals respectively the Sum. You do this in the Cell View tab.
- The calculations of Totals and Sum is not done until the data entered for the FS Item is saved to the database.
The only difference between "Itemized with Total After" and "Sum" is that "Sum" by itself only shows the sum of the related block FS Items while "Itemized with Total After" shows the related block FS Items and their sum.
Note
To get the same result with the Aggregation Mode "Sum" as the Aggregation Mode "Itemized with Total After", you must first create rows 4 and 7 with Aggregation Mode "Itemized" to list the relevant FS Items. Then, in rows 5 and 8, you can use the "Sum" feature to acquire the sum of items of rows 4 and 7. This is shown in the above example.
So, if you only want the sum of the selection but not the selection itself, use the Aggregation Mode "Sum".
The figure, The Sum Function Formula, describes the use of the sum function formula instead of the Aggregation Mode "Itemized with Total After" or "SUM". As an alternative to the Aggregation Mode, you can create a formula using the SUM function in the formula section of the Cell View. Then you can format the cell using the total style under Cell Formatting in the Cell View.
For example, if you choose Cell Details for cell C6, you see the cell formula the system applies according to the SUM function formula in cell C5.
The total style uses fewer resources than the Aggregation Mode features, reducing the amount of time it takes to run the form in the Enter Package Data app. The advantage of using a formula is that the formula is calculated at runtime, on the fly, before saving the data to the ACDOCU table - just by pressing Enter after data entry in the input-ready cells of the form.
You can use the automatic conditional formatting features to automatically change the background or font color, depending on the value in the cell. When you select both the Apply Background Color checkbox and the Apply Font Color checkbox, the background (cell color) and font color change depending on the value in the cell. This is shown in the figure, Automatic Conditional Formatting Features.
Note
You can also define if a value is visible or hidden when the form is run in the Enter Package Data app. If the value is hidden, it remains visible on the Cell Details screen.Note
If a cell has both "Automatic Conditional Formatting" and "Cell Formatting: Apply Total Style" enabled, conditional formatting takes precedence over cell formatting.
Note
You cannot format, for example, separation rows or data cells in a Sum row.
The figure, Insertion Mode "Display Dimension Values with Amounts", describes the selection of insertion mode Display Dimension Values with Amounts.
Using this insertion mode, the system displays transaction data stored in the Consolidation Journal Entries (ACDOCU) table for the selected dimension members. In our scenario for the selected FS Item and Subitem dimension members, the user cannot manually add any dimension values to the form.
The figure, Insertion Mode "Display and Add Dimension Values", describes the selection of insertion mode Display and Add Dimension Values.
The system inserts and displays dimension values based on the selected dimension members and displays their transaction data stored in the Consolidation Journal Entries (ACDOCU) table. Using the Input Help, you can add dimension members from the selection list of dimension members if necessary.
The figure, Insertion Model "Display All Dimension Values", describes the selection of insertion mode Display and All Dimension Values.
The system inserts and displays all master data combinations for the selected dimension members, whether or not there is transaction data (data stored in ACDOCU). The user cannot manually add any dimension values to the form.