Show TOC

Function documentationPerforming Intercompany Eliminations Locate this document in the navigation structure


The elimination of intercompany values is based on the hierarchical relationships defined in the entity dimension. This type of relationship can be evaluated using MDX-based rules, because the MDX language understands hierarchies. But there is a special SQL-based function that allows the system to evaluate hierarchies and understand parent-child relationships in a tree, to enable the elimination of intercompany values.


This function is named CPE (Common Parent Elimination) and supports the following syntax:

CPE( {Entity1} , {Entity2} [ , {Organization} [ , {ElimProperty} [ , {ElimDim} ] ] ] )

This function returns the name of the “Elimination entity” immediately below the first common parent of {Entity1} and {Entity2}, as found in the specified {Organization}. The Elimination entity is an immediate dependant of the common parent having the property {ElimProperty} set to Y.



Entity1 and Entity2

The parameters Entity1 and Entity2 are required and can be:

  • A specific name enclosed in double quotes (such as “Italy”)

  • A dimension name (such as ENTITY, meaning the current member of the ENTITY dimension)

  • A dimension name and property combination (such as INTCO.ENTITYPROP, meaning the value of the ENTITYPROP property of the current member of the INTCO dimension)

For example, the following returns the name of the elimination entity below EUROPE, for example E_EUROPE.


The following returns the name of the elimination entity below WORLD, for example E_WORLD.

  • CPE("ITALY" , "US")


This parameter is optional, and must be in the format Hn where n is the organization number (for example H1 or H2, and so on, to indicate PARENTH1 or PARENTH2, and so on). If omitted, the value H1 is assumed.


This parameter is optional, and can be used to indicate the name of the entity property that defines the elimination entities (that is, the entities where the amounts should be eliminated). If omitted, the value ELIM is assumed.


This parameter can be used in case the dimension for which the elimination must be performed is NOT a dimension of type ENTITY. In the following example the user is defining the elimination between Business Units in the BU dimension (here the INTERCO dimension is an inter-BU dimension).

*WHEN CPE(I_BU.BU,BU,H1,ELIM,BU)// note the additional parameter

*IS <>""




Example Example

The CPE( ) function is only valid in a *WHEN statement. The subsequent *IS statement should verify that a valid elimination entity has been returned. (*IS <> “”). The returned elimination entity can then be referenced in a subsequent *REC statements using the keyword %CPE%. The following example shows a correct use of the function:


*IS <>""

*REC( ENTITY = %CPE%, FACTOR = -1 ) // eliminate the original account...

*REC( ENTITY = %CPE%, ACCOUNT = ”PLUG”) //...into the account plug


End of the example.

Note Note

Note how the name of Entity1 is derived from the current ENTITY member and the name of Entity2 is derived from the ENTITY property of the current INTCO member.

End of the note.