Enables the choice of an execution mode of a formula. @CALCMODE can control two types of modes:
 Whether a formula is calculated in block calculation or cell calculation mode when calculating formulas that contain certain functions (in particular the @ISMBR function)
 Whether a formula assigned to a sparse member is calculated in bottomup or topdown mode
Syntax
@CALCMODE (CELLBLOCKTOPDOWNBOTTOMUP)
CELL  Turns on the cell calculation mode 
BLOCK  Turns on the block calculation mode 
TOPDOWN  Turns on the topdown calculation mode 
BOTTOMUP  Turns on the bottomup calculation mode 
Cell and block modes are mutually exclusive. Topdown and bottomup modes are mutually exclusive. Within one @CALCMODE specification, you can specify only one option. To specify both types of modes, perform the instruction twice; for example:
@CALCMODE (CELL)
@CALCMODE (TOPDOWN)
Description
Understanding Block Calculation and Cell Calculation Modes
Using block calculation mode, Essbase groups the cells within a block and simultaneously calculates the cells in each group. Block calculation mode is fast, but you must carefully consider data dependencies within the block to ensure that the resulting data is accurate.
Using cell calculation mode, Essbase calculates each cell sequentially, following the calculation order, which is based on the order of the dense dimensions in the outline. For more information on calculation order, see the Hyperion Essbase – System 9 Database Administrator’s Guide.
For more information on which mode Essbase uses and on data dependency issues, see Notes.
Understanding BottomUp and TopDown Calculation Modes
Essbase uses one of two calculation methods to do a full calculation of a database outline: bottomup calculation (the default) or topdown calculation. If the database outline contains a complex member formula, Essbase performs a topdown calculation for that member. When a formula is compiled, if the formula is to be calculated topdown, Essbase logs a message in the application log file.
For a bottomup calculation, Essbase determines which existing data blocks need to be calculated before it calculates the database. Essbase then calculates only the blocks that need to be calculated during the full database calculation. The calculation begins with the lowest existing block number and works up through each subsequent block until the last existing block is reached.
In contrast, a topdown calculation calculates the formula on all potential datablocks with the member. A topdown calculation may be less efficient than a bottomup calculation because more blocks may be calculated than is necessary. Although a topdown calculation is less efficient than a bottomup calculation, in some cases topdown calculations are necessary to ensure that calculation results are correct. See Example 4.
For more information about bottomup and topdown calculation modes, see the Hyperion Essbase – System 9 Database Administrator’s Guide.
Notes
Knowing When Essbase uses Cell or Block Mode and Topdown or Bottomup Mode
 When Essbase compiles a formula, it prints a message in the application log file explaining the mode of execution for the formula similar to the following message:
Formula on member Profit % will be executed in CELL and TOPDOWN mode.
When Essbase determines that the formula will be executed in block and bottomup mode, no message is written in the application log file.
 In calculation scripts, @CALCMODE statements must be placed within parentheses and associated with a specific database member.
 By default, for a simple formula such as A = B + C, Essbase does a bottomup calculation. A is calculated only if B or C exists in the database. The dependency of the formula on B and C is known before the calculation is started.
For a complex formula such as A = B>D + C>D, Essbase performs a topdown calculation because every possible combination of A must be examined to see whether B>D or C>D exists.
 By default, Essbase uses cell calculation mode for formulas containing:
 @ANCEST
 @CURRMBR
 @ISMBR on a dense member
 @MDANCESTVAL
 @MDPARENTVAL
 @MDSHIFT
 @NEXT
 @PARENT
 @PARENTVAL
 @PRIOR
 @SANCESTVAL
 @SPARENTVAL
 @SHIFT

For all other formulas, Essbase uses block calculation mode by default.
You can also set CALCMODE BLOCK or CALCMODE BOTTOMUP at the Essbase Server, application, or database level using the configuration setting CALCMODE.
Understanding Data Dependency Issues With Block Calculation Mode
Data dependency occurs if the accurate calculation of one or more members depends on another member or other on members being calculated previously. Most data dependency issues with block calculation mode occur when a formula contains IF ELSE or IF ELSEIF conditions. However, data dependencies can occur in other formulas; for example, when using the @PRIOR function.
Data Dependency Issues With IF ELSE and IF ELSEIF
When Essbase uses block calculation mode to calculate a formula that contains IF ELSE or IF ELSEIF conditions, it separates the members being calculated into two groups. The first group contains the members that satisfy the IF condition. The second group contains the members that satisfy the ELSE or ELSEIF conditions.
Essbase simultaneously calculates the members in the first group before simultaneously calculating the members in the second group. See Example 1.
If a formula contains data dependencies, ensure that the following conditions are met:
 Members on which the accurate calculation of other members depends are in the first group.
 Dependent members are in the second group.
If an IF condition has multiple ELSEIF conditions, Essbase evaluates each ELSEIF condition, placing the members that satisfy the ELSEIF condition in the first group and the members that satisfy subsequent ELSEIF or ELSE conditions in the second group. See Example 2.
Understanding Other Data Dependency Issues
Data dependencies can occur in formulas that do not contain IF ELSE conditions. See Example 3 for an example of data dependency in a formula containing the @PRIOR function.
You can also set CALCMODE BLOCK or CALCMODE BOTTOMUP at the Essbase Server, application, or database level using the configuration setting CALCMODE.
Examples
Example 1, Example 2, and Example 3 illustrate use of the BLOCK and CELL options of the @CALCMODE function. Example 4 illustrates use of the BOTTOMUP and TOPDOWN options.
Example 1
Consider a database with two dense dimensions, Time and Accounts. The following formula is placed on the Budget Sales member of the Accounts dimension. Because this is a formula containing @ISMBR applied to a dense member (Budget Sales), by default Essbase uses cell calculation mode. Use the @CALCMODE(BLOCK) function to specify block calculation mode for this formula.
@CALCMODE(BLOCK); IF(@ISMBR(Feb)) "Budget Sales"=100; ELSE "Budget Sales"=Feb+10;
According to the above formula, we expect that if the member being calculated is Feb, the Budget Sales value is 100. If the member being calculated is not Feb, the Budget Sales value is 100+10 (the value for Feb + 10).
Assume that we load the values 10, 20, and 30 into the Budget Sales data block for Jan, Feb and Mar, as follows:
Jan  Feb  Mar  
Budget Sales  10  20  30 
Using block calculation mode, Essbase calculates the members satisfying the IF condition first. In this example, Feb is the only member that satisfies the IF condition. After calculating Feb, Essbase calculates the members Jan and Mar. In this example, the results are as expected:
Jan  Feb  Mar  
Budget Sales  110  100  110 
Example 2
Now consider the same database as in Example 1, but we place the following formula on the Budget Sales member of the Accounts dimension. As in Example 1, because this is a formula containing @ISMBR applied to a dense dimension member (Budget Sales), by default Essbase uses cell calculation mode. However, we use the @CALCMODE(BLOCK) function to specify the block calculation mode for this formula.
@CALCMODE(BLOCK); IF(@ISMBR(Mar)) "Budget">"Sales"=Feb+20; ELSEIF(@ISMBR(Jan)) "Budget">"Sales"=Feb+10; ELSE "Budget">"Sales"=100; ENDIF
According to this formula, we want the Jan and Mar Budget Sales values to be calculated based on the Feb Budget Sales value, which is 100. We want to see the following results:
Jan  Feb  Mar  
Budget Sales  110  100  120 
Assume that we load the values 10, 20, and 30 into the Budget Sales data block for Jan, Feb, and Mar, as follows:
Jan  Feb  Mar  
Budget Sales  10  20  30 
Using block calculation mode, Essbase calculates the members satisfying the IF condition first, followed by the members satisfying the ELSEIF condition, followed by the members satisfying the ELSE condition. In this example, Essbase calculates the members in the following order: Mar, Jan, Feb. The results are not what we want, because the calculation of Jan and Mar is dependent on the calculation of Feb and Feb is calculated after Jan and Mar. The inaccurate results are as follows:
Jan  Feb  Mar  
Budget Sales  30  100  40 
To achieve the desired results, use the @CALCMODE(CELL) function.
Example 3
The following formula calculates the members Opening Inventory and Ending Inventory using the @PRIOR function. There is a data dependency between Opening Inventory and Ending Inventory. The formula is placed on the Opening Inventory member. The example shows the results for January, February, and March.
@CALCMODE(BLOCK) "Opening Inventory"=@PRIOR("Ending Inventory")+10; "Ending Inventory"="Opening Inventory";
Before the calculation, there is no data for these members (the data is #MISSING or #MI):
Jan  Feb  Mar  
Opening Inventory  #MI  #MI  #MI 
Ending Inventory  #MI  #MI  #MI 
Using block calculation mode, Essbase calculates the members simultaneously, taking the previous month’s Ending Inventory #MISSING value as 0 for all member combinations and adding 10. This is not the desired result.
Jan  Feb  Mar  
Opening Inventory  10  10  10 
Ending Inventory  10  10  10 
The following formula on the Opening Inventory member causes Essbase to use cell calculation mode (the default for formulas containing the @PRIOR function):
"Opening Inventory"=@PRIOR("Ending Inventory")+10; "Ending Inventory"="Opening Inventory";
The results are as follows:
Jan  Feb  Mar  
Opening Inventory  10  20  30 
Ending Inventory  10  20  30 
Example 4
Depending on the formula and the structure of the data, calculating a formula topdown versus bottomup may involve two issues: performance (reflecting the number of calculations that must be made) and accuracy. This example compares calculation results to illustrate both of these issues.
Before the calculation, assume that Actual and Budget are members of a sparse dimension and they contain the following data:
Cola  New York  Sales  
Actual  Budget  
Jan  #MISSING  50  
Feb  200  #MISSING  
Mar  400  450 
The following formula is calculated bottomup.
@CALCMODE(BOTTOMUP); Budget=Actual*1.10;
This bottomup calculation calculates two values, based on existing combinations of Budget, with the following results:
Cola  New York  Sales  (Comment)  
Actual  Budget  
Jan  #MISSING  #MISSING  (#MISSING*1.10)  
Feb  200  #MISSING  (No calculation is performed)  
Mar  400  440  (400*1.10) 
The following formula is calculated topdown.
@CALCMODE(TOPDOWN); Budget=Actual*1.10;
This topdown calculation calculates three values, considering all potential combinations of Budget, with the following results:
Cola  New York  Sales  (Comment)  
Actual  Budget  
Jan  #MISSING  #MISSING  (#MISSING*1.10)  
Feb  200  220  (200*1.10)  
Mar  400  440  (400*1.10 