Essbase MDX Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
These functions originate from Essbase (Oracle Hyperion) MDX and are supported in Kyvos for compatibility.
Generation
Description: Returns the set of members from a specified generation number within a hierarchy. Generation 1 = root (All), increasing numbers = deeper levels.
Syntax: Generation(Hierarchy_Expression, Generation_Number)
Input: A hierarchy and integer generation number
Output: Set of all members at that generation
Business Use Case: Get all members at the subcategory level (generation 3) without knowing the level name.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Generation([Product].[Product Categories], 3) ON ROWS
FROM [Adventure Works]NonemptyCount
Description: Returns the count of non-empty cells in a set relative to a measure.
Syntax: NonemptyCount(Set_Expression [, Measure])
Input: A set and optional measure
Output: Integer count of non-empty members
Business Use Case: Count how many products have at least one sale.
WITH MEMBER [Measures].[Products With Sales] AS NonemptyCount(
[Product].[Product].[Product].Members, [Measures].[Internet Sales Amount])
SELECT
[Measures].[Products With Sales] ON COLUMNS FROM [Adventure Works]ClosingPeriod
Description: Returns the last sibling among the descendants of a member at a specified level — effectively the last period within a parent period.
Syntax: ClosingPeriod([Level_Expression [, Member_Expression]])
Input: Optional level and optional member
Output: The closing (last) period member
Business Use Case: Find the last month of a given year for end-of-period reporting.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, ClosingPeriod(
[Date].[Calendar].[Month], [Date].[Calendar].[Calendar Year].[2012]) ON ROWS
FROM [Adventure Works]
Variation — last quarter of a year:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, ClosingPeriod(
[Date].[Calendar].[Calendar Quarter],
[Date].[Calendar].[Calendar Year].[2012]) ON ROWS FROM [Adventure Works]LastPeriods
Description: Returns a set of members ending with the specified member and spanning N periods backward. Negative count returns forward periods.
Syntax: LastPeriods(Index [, Member_Expression])
Input: Count of periods, optional end member (default current)
Output: Set of the last N periods ending at the specified member
Business Use Case: Calculate a 6-month rolling average by summing the last 6 months.
WITH MEMBER [Measures].[6 Month Rolling Avg] AS AVG(
LastPeriods(6, [Date].[Calendar].CurrentMember), [Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[6 Month Rolling Avg]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]RelMemberRange
Description: Returns a set of members relative to a specified member, defined by start and end offsets.
Syntax: RelMemberRange(Member_Expression, Start_Offset, End_Offset [, Hierarchy_Flag])
Input: A member, start offset (negative = before), end offset (positive = after), optional hierarchy flag
Output: A set of members spanning the offset range
Business Use Case: Show a ±3 month window around a specific month for trend analysis.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, RelMemberRange(
[Date].[Calendar].[Month].[June 2012],
-3,
3) ON ROWS
FROM [Adventure Works]Existing
Description: Forces evaluation of a set in the current query context rather than the default context. Used to respect slicer filters when computing sets inside calculations.
Syntax: EXISTING Set_Expression
Input: A set expression
Output: The set evaluated within current context
Business Use Case: Ensure a SUM respects the WHERE clause filter rather than ignoring it.
WITH MEMBER [Measures].[Context-Aware Sales] AS SUM(
EXISTING [Product].[Product Categories].[Product].Members,
[Measures].[Internet Sales Amount])
SELECT
[Measures].[Context-Aware Sales] ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]
WHERE [Customer].[Customer Geography].[Country].[United States]GetFilterInfo
Description: Returns information about the filters currently applied to a dimension as a string.
Syntax: GetFilterInfo(Dimension_Expression)
Input: A dimension expression
Output: String describing the current filter applied to that dimension
Business Use Case: Display which year filter is currently active in are port header.
WITH MEMBER [Measures].[Active Date Filter] AS GetFilterInfo([Date])
SELECT
[Measures].[Active Date Filter] ON COLUMNS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]VisualTotals
Description: Calculates subtotals for a set of visible members — parent members reflect only the children shown, not the full hierarchy rollup.
Syntax: VisualTotals(Set_Expression [, Pattern])
Input: A set of members (typically a mix of parents and children), optional name pattern
Output: The same set but with parent totals recalculated based only on visible children
Business Use Case: Show Bikes total that reflects only Mountain Bikes and Road Bikes (not Touring), for selective subset reporting.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, VisualTotals(
{[Product].[Product Categories].[Category].[Bikes], [Product].[Product Categories].[Subcategory].[Mountain Bikes], [Product].[Product Categories].[Subcategory].[Road Bikes]}) ON ROWS
FROM [Adventure Works]