Working with Essbase Calc Script
Essbase Calc Script is the language available to you to write your own custom business logic in Financial Consolidation and Close. This section provides some basic Essbase constructs as well as restrictions applied to Financial Consolidation and Close. For details regarding Essbase Calc Script, see Getting Started with Essbase Cloud for Administrators.
Common Syntax
- Semicolon
- Required at end of each statement
- Example:
Sales = Sales * 1.50;
- Not needed after FIX and ENDFIX
- Double Quotes
- Member names with spaces / special characters / start with number
- Best practice is to always use double quotes around a member name
- Example:
"Cash Ratio" = "Cash"/"Current Liabilities";
- Cross-dimensional Operator
- Use
->
to specify intersections of more than one dimension - Example:
"Sales"->"Changes In Net Income"->"Product1";
- Use
Comments
Comments start with /*
and end with */
.
Single or multi-line comments are supported.
You can use the toolbar icon to set or remove comment blocks.
FIX/ENDFIX
FIX/ENDFIX is one of the basic building blocks of any calc script. To do any calculation, you must define a FIX/ENDFIX section and then place the actual business calculations within it.
Example: Assume "Products" is your custom dimension and you want to calculate number of Televisions sold. You could use the following syntax:
FIX("Televisions")
"Units_Sold" = "LED_TVs" + "UHD_TVs";
ENDFIX
The actual business calculation is:
"Units_Sold = "LED_TVs" + "UHD_TVs";
, which calculates the number of Televisions sold.
Note:
You must put a semi-colon at the end of each calculation statement, but not for FIX or ENDFIX.The FIX/ENDFIX section limits members from various dimensions that participate in calculations within it. In this example, only "Televisions" are participating in the calculations.
You can define nested FIXes also, such as the following:
![Essbase Nested Example 1 Essbase Nested Example 1](img/essbase_fix_1.gif)
The above calculation can also be written as shown below:
![Essbase Nested Example 2 Essbase Nested Example 2](img/essbase_fix_2.gif)
For example, @List is an Essbase function. Essbase provides many functions. However, Financial Consolidation and Close does not support all Essbase functions. See the "Essbase Function List" section for a list of supported functions.
FIX on Financial Consolidation and Close Dimensions
An Financial Consolidation and Close application can contain 13 dimensions, depending on the application features that are enabled. Out of the possible 13 dimensions, you are not allowed to FIX on the following dimensions because the system automatically applies a FIX on them when a user invokes the consolidation process.
-
Scenario
-
Year
-
Period
-
View
-
Entity
For example, suppose you have the following statement:
![Essbase Fix example 3 Essbase Fix example 3](img/essbase_fix_3.gif)
The system will fail deployment and an error message will be logged in the Jobs console with the appropriate information.
However, this does not mean that you cannot use these dimensions in the script. Generally, a calculation will have something like the syntax below:
![Essbase Fix example 4 Essbase Fix example 4](img/essbase_fix_4.gif)
Note that in FIX and "Left hand side", you cannot use any member from the Scenario, Year, Period, Entity and View dimensions. But there is no such restriction on the "Right hand side" of the equation.
The following syntax would be allowed:
![Essbase Fix example 5 Essbase Fix example 5](img/essbase_fix_5.gif)
For the remaining eight dimensions, if you do not FIX on a particular dimension, the system will assume all the members from that dimension. For example, suppose you have the following statement:
![Essbase Fix example 6 Essbase Fix example 6](img/essbase_fix_6.gif)
In this example, all remaining dimensions have a FIX except Account. In this case, Essbase will consider all members from the Account dimension for the calculations within FIX/ENDFIX.
Restricted Financial Consolidation and Close Members
Certain system members within each Financial Consolidation and Close are restricted from being used or calculated in the calculation script. If restricted members are used in the calculation, the system will fail validation or deployment and an error message will be logged in the Jobs console.
Remember that if no members are specified for the dimension, the system assumes ALL members within the dimension. However, since restricted members are not allowed to be included as part of the process, you must explicitly exclude these restricted members in your FIX dimension statements or on the Left-hand side of the expression.
The following table is a complete listing of the restricted Financial Consolidation and Close members from these dimensions. Note that for the Currency and Consolidation dimensions, they are restricted based on the insertion rule being used. Note that each seeded insertion rule includes information in the Comment section as to which member of the Currency and Consolidation dimension can be included for that rule.
Table 20-5 Restricted Dimension Members
Dimension | Member | ExpressionLeft-Hand Side | ExpressionRight-Hand Side |
---|---|---|---|
Scenario | All Members | No | Yes |
Year | All Members | No | Yes |
Period | All Members | No | Yes |
View | All Members | No | Yes |
Entity | All Members | No | Yes |
Account | FCCS_CSTATUS | No | Yes |
FCCS_CSTATUS FILTER | No | Yes | |
FX Rates - Ending | No | Yes | |
FX Rates - Average | No | Yes | |
Average Rate | No | Yes | |
Ending Rate | No | Yes | |
SrcAverageRate | No | Yes | |
TgtAverageRate | No | Yes | |
SrcEndingRate | No | Yes | |
TgtEndingRate | No | Yes | |
FCCS_Balance | No | Yes | |
FCCS_CTA | No | Yes | |
FCCS_CICTA | No | Yes | |
FCCS_Percent Control | No | Yes | |
FCCS_Current Ratio | No | Yes | |
FCCS_Quick Ratio | No | Yes | |
FCCS_Cash Ratio | No | Yes | |
FCCS_Inventory Turnover | No | Yes | |
FCCS_Asset Turnover | No | Yes | |
FCCS_Days Sales In Receivables | No | Yes | |
FCCS_Days Sales In Inventory | No | Yes | |
FCCS_Gross Profit Margin | No | Yes | |
FCCS_Return on Sales | No | Yes | |
FCCS_Return on Equity | No | Yes | |
FCCS_Debt to Equity Ratio | No | Yes | |
FCCS_Debt Ratio | No | Yes | |
Data Source | FCCS_System Types | No | Yes |
FCCS_Rate Override | No | Yes | |
FCCS_Account Override | No | Yes | |
FCCS_PCON | No | Yes | |
FCCS_Driver Source | No | Yes | |
Movement | FCCS_Opening Balance | No | Yes |
FCCS_OpeningBalance_Cash | No | Yes | |
FCCS_FX_Total_NonCash | No | Yes | |
FCCS_ClosingBalanceCash | No | Yes |
FIX On All Members of a Dimension Except Restricted Members
It is a common use case where you need to FIX on all level 0 members of a dimension and that dimension may have restricted members. This example show how to optimally FIX on all level 0 members except the restricted members of that dimension.
For example, the Account dimension has the highest number of restricted members.
Following is the calc script code snippet that can be used to FIX on all level 0 Account members except restricted members:
@REMOVE( @LEVMBRS( "Account", 0 ), @LIST( @RELATIVE( "FCCS_System Account", 0 ),@RELATIVE( "FCCS_Drivers", 0 ),@RELATIVE( "FCCS_Ratios", 0 ),@RELATIVE( "Exchange Rates", 0 ) ) )
FIX on Sparse versus Dense dimensions
FIX is more effective when it is done on Sparse dimensions. In this case, FIX will make Essbase pull blocks only for the combination of Sparse dimension members that are defined in the FIX and skip the rest.
These sparse combinations work as indexes for Essbase to search the data blocks that match indexes and pull them for calculations. Therefore, not all blocks are pulled for the system to perform.
When FIX is used on a Dense dimension, dense members will be present in each data block in Essbase. Therefore, this would have a performance impact if not used correctly. Note than when FIX is on the Dense dimension, Essbase will pull all data blocks without limit to the number of blocks and will only limit to a portion within each block. Therefore, it may require multiple passes to the database to return the information.
For example, you could reference "Sales" and "PostSales" from the Account dimension with the following statements:
![Essbase Fix example 7 Essbase Fix example 7](img/essbase_fix_7.gif)
When the system processes the first fix on "Sales", Essbase pulls all data blocks of the Account dimension but only works on the one "Sales" account.
Later in the FIX statement on "PostSales", Essbase again pulls all data blocks of the Account dimension but only works on the one "PostSales" account. In this case, two passes are made to the database for these two accounts.
To avoid a performance issue, you can avoid using FIX on the Account dimension, but use IF...THEN for a Dense dimension.
![Essbase Fix example 8 Essbase Fix example 8](img/essbase_fix_8.gif)
In this example where you are not using the FIX statement, you only need to make one pass to the Essbase database.
The recommendation is to use FIX on Sparse dimensions, and use IF..THEN for Dense dimensions to help calculation performance.
Member Block
Member blocks are also known as calculation blocks. Sometimes you will see the term "anchor" used for Member blocks. The syntax of a member block is as follows:
![Essbase Member Block Example 1 Essbase Member Block Example 1](img/essbase_member1.gif)
Following is an example of member block statements:
![Essbase Member Block Example 2 Essbase Member Block Example 2](img/essbase_member2.gif)
In this example, "My Total Opening Balance" is known as a calculation block member or anchor. Whenever possible, this member should be a member from a Dense dimension.
In the above calculation, we will only limit to the member "My FX Opening" and the calculations are performed on the member specified in the FIX statement.
Note that when using IF statements, you will need a member block. You cannot write IF statements outside of Member blocks.