Working with Essbase Calc Script

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";

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:

The above calculation can also be written as shown below:

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:

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:

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:

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:

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 19-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:

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.

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:

Following is an example of member block statements:

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.