Consolidation Functions
Return to Financial Consolidation Model Overview.
Consolidation functions retrieve data from the database for every consolidation rule line. This includes amounts from the regular fact cubes, parameters, and data from the investment register.
Combinations of entities
Consolidation measures are designed to establish a business relationship between two entities within the scope. Within the Financial Consolidation model, much of the captured data is organized by the legal entity that reports the data. Some of this data represents intercompany relations, so the internal partner entity also breaks down the amounts. For this type of data, the two parties of the intercompany relationship are defined by the reporting entity and its partner entity. For example:
Entity 11 reports 80,563.50 USD internal revenue with the partner entity 15.
Entity 15 reports 79,662.94 USD (after currency conversion) internal cost of sales with the partner entity 11.
For some consolidation measures, intercompany relations need to align the reported data from both entities and treat their combination as a single transaction (one journal entry.) To enhance the matching and subsequent processing, many consolidation functions allow the intercompany relation to be inverted, which means that when processing the data, two parties are exchanged. For example:
The partner of entity 11 reports USD 79,662.94 (after currency conversion) internal cost of sales with the partner, whose partner is 15.
Following the inversion of the I/C relation, the subsequent matching of corresponding records and subsequent processing becomes exceptionally straightforward. Consolidation functions that reverse the I/C relations typically incorporate an additional 'X' (representing exchange) in their function code.
Several parameters also pertain to a pair of entities. However, since these parameters are not reported as separate financial statements, the two involved entities are not as obvious. The consolidation functions outlined below specify which entity is designated as the reporting entity (or legal entity) and which is classified as the partner entity.
Some data, such as equity or tax liabilities, does not represent an intercompany relationship. To allow the processing of such data in the context of a specific pair of entities, the Financial Consolidation model offers this data for every entity in the group so that it can be processed in the context of the correct pair (but also in the context of any other pair). The model offers additional filters for the intercompany parties to restrict the consolidation rules to specific pairs, e.g., where one entity holds direct ownership of the other entity. This filter prevents the execution of the consolidation function on any other unintended partner and restricts the processed data from the multiple offerings again to one correct pair of entities.
For example, the consolidation rule for non-controlling interests operates on the group's holding in pair with any fully consolidated entity with a consolidation rate of less than 100% in that group. In the EU scope (whose holding is entity 17), this applies to entities 12 and 14, so only these two pairs match the criteria of the additional filter. The consolidation rule processes the equity of 12 and 14 with inverted I/C relations, so it offers the equity 12 for every entity with partner 12 and 14 for every entity with partner 14. Most of these pairs are unintended and must be suppressed by the filter.
The PEQ (proportional equity) function offers:
Legal Entity | Partner Entity | BS Account | Value (converted to the group currency) | Additional filter for the intercompany parties: Is the legal entity the holding and partner entity fully consolidated with less than 100%? |
12 | 12 | Total equity | 229.176 | No |
14 | 12 | Total equity | 229.176 | No |
15 | 12 | Total equity | 229.176 | No |
17 | 12 | Total equity | 229.176 | Yes |
12 | 14 | Total equity | 173.562 | No |
14 | 14 | Total equity | 173.562 | No |
15 | 14 | Total equity | 173.562 | No |
17 | 14 | Total equity | 173.562 | Yes |
… | … | Total equity | … | No |
The descriptions of the individual consolidation functions below state which require one of the additional filters for the intercompany parties to prevent unintended duplication of entries.
Parameters
Most functions share similar parameters that specify query details. This section provides a detailed explanation of these parameters, aiming to maintain a concise list of individual functions.
Parameter | Description |
Factor | Eventually flips the sign of the processed facts |
Eliminate | The sign of the retrieved value is flipped to eliminate an amount from the financial statement |
Recognize | The retrieved value retains its original sign, allowing for identification of an amount (or a value derived from it) in a different context, such as on another account or transaction type. |
Rate 1 and Rate 2 | The retrieved value retains its original sign, allowing for identification of an amount (or a value derived from it) in a different context, such as on another account or transaction type. |
Parameters for one of the account dimensions, as well as for the transaction type dimension, consist of three individual settings, which correspond to the parameters of the dimension extract from Jedox Integrator
Parameter | Description |
Operator equal | The value must be an element of the corresponding account dimension or transaction type dimension |
Operator inAlpharange | The value is a range for alphanumerical comparison, e.g. [A100,D200], and selects all elements within this range. |
Operator inrange | The value is a range for alphanumerical comparison, e.g. [1000,2000], and selects all elements within this range. |
Operator like | The value is a regular expression. This selects all elements that match the pattern. This operator is case-sensitive. You can change that by using the (?i) modifier at the start of a regular expression. |
Operator Subset | The value is the name of a stored global Subset for that dimension. |
Parameter | Description |
Only Nodes | Values are retrieved from the elements that the operator and value select. If any of them happen to be a consolidated element, the consolidated value from that element is retrieved. Note that in this case, the resulting posting journal entry cannot be posted to the same element but has to be posted on some other base element instead. |
Only Bases | If the elements selected by the operator and value are consolidated, the values are retrieved from all base-level descendants thereof. This could potentially lead to an increase in the number of processed figures. Subsequently, the resulting posting journal entry can be posted to these same elements. |
In most cases, the consolidation measures operate on top of the combined financial statement, so the Combined Financial Statement level shall be selected from the Consolidation dimension. It's important to note that this level already encompasses certain transformations, such as the removal of entities consolidated at equity, which happens on the Adjustment Consolidation Method level. In exceptional cases, the consolidation rules need to access data before certain transformations, for instance, from the Group GAAP Corrected level. |
Scope of Consolidation Parameters
The functions in this group query parameters from the Scope of Consolidation cube. Special attention should be paid to the legal entity and partner entity roles.
:child | Name | Text | Parameters |
CRT | Consolidation Rate of holding on the Partner Entity | This consolidation function can be used only once in a consolidation rule line. | Valid for the Consolidation ledger |
NCR | 1 - Consolidation Rate of holding on Partner Entity | This consolidation function can be used only once in a consolidation rule line. | Valid for the Consolidation ledger |
DO | Direct Ownership Rate of Legal Entity on Partner Entity | This consolidation function can be used only once in a consolidation rule line. | Valid for the Consolidation ledger |
DOX | Direct Ownership Rate of Partner Entity on Legal Entity | This consolidation function can be used only once in a consolidation rule line. | Valid for the Consolidation ledger |
TXR | Tax rate of legal entity | This consolidation function can be used only once in a consolidation rule line. | Valid for the Consolidation ledger |
TXRP | Tax rate of partner entity | This consolidation function can be used only once in a consolidation rule line. | Valid for the Consolidation ledger |
PM | Profit margin | This consolidation function can be used only once in a consolidation rule line. | Valid for the Consolidation ledger |
PMX | Profit margin with inverted I/C relation | This consolidation function can be used only once in a consolidation rule line. | Valid for the Consolidation ledger |
Fact Selectors
The functions in this group select figures from the regular fact cubes Profit and Loss, Balance Sheet, and their counterparts in the Segment Consolidation ledger. The parameters are used to specify the details of the query.
:child | Name | Text | Parameters | Validity | Notes |
PnL | Profit and Loss account with I/C relation | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
PnL Account: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers. |
- |
PnLX | Profit and Loss account with inverted I/C relation | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
PnL Account: Operator, value, and mode.
|
Valid for the Consolidation and Segment Consolidation ledgers. |
- |
PnLCr | Profit and Loss account with I/C relation, only credit values | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
PnL Account: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers. |
- |
PnLXCr | Profit and Loss account with inverted I/C relation, only credit values | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
PnL Account: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers. |
- |
PnLDr | Profit and Loss account with I/C relation, only debit values | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
PnL Account: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers |
- |
PnLXDr | Profit and Loss account with inverted I/C relation, only debit values | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
PnL Account: Operator, value and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers. |
- |
BS | Balance Sheet account with I/C relation on specific transaction type | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers. | - |
BSX | Balance Sheet account with inverted I/C relation on specific transaction type | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers. | - |
BSHist | Balance Sheet account with I/C relation on specific transaction type, converted at historic rate into the group currency | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger. | - |
BSXHist | Balance Sheet account with inverted I/C relation on specific transaction type, converted at historic rate into the group currency | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger. | - |
BSFXDiff | Balance Sheet account with I/C relation on specific transaction type, foreign exchange differences from historic to month-end conversion | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger. | - |
.BSXFXDiff | Balance Sheet account with inverted I/C relation on specific transaction type, foreign exchange differences from historic to month-end conversion | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger. | - |
BSR | Balance Sheet account times a rate with I/C relation on specific transaction type | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger. | - |
BSRX | Balance Sheet account times a rate with inverted I/C relation on specific transaction type | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger. | - |
PBSR | Balance Sheet account times a rate and a proportional factor with I/C relation on specific transaction type | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger. | - |
PBSRX | Balance Sheet account times a rate and a proportional factor with inverted I/C relation on specific transaction type | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger. | - |
BSExt | Balance Sheet account for the external (~) partner entity on specific transaction type | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers. |
Note that this function requires one of the additional filters for the intercompany parties to prevent unintended duplication of entries. |
BSXExt | Balance Sheet account reported by the partner entity with external (~) partner entity on specific transaction type. | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation and Segment Consolidation ledgers |
Note that this function requires one of the additional filters for the intercompany parties to prevent unintended duplication of entries. |
PEQ | Equity account times a proportional factor for all partner entities on specific transaction type | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger | Note that this function requires one of the additional filters for the intercompany parties to prevent unintended duplication of entries. |
PEQHist | Equity account times a proportional factor for all partner entities on specific transaction type, converted at historic rate into the group currency | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger | Note that this function requires one of the additional filters for the intercompany parties to prevent unintended duplication of entries. |
PEQFXDiff | Equity account times a proportional factor for all partner entities on specific transaction type, foreign exchange differences from historic to month-end conversion | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
BS Account: Operator, value, and mode. Transaction Type: Operator, value, and mode. Consolidation: Element from the Consolidation dimension |
Valid for the Consolidation ledger | Note that this function requires one of the additional filters for the intercompany parties to prevent unintended duplication of entries. |
Calculation
The calculation function allows arbitrary calculations based on other consolidation rule lines. In a simplistic view, the calculation expression refers to other lines by single uppercase letters (e.g., A, B, C, … ) in combination with basic math operators (+
, -
, *
, /
) and parenthesis.
The calculation syntax extends further by providing comprehensive support for the Jedox rule syntax in expressions, encompassing everything to the right of the equal sign (=
) in a business rule, including elements such as B:
, C:
, and all other functions. Subsequently, the consolidation engine generates a business rule for the new consolidation rule line by using the calculation as its expression and converts all single uppercase letters into static cell references to the results of other consolidation rule lines.
Eliminations
The functions in this group are much-simplified versions of the fact selectors with none or only very few parameters. In contrast to the fact selectors, the elimination functions use the Type
, Consolidation Rule, and Clearing Account
attributes of the account dimensions to identify the accounts to be processed automatically by the consolidation rule.
Functions | Name | Text | Parameters | Validity |
Elim.Assets | Elim.Assets | This consolidation function can be used only once in a consolidation rule line. | Transaction Type: Operator, value, and mode. | Valid for the Consolidation and Segment Consolidation ledgers |
ElimX.Assets | Eliminate assets (with inverted I/C relation) | This consolidation function can be used only once in a consolidation rule line. | Transaction Type: Operator, value, and mode. | Valid for the Consolidation and Segment Consolidation ledgers |
Elim.Liabilities | Eliminate liabilities | This consolidation function can be used only once in a consolidation rule line. | Transaction Type: Operator, value, and mode. | Valid for the Consolidation and Segment Consolidation ledgers |
ElimX.Liabilities | Eliminate liabilities (with inverted I/C relation) | This consolidation function can be used only once in a consolidation rule line. | Transaction Type: Operator, value, and mode. | Valid for the Consolidation and Segment Consolidation ledgers |
Elim.Income | Eliminate income | This consolidation function can be used only once in a consolidation rule line. | - | Valid for the Consolidation and Segment Consolidation ledgers |
ElimX.Income | Eliminate income (with inverted I/C relation) | This consolidation function can be used only once in a consolidation rule line. | - | Valid for the Consolidation and Segment Consolidation ledgers |
Elim.Expenses | Eliminate expenses | This consolidation function can be used only once in a consolidation rule line. | - | Valid for the Consolidation and Segment Consolidation ledgers |
ElimX.Expenses | Eliminate expenses (with inverted I/C relation) | This consolidation function can be used only once in a consolidation rule line. | - | Valid for the Consolidation and Segment Consolidation ledgers |
Elim.IncomeCredit | Eliminate income, only credit values | This consolidation function can be used only once in a consolidation rule line. | - | Valid for the Consolidation and Segment Consolidation ledgers |
ElimX.IncomeDebit | Eliminate income, only debit values (with inverted I/C relation) | This consolidation function can be used only once in a consolidation rule line. | - | Valid for the Consolidation and Segment Consolidation ledgers |
Investment Register Selectors
The functions in this group select data from the investment register, which captures data for various Business Transaction Types. Consolidation rules are designed to handle specific consolidation measures.
To fulfill this criterion, the consolidation functions accessing the investment register consistently target a designated business transaction type specified by the Business Transaction Type
parameter.
If this specific type of business transaction happened for a combination of entities in the current reporting period, the function will return values.
Functions | Name | Text | Parameters | Validity |
InvRegBSHist | Investment (balance sheet account) from the investor, converted at historic rate into the group currency | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. | Business Transaction Type: One type of business transactions from the Investment Register | Valid for the Consolidation ledger |
InvRegPnL | Profit (loss) (profit and loss account) from investor), converted into the group currency. | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. | Business Transaction Type: One type of business transactions from the Investment Register | Valid for the Consolidation ledger |
InvRegPEQHist | Equity account times a proportional factor for all partner entities, converted at historic rate into the group currency | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have its own parameters. |
Business Transaction Type: One type of business transactions from the Investment Register BS Account: Operator, value, and mode. |
Valid for the Consolidation ledger |
InvRegBSFXDiff | Investment (balance sheet account) from investor, foreign exchange differences from historic to month-end conversion | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. | Business Transaction Type: One type of business transactions from the Investment Register | Valid for the Consolidation ledger |
InvRegPnLFXDiff | Profit (loss) (profit and loss account) from the investor), foreign exchange differences from historic to month-end conversion | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. | Business Transaction Type: One type of business transactions from the Investment Register | Valid for the Consolidation ledger |
InvRegPEQFXDiff | Equity account times a proportional factor for all partner entities, foreign exchange differences from historic to month-end conversion | This consolidation function can be used multiple times in one consolidation rule line. Each of these details can have their own parameters. |
Business Transaction Type: One type of business transactions from the Investment Register BS Account: Operator, value, and mode. |
Valid for the Consolidation ledger |
Updated November 5, 2024