# Essbase Calculation Functions List with Syntax and Examples

> Source: https://netray.co/tools/essbase-calculation-functions-reference
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

A searchable reference of the Essbase block storage @ functions used in calc scripts, member formulas and business rules, with syntax, return type, example and performance note.

## Category

oracle

## Erp

Oracle Hyperion / Essbase

## Audience

- Essbase administrator
- Hyperion Planning developer
- EPM consultant

## Tool

**Kind:** worksheet

**Mode:** reference

**Columns:**

#### Function

**Key:** fn

**Type:** text

#### Category

**Key:** category

**Type:** text

#### Syntax

**Key:** syntax

**Type:** text

#### Returns

**Key:** returns

**Type:** text

#### Example

**Key:** example

**Type:** text

#### Performance note

**Key:** perf

**Type:** text

**Rows:**

**Fn:** @ISMBR

**Category:** Boolean

**Syntax:** @ISMBR(mbrName | rangeList | mbrList)

**Returns:** TRUE if the current member matches

**Example:** IF (@ISMBR("Budget")) ... ENDIF

**Perf:** Cheap; prefer FIX over IF on sparse members

**Fn:** @ISLEV

**Category:** Boolean

**Syntax:** @ISLEV(dimName, levName | levNum)

**Returns:** TRUE if current member is at that level

**Example:** IF (@ISLEV("Entity", 0))

**Perf:** Cheap

**Fn:** @ISGEN

**Category:** Boolean

**Syntax:** @ISGEN(dimName, genName | genNum)

**Returns:** TRUE if current member is at that generation

**Example:** IF (@ISGEN("Period", 3))

**Perf:** Cheap

**Fn:** @ISCHILD

**Category:** Boolean

**Syntax:** @ISCHILD(mbrName)

**Returns:** TRUE if current member is a child of mbrName

**Example:** IF (@ISCHILD("Q1"))

**Perf:** Cheap

**Fn:** @ISDESC

**Category:** Boolean

**Syntax:** @ISDESC(mbrName)

**Returns:** TRUE if current member is a descendant of mbrName

**Example:** IF (@ISDESC("Total Opex"))

**Perf:** Cheap

**Fn:** @ISUDA

**Category:** Boolean

**Syntax:** @ISUDA(dimName, UDAStr)

**Returns:** TRUE if current member has the UDA

**Example:** IF (@ISUDA("Entity", "USD"))

**Perf:** Cheap; @UDA in FIX is better for sparse

**Fn:** @PARENTVAL

**Category:** Relationship

**Syntax:** @PARENTVAL(dimName [, mbrName])

**Returns:** Value of the parent of the current member

**Example:** "Share" = "Sales" / @PARENTVAL("Product", "Sales");

**Perf:** Reads another block when the dimension is sparse

**Fn:** @ANCESTVAL

**Category:** Relationship

**Syntax:** @ANCESTVAL(dimName, genLevNum [, mbrName])

**Returns:** Value of the ancestor at a generation or level

**Example:** @ANCESTVAL("Product", 2, "Sales")

**Perf:** Cross-block reads on sparse dims

**Fn:** @MDANCESTVAL

**Category:** Relationship

**Syntax:** @MDANCESTVAL(dimCount, dimName1, genLevNum1, ... [, mbrName])

**Returns:** Ancestor value across several dimensions

**Example:** @MDANCESTVAL(2, "Market", 2, "Product", 2, "Sales")

**Perf:** Heavier; use for allocation bases

**Fn:** @PRIOR

**Category:** Range / financial

**Syntax:** @PRIOR(mbrName [, n, XrangeList])

**Returns:** Value n members back in the range (default Time dimension)

**Example:** "Opening" = @PRIOR("Closing");

**Perf:** Dense time keeps it in-block

**Fn:** @NEXT

**Category:** Range / financial

**Syntax:** @NEXT(mbrName [, n, XrangeList])

**Returns:** Value n members forward in the range

**Example:** @NEXT("Sales", 1)

**Perf:** As @PRIOR

**Fn:** @SHIFT

**Category:** Range / financial

**Syntax:** @SHIFT(mbrName [, n, XrangeList])

**Returns:** Value shifted n positions (negative goes back)

**Example:** @SHIFT("Sales", -12, @LEVMBRS("Period", 0))

**Perf:** Range spanning sparse years crosses blocks

**Fn:** @MDSHIFT

**Category:** Range / financial

**Syntax:** @MDSHIFT(mbrName, shiftCnt1, dimName1, [range1], ...)

**Returns:** Value shifted along several dimensions

**Example:** @MDSHIFT("Closing", -1, "Period", , -1, "Years", )

**Perf:** Use for year-crossing opening balances

**Fn:** @SUMRANGE

**Category:** Range / financial

**Syntax:** @SUMRANGE(mbrName [, XrangeList])

**Returns:** Sum of mbrName across the range

**Example:** @SUMRANGE("Sales", @CHILDREN("Q1"))

**Perf:** Range on dense dims is in-block

**Fn:** @AVGRANGE

**Category:** Range / financial

**Syntax:** @AVGRANGE(SKIPNONE | SKIPMISSING | SKIPZERO | SKIPBOTH, mbrName [, XrangeList])

**Returns:** Average across the range

**Example:** @AVGRANGE(SKIPMISSING, "Headcount", @CHILDREN("Q1"))

**Perf:** As @SUMRANGE

**Fn:** @CURRMBR

**Category:** Member set

**Syntax:** @CURRMBR(dimName)

**Returns:** Current member of the dimension

**Example:** @PARENT(@CURRMBR("Entity"))

**Perf:** Not allowed in FIX or on the left side of an equation

**Fn:** @PARENT

**Category:** Member set

**Syntax:** @PARENT(dimName [, mbrName])

**Returns:** Parent of the current member

**Example:** @PARENT("Entity")

**Perf:** Cheap

**Fn:** @CHILDREN

**Category:** Member set

**Syntax:** @CHILDREN(mbrName)

**Returns:** Children of the member

**Example:** FIX (@CHILDREN("East"))

**Perf:** Good in FIX

**Fn:** @ICHILDREN

**Category:** Member set

**Syntax:** @ICHILDREN(mbrName)

**Returns:** The member and its children

**Example:** FIX (@ICHILDREN("East"))

**Perf:** Good in FIX

**Fn:** @DESCENDANTS

**Category:** Member set

**Syntax:** @DESCENDANTS(mbrName [, genLevNum | genLevName])

**Returns:** All descendants, optionally down to a gen/level

**Example:** FIX (@DESCENDANTS("Total Entity"))

**Perf:** Large sets in FIX are fine; avoid in IF

**Fn:** @IDESCENDANTS

**Category:** Member set

**Syntax:** @IDESCENDANTS(mbrName [, genLevNum | genLevName])

**Returns:** The member and all descendants

**Example:** FIX (@IDESCENDANTS("Total Entity"))

**Perf:** As @DESCENDANTS

**Fn:** @LEVMBRS

**Category:** Member set

**Syntax:** @LEVMBRS(dimName, levName | levNum)

**Returns:** All members at a level

**Example:** FIX (@LEVMBRS("Entity", 0))

**Perf:** Standard level-0 FIX

**Fn:** @GENMBRS

**Category:** Member set

**Syntax:** @GENMBRS(dimName, genName | genNum)

**Returns:** All members at a generation

**Example:** FIX (@GENMBRS("Product", 2))

**Perf:** Cheap

**Fn:** @RELATIVE

**Category:** Member set

**Syntax:** @RELATIVE(mbrName, genLevNum | genLevName)

**Returns:** Members at a gen/level under mbrName

**Example:** FIX (@RELATIVE("Total Entity", 0))

**Perf:** Level-0 under one branch

**Fn:** @UDA

**Category:** Member set

**Syntax:** @UDA(dimName, uda)

**Returns:** Members tagged with the UDA

**Example:** FIX (@UDA("Entity", "Plan"))

**Perf:** Good way to drive FIX from metadata

**Fn:** @REMOVE

**Category:** Member set

**Syntax:** @REMOVE(list1, list2)

**Returns:** list1 without the members of list2

**Example:** @REMOVE(@LEVMBRS("Entity", 0), @UDA("Entity", "Closed"))

**Perf:** Cheap

**Fn:** @LIST

**Category:** Member set

**Syntax:** @LIST(argument1, argument2, ..., argumentN)

**Returns:** Arguments treated as one list argument

**Example:** @LIST("Jan", "Feb", "Mar")

**Perf:** Cheap

**Fn:** @RANGE

**Category:** Member set

**Syntax:** @RANGE(mbrName [, rangeList])

**Returns:** Member combined with each member of the range

**Example:** @RANGE("Sales", @CHILDREN("Q1"))

**Perf:** Used inside range functions

**Fn:** @XRANGE

**Category:** Member set

**Syntax:** @XRANGE(mbrName1, mbrName2)

**Returns:** Range between two members, including cross-dimension combos

**Example:** @XRANGE("FY25"->"Jul", "FY26"->"Jun")

**Perf:** Use for fiscal ranges across years

**Fn:** @MEMBER

**Category:** Member set

**Syntax:** @MEMBER(String)

**Returns:** Member whose name matches the string

**Example:** @MEMBER(@CONCATENATE("E_", @NAME(@CURRMBR("Entity"))))

**Perf:** String functions add per-cell cost

**Fn:** @CONCATENATE

**Category:** Member set (string)

**Syntax:** @CONCATENATE(String1, String2)

**Returns:** Joined string

**Example:** @CONCATENATE("FY", "26")

**Perf:** Use with @MEMBER

**Fn:** @NAME

**Category:** Member set (string)

**Syntax:** @NAME(mbrName [, UNIQUE])

**Returns:** Member name as a string

**Example:** @NAME(@CURRMBR("Entity"))

**Perf:** UNIQUE for duplicate-name outlines

**Fn:** @ALLOCATE

**Category:** Allocation

**Syntax:** @ALLOCATE(amount, allocationRange, basisMbr, [roundMbr], method [, methodParams] [, round [, numDigits][, roundErr]])

**Returns:** Allocated value per member of the range

**Example:** @ALLOCATE("Total Rent"->"Corp", @LEVMBRS("Entity", 0), "Sq Ft", , share)

**Perf:** Run inside a tight FIX

**Fn:** @MDALLOCATE

**Category:** Allocation

**Syntax:** @MDALLOCATE(amount, Ndim, allocationRange1 ... allocationRangeN, basisMbr, [roundMbr], method [, methodParams] [, round [, numDigits][, roundErr]])

**Returns:** Allocation across several dimensions

**Example:** @MDALLOCATE("IT Cost"->"Corp", 2, @LEVMBRS("Entity", 0), @LEVMBRS("Product", 0), "Revenue", , share)

**Perf:** Heavy; can create many blocks

**Fn:** @CREATEBLOCK

**Category:** Block creation

**Syntax:** @CREATEBLOCK(mbrName | mbrList)

**Returns:** Creates blocks for sparse combinations

**Example:** @CREATEBLOCK("Budget");

**Perf:** Use instead of SET CREATENONMISSINGBLK where possible

**Fn:** @ROUND

**Category:** Math

**Syntax:** @ROUND(expression, numDigits [, compatibility])

**Returns:** Rounded value

**Example:** @ROUND("Rate" * "Sales", 2)

**Perf:** Cheap; compatibility argument is in newer releases

**Fn:** @ABS

**Category:** Math

**Syntax:** @ABS(expression)

**Returns:** Absolute value

**Example:** @ABS("Variance")

**Perf:** Cheap

**Fn:** @VAR

**Category:** Math / financial

**Syntax:** @VAR(mbrName1, mbrName2)

**Returns:** Variance, respecting expense reporting tags

**Example:** "Variance" = @VAR("Actual", "Budget");

**Perf:** Cheap

**Fn:** @VARPER

**Category:** Math / financial

**Syntax:** @VARPER(mbrName1, mbrName2)

**Returns:** Percent variance, respecting expense tags

**Example:** "Var %" = @VARPER("Actual", "Budget");

**Perf:** Cheap

**Fn:** @TODATE

**Category:** Date

**Syntax:** @TODATE(formatString, dateString)

**Returns:** Date as a number for comparisons

**Example:** @TODATE("mm-dd-yyyy", "10-01-2026")

**Perf:** Format must be mm-dd-yyyy or dd-mm-yyyy

**Fn:** @XREF

**Category:** Cross-cube

**Syntax:** @XREF(locationAlias [, mbrList])

**Returns:** Value from another database via location alias

**Example:** "Rate" = @XREF(RatesDB, "Avg Rate");

**Perf:** Forces serial calc (1012571); use sparingly

**Fn:** @XWRITE

**Category:** Cross-cube

**Syntax:** @XWRITE(expression, locationAlias [, mbrList])

**Returns:** Writes a value to another database

**Example:** @XWRITE("Sales", ReportDB, "Sales");

**Perf:** Remote writes; batch inside a tight FIX

**Fn:** @RETURN

**Category:** Control

**Syntax:** @RETURN("ErrorMessage" [, INFO | ERROR | WARNING])

**Returns:** Stops the calc and returns a message

**Example:** IF ("Rate" == #Missing) @RETURN("Rate missing", ERROR); ENDIF

**Perf:** Use for validation in business rules

**Fn:** @CALCMODE

**Category:** Control

**Syntax:** @CALCMODE(CELL | BLOCK | TOPDOWN | BOTTOMUP)

**Returns:** Sets calculation mode for the formula

**Example:** @CALCMODE(BOTTOMUP);

**Perf:** BOTTOMUP visits only existing blocks; TOPDOWN evaluates every potential block and is slower

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### What are the most used Essbase calculation functions?

**Answer:** 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?

**Answer:** 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?

**Answer:** @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?

**Answer:** 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.

## Search terms

essbase calculation function list, essbase calc functions, essbase functions, hyperion business rules functions, essbase formulas, essbase calculation functions in essbase, essbase @prior example, essbase @ismbr

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json