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.

General Parameters
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

Operator and value
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.
Mode
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.
Consolidation

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.
Consolidation: Element from the Consolidation dimension

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