Essbase member consolidation properties control how data values roll up, from child members in the hierarchy to their parents. Examples follow for using most of the consolidation operators in outline member properties, including + (add), - (subtract), * (multiply), / (divide), % (percentage calculation), and ~ (no consolidation).

In these examples, assume that initially, the Essbase cube has not yet been calculated, and only level-0 members have values. These values have been loaded to the cube, but prior to calculation, the data has not yet been rolled up to parent members.

Consolidation using member operators happens in top-down order. As the default consolidation is addition, the (+) operator can be demonstrated first.

Example with (+) Operator

In this example below, P1 has no value before calculation. Its value will display as #MISSING if queried in the spreadsheet before the cube has been calculated.

P1
  M1 (+)  10
  M2 (+)  15
  M3 (+)  20

Once the cube has been calculated, P1 will have a value of 45. The consolidation proceeds top down, as follows:

P1 = P1+M1 = #MISSING+10 = 10
P1 = P1+M2 = 10+15 = 25
P1 = P1+M3 = 25+20 = 45

Example with (-) Operator

Now, consider a similar hierarchy with members that consolidate using subtraction.

P2
  M1 (-)  10
  M2 (-)  15
  M3 (-)  20

Once the cube has been calculated, P2 will have a value of -45. The consolidation proceeds top down, as follows:

P2 = P2–M1 = #MISSING–10 = -10
P2 = P2-M2 = -10-15 = -25
P2 = P2-M3 = -25–20 = -45

Example with (*) Operator

Consider a hierarchy with members that consolidate using multiplication.

P3
  M1 (*)  10
  M2 (*)  15
  M3 (*)  20

Once the cube has been calculated, P3 will have a value of #MISSING. The consolidation proceeds top down, as follows:

P3 = P3*M1 = #MISSING*10 = #MISSING
P3 = P3*M2 = #MISSING*15 = #MISSING
P3 = P3*M3 = #MISSING*20 = #MISSING

The final consolidated value of P3 may not have been the intended result. If the requirement is for P3 to be the multiplied product of all the child members, try using the (+) operator on the first child member:

P3
  M1 (+)  10
  M2 (*)  15
  M3 (*)  20

With the first operator changed to addition, P3 will have a non-#MISSING value after the cube is calculated. The consolidation proceeds top down, as follows:

P3 = P3+M1 = #MISSING+10 = 10
P3 = P3*M2 = 10*15 = 150
P3 = P3*M3 = 150*20 = 3000

Example with (/) Operator

Consider a hierarchy with members that consolidate using division (except for the first child member).

P4
  M1 (+)  10
  M2 (/)  15
  M3 (/)  20

Once the cube has been calculated, P4 will have a value of .033. The consolidation proceeds top down, as follows:

P4 = P4+M1 = #MISSING+10 = 10
P4 = P4/M2 = 10/15 = 0.666
P4 = P4/M3 = 0.666/20 = 0.033

Example with (%) Operator

Consider a hierarchy with members that consolidate using percentage calculation ([a/b]*100), except for the first child member.

P5
  M1 (+)  10
  M2 (%)  15
  M3 (%)  20

Once the cube has been calculated, P5 will have a value of 333. The consolidation proceeds top down, as follows:

P5 = P5+M1 = #MISSING+10 = 10
P5 = (P5/M2)*100 = (10/15)*100 = 66.6
P5 = (P5/M3)*100 = (66.6/20)*100 = 333

Example with Many Operators

Consider the following hierarchy that consolidates with a variety of operators.

Parent1
  Member1 (+)  10
  Member2 (+)  20
  Member3 (-)  25
  Member4 (*)  40
  Member5 (%)  50
  Member6 (/)  60
  Member7 (~)  70

Essbase calculates Member1 through Member6 as follows:

Parent1 = Parent1+Member1 = #MISSING+10 = 10;
Parent1 = Parent1+Member2 = 10+20 = 30; 
Parent1 = Parent1+Member3 = 30-25 = 5;
Parent1 = Parent1+Member4 =  5*40 = 200;
Parent1 = Parent1+Member5 = (200/50)*100 = 400
Parent1 = Parent1+Member6 = 400/60 = 6.666;

Because Member7 is set to No Consolidation(~), Essbase ignores Member7 in the consolidation. The final value of Parent1 is therefore 6.666.

See also some important considerations in Use of the Calculation Operators *, /, and %.