ConsolidateChildren

ConsolidateChildren forces consolidated values to be calculated by summing immediate children along a specified dimension. ConsolidateChildren is useful when intermediate consolidations are calculated by rules and you want a parent consolidation to be calculated by summing the intermediate consolidations rather than by summing the underlying leaf values.

This function is valid in both rules and TurboIntegrator processes.

Syntax

ConsolidateChildren(DimName1, DimName2, ...)

Argument

Description

DimName1, DimName2, ...

Names of the dimensions along which consolidations will be performed.

The function requires at least one DimName argument, and can accept as many DimName arguments as there are dimensions in the cube for which the rule is written.

Example

Consider a cube named Sales composed of the dimensions ActVsBud, Region, Model, Account1, and Month.

In this example, the Month dimension is defined as follows:

A sample dimension with Year, and each Quarter having leaf elements of the months.

If no rule is in place for this cube, the value of the Year consolidation is calculated by summing all the underlying leaf values, in this case Jan through Dec. The following image illustrates this consolidation.

Sample view with Year consolidation calculated by summing all underlying leaf values.

Now, suppose you create the following rule for this cube, which indicates that all quarterly values should be 1:

[{'1 Quarter', '2 Quarter', '3 Quarter', '4 Quarter'}]=1;

The result is as follows:

Same view as in previous images with each Quarter set to 1. Year consolidation is 3156 since quarter values are summed.

In the following image, you can see that quarterly values are indeed calculated by the rule, but the Year consolidation is still calculated by summing all underlying leaf values. If this is not your desired calculation path, you can use the ConsolidateChildren function to force TM1® to calculate the Year consolidation by summing its immediate children, specifically 1 Quarter, 2 Quarter, 3 Quarter, and 4 Quarter.

['Year']=ConsolidateChildren('Month');[{'1 Quarter', '2 Quarter', '3 Quarter', '4 Quarter'}]=1;

In the rule, the statement ['Year']=ConsolidateChildren('Month') says that the Year consolidation should be calculated by summing the immediate children of Year in the Month dimension.

The following image shows the result of the ['Year']=ConsolidateChildren('Month') statement:

Same view as in previous images where the Year consolidation has changed from 3156 to 4.

Note that the Year consolidation is now calculated by summing its immediate children.

It's important to remember that for a given consolidation, the ConsolidateChildren function applies only to the immediate children of the consolidation.

The ConsolidateChildren function can also be used to specify how consolidations are calculated in multiple dimensions, as in the following example:

Argument

Description

['World','Year']= ConsolidateChildren('Region','Month')

This statement applies the ConsolidateChildren function to both the World and Year consolidations. In this case, World is calculated by summing all its immediate children in the Region dimension, while Year is calculated by summing its immediate children in the Month dimension.