OracleFree referencePDF, Excel, Word exportOracle Hyperion / EssbaseAll Oracle Hyperion and Essbase tools

Essbase Calculation Functions List with Syntax and Examples

A searchable list of 44 Essbase block storage calculation functions, the @ functions used in calc scripts, member formulas and Hyperion Planning business rules. Each row gives the category, syntax from the Technical Reference, what it returns, a short example and a performance note.

Download:

Function	Category	Syntax	Returns	Example	Performance note
@ISMBR	Boolean	@ISMBR(mbrName \| rangeList \| mbrList)	TRUE if the current member matches	IF (@ISMBR("Budget")) ... ENDIF	Cheap; prefer FIX over IF on sparse members
@ISLEV	Boolean	@ISLEV(dimName, levName \| levNum)	TRUE if current member is at that level	IF (@ISLEV("Entity", 0))	Cheap
@ISGEN	Boolean	@ISGEN(dimName, genName \| genNum)	TRUE if current member is at that generation	IF (@ISGEN("Period", 3))	Cheap
@ISCHILD	Boolean	@ISCHILD(mbrName)	TRUE if current member is a child of mbrName	IF (@ISCHILD("Q1"))	Cheap
@ISDESC	Boolean	@ISDESC(mbrName)	TRUE if current member is a descendant of mbrName	IF (@ISDESC("Total Opex"))	Cheap
@ISUDA	Boolean	@ISUDA(dimName, UDAStr)	TRUE if current member has the UDA	IF (@ISUDA("Entity", "USD"))	Cheap; @UDA in FIX is better for sparse
@PARENTVAL	Relationship	@PARENTVAL(dimName [, mbrName])	Value of the parent of the current member	"Share" = "Sales" / @PARENTVAL("Product", "Sales");	Reads another block when the dimension is sparse
@ANCESTVAL	Relationship	@ANCESTVAL(dimName, genLevNum [, mbrName])	Value of the ancestor at a generation or level	@ANCESTVAL("Product", 2, "Sales")	Cross-block reads on sparse dims
@MDANCESTVAL	Relationship	@MDANCESTVAL(dimCount, dimName1, genLevNum1, ... [, mbrName])	Ancestor value across several dimensions	@MDANCESTVAL(2, "Market", 2, "Product", 2, "Sales")	Heavier; use for allocation bases
@PRIOR	Range / financial	@PRIOR(mbrName [, n, XrangeList])	Value n members back in the range (default Time dimension)	"Opening" = @PRIOR("Closing");	Dense time keeps it in-block
@NEXT	Range / financial	@NEXT(mbrName [, n, XrangeList])	Value n members forward in the range	@NEXT("Sales", 1)	As @PRIOR
@SHIFT	Range / financial	@SHIFT(mbrName [, n, XrangeList])	Value shifted n positions (negative goes back)	@SHIFT("Sales", -12, @LEVMBRS("Period", 0))	Range spanning sparse years crosses blocks
@MDSHIFT	Range / financial	@MDSHIFT(mbrName, shiftCnt1, dimName1, [range1], ...)	Value shifted along several dimensions	@MDSHIFT("Closing", -1, "Period", , -1, "Years", )	Use for year-crossing opening balances
@SUMRANGE	Range / financial	@SUMRANGE(mbrName [, XrangeList])	Sum of mbrName across the range	@SUMRANGE("Sales", @CHILDREN("Q1"))	Range on dense dims is in-block
@AVGRANGE	Range / financial	@AVGRANGE(SKIPNONE \| SKIPMISSING \| SKIPZERO \| SKIPBOTH, mbrName [, XrangeList])	Average across the range	@AVGRANGE(SKIPMISSING, "Headcount", @CHILDREN("Q1"))	As @SUMRANGE
@CURRMBR	Member set	@CURRMBR(dimName)	Current member of the dimension	@PARENT(@CURRMBR("Entity"))	Not allowed in FIX or on the left side of an equation
@PARENT	Member set	@PARENT(dimName [, mbrName])	Parent of the current member	@PARENT("Entity")	Cheap
@CHILDREN	Member set	@CHILDREN(mbrName)	Children of the member	FIX (@CHILDREN("East"))	Good in FIX
@ICHILDREN	Member set	@ICHILDREN(mbrName)	The member and its children	FIX (@ICHILDREN("East"))	Good in FIX
@DESCENDANTS	Member set	@DESCENDANTS(mbrName [, genLevNum \| genLevName])	All descendants, optionally down to a gen/level	FIX (@DESCENDANTS("Total Entity"))	Large sets in FIX are fine; avoid in IF
@IDESCENDANTS	Member set	@IDESCENDANTS(mbrName [, genLevNum \| genLevName])	The member and all descendants	FIX (@IDESCENDANTS("Total Entity"))	As @DESCENDANTS
@LEVMBRS	Member set	@LEVMBRS(dimName, levName \| levNum)	All members at a level	FIX (@LEVMBRS("Entity", 0))	Standard level-0 FIX
@GENMBRS	Member set	@GENMBRS(dimName, genName \| genNum)	All members at a generation	FIX (@GENMBRS("Product", 2))	Cheap
@RELATIVE	Member set	@RELATIVE(mbrName, genLevNum \| genLevName)	Members at a gen/level under mbrName	FIX (@RELATIVE("Total Entity", 0))	Level-0 under one branch
@UDA	Member set	@UDA(dimName, uda)	Members tagged with the UDA	FIX (@UDA("Entity", "Plan"))	Good way to drive FIX from metadata
@REMOVE	Member set	@REMOVE(list1, list2)	list1 without the members of list2	@REMOVE(@LEVMBRS("Entity", 0), @UDA("Entity", "Closed"))	Cheap
@LIST	Member set	@LIST(argument1, argument2, ..., argumentN)	Arguments treated as one list argument	@LIST("Jan", "Feb", "Mar")	Cheap
@RANGE	Member set	@RANGE(mbrName [, rangeList])	Member combined with each member of the range	@RANGE("Sales", @CHILDREN("Q1"))	Used inside range functions
@XRANGE	Member set	@XRANGE(mbrName1, mbrName2)	Range between two members, including cross-dimension combos	@XRANGE("FY25"->"Jul", "FY26"->"Jun")	Use for fiscal ranges across years
@MEMBER	Member set	@MEMBER(String)	Member whose name matches the string	@MEMBER(@CONCATENATE("E_", @NAME(@CURRMBR("Entity"))))	String functions add per-cell cost
@CONCATENATE	Member set (string)	@CONCATENATE(String1, String2)	Joined string	@CONCATENATE("FY", "26")	Use with @MEMBER
@NAME	Member set (string)	@NAME(mbrName [, UNIQUE])	Member name as a string	@NAME(@CURRMBR("Entity"))	UNIQUE for duplicate-name outlines
@ALLOCATE	Allocation	@ALLOCATE(amount, allocationRange, basisMbr, [roundMbr], method [, methodParams] [, round [, numDigits][, roundErr]])	Allocated value per member of the range	@ALLOCATE("Total Rent"->"Corp", @LEVMBRS("Entity", 0), "Sq Ft", , share)	Run inside a tight FIX
@MDALLOCATE	Allocation	@MDALLOCATE(amount, Ndim, allocationRange1 ... allocationRangeN, basisMbr, [roundMbr], method [, methodParams] [, round [, numDigits][, roundErr]])	Allocation across several dimensions	@MDALLOCATE("IT Cost"->"Corp", 2, @LEVMBRS("Entity", 0), @LEVMBRS("Product", 0), "Revenue", , share)	Heavy; can create many blocks
@CREATEBLOCK	Block creation	@CREATEBLOCK(mbrName \| mbrList)	Creates blocks for sparse combinations	@CREATEBLOCK("Budget");	Use instead of SET CREATENONMISSINGBLK where possible
@ROUND	Math	@ROUND(expression, numDigits [, compatibility])	Rounded value	@ROUND("Rate" * "Sales", 2)	Cheap; compatibility argument is in newer releases
@ABS	Math	@ABS(expression)	Absolute value	@ABS("Variance")	Cheap
@VAR	Math / financial	@VAR(mbrName1, mbrName2)	Variance, respecting expense reporting tags	"Variance" = @VAR("Actual", "Budget");	Cheap
@VARPER	Math / financial	@VARPER(mbrName1, mbrName2)	Percent variance, respecting expense tags	"Var %" = @VARPER("Actual", "Budget");	Cheap
@TODATE	Date	@TODATE(formatString, dateString)	Date as a number for comparisons	@TODATE("mm-dd-yyyy", "10-01-2026")	Format must be mm-dd-yyyy or dd-mm-yyyy
@XREF	Cross-cube	@XREF(locationAlias [, mbrList])	Value from another database via location alias	"Rate" = @XREF(RatesDB, "Avg Rate");	Forces serial calc (1012571); use sparingly
@XWRITE	Cross-cube	@XWRITE(expression, locationAlias [, mbrList])	Writes a value to another database	@XWRITE("Sales", ReportDB, "Sales");	Remote writes; batch inside a tight FIX
@RETURN	Control	@RETURN("ErrorMessage" [, INFO \| ERROR \| WARNING])	Stops the calc and returns a message	IF ("Rate" == #Missing) @RETURN("Rate missing", ERROR); ENDIF	Use for validation in business rules
@CALCMODE	Control	@CALCMODE(CELL \| BLOCK \| TOPDOWN \| BOTTOMUP)	Sets calculation mode for the formula	@CALCMODE(BOTTOMUP);	BOTTOMUP visits only existing blocks; TOPDOWN evaluates every potential block and is slower

Every function name and syntax line above is checked against the Calculation Functions section of the Essbase Technical Reference (21c, consistent with 11.1.2.4). These are block storage (BSO) functions used in calc scripts, BSO member formulas and Planning business rules; ASO outlines use MDX formulas instead. Member names in the examples are illustrative.

Have a business rule nobody understands?

Send it to us and we will return a line-by-line explanation and any performance problems we see.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search by function name or by job: type allocation, range, UDA or level and the matching rows appear. Copy the syntax line into your script and replace the member names. The category tells you where the function belongs: Boolean functions go inside IF conditions, member set functions go inside FIX or as arguments to other functions, range functions read across a list of members such as months, and allocation functions spread a value over a basis. Export the table to Excel if you want to add the custom-defined functions your team uses.

Boolean: @ISMBR, @ISLEV, @ISGEN, @ISCHILD, @ISDESC, @ISUDA.
Member set: @CHILDREN, @DESCENDANTS, @LEVMBRS, @RELATIVE, @UDA, @REMOVE.
Range and financial: @PRIOR, @NEXT, @SHIFT, @MDSHIFT, @SUMRANGE, @AVGRANGE, @VAR.

Top-down, bottom-up and block creation

By default Essbase calculates formulas bottom-up: it only visits blocks that already exist, which is fast. Some functions, such as cross-dimensional references to members that may not have blocks yet, make Essbase switch to top-down for that formula, which visits every potential block and is much slower. @CALCMODE(BOTTOMUP) forces bottom-up when you know the target blocks exist, and SET FRMLBOTTOMUP ON does the same for a whole script. When the target blocks do not exist (for example seeding a new Budget from Actual), create them deliberately with @CREATEBLOCK or DATACOPY rather than turning on SET CREATENONMISSINGBLK, which can create blocks for every combination.

Worked example

A rent allocation: FIX on the Budget scenario, the current year and @LEVMBRS("Entity", 0), then "Rent Alloc" = @ALLOCATE("Total Rent"->"Corp", @LEVMBRS("Entity", 0), "Sq Ft", , share);. The amount is a cross-dimensional reference to Corp, the range is level-0 entities and the basis is square feet, so each entity gets rent in proportion to its share of the total. Add @RETURN with ERROR if Sq Ft is missing for the whole range so the business rule stops with a clear message instead of writing zeros.

Common mistakes

Using IF with @ISMBR on sparse members instead of FIX forces Essbase to touch every block. @CURRMBR is not allowed inside FIX or on the left side of an assignment. @PRIOR and @SHIFT default to the Time dimension range; across years in a separate Years dimension you need @MDSHIFT or @XRANGE. @XREF and @XWRITE force serial calculation and depend on a working location alias. @TODATE only accepts mm-dd-yyyy or dd-mm-yyyy in lower case. Member formulas on dense members recalculate in every block, so keep them simple or make them Dynamic Calc.

Frequently Asked Questions

What are the most used Essbase calculation functions?

In real calc scripts and business rules the most common are @LEVMBRS, @RELATIVE, @CHILDREN and @UDA inside FIX, @ISMBR and @ISLEV in IF conditions, @PRIOR for opening balances, @SUMRANGE for year-to-date style totals, @ALLOCATE for cost spreads and @CREATEBLOCK when seeding new versions or scenarios.

Can I use these functions in Hyperion Planning business rules?

Yes. Business rules in Calculation Manager generate Essbase calc script code for block storage plan types, so every BSO @ function works there. Runtime prompts are substituted before the script runs. ASO plan types use a different engine, so these functions do not apply to ASO cubes.

What does @CALCMODE do?

@CALCMODE changes how Essbase evaluates the formula it appears in. CELL and BLOCK control whether the formula is evaluated cell by cell or for a whole block, and TOPDOWN and BOTTOMUP control whether Essbase visits all potential blocks or only existing ones. Use BOTTOMUP when target blocks already exist to avoid slow top-down passes.

Is there a calc function for previous year values?

If years are part of the Time dimension, @PRIOR("Sales", 12) works. If years are a separate sparse dimension, use @MDSHIFT to shift along Years, or a cross-dimensional reference such as "Sales"->"FY25". @SHIFT with a negative count is equivalent to @PRIOR on the same range.

If you want an on-prem assistant that explains your Essbase business rules and member formulas to finance users in plain English, we can set that up on your own servers.

Talk to an Expert Explore More Free Tools