Member Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
Member functions return a single member. They navigate within dimension hierarchies and are commonly used inside calculated member expressions.
CurrentMember
Description: Returns the current member of a dimension or hierarchy during iteration over a set.
Syntax:
Dimension_Expression.CurrentMember
Hierarchy_Expression.CurrentMember
Input: A dimension or hierarchy reference
Output: The member currently in context during set iteration
Business Use Case: Use the currently iterated month to compute month-specific calculations.
WITH MEMBER [Measures].[Month Label] AS [Date].[Calendar].CurrentMember.Name
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Month Label]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Item
Description: Returns a specific member or tuple from a set by its zero-based index position.
Syntax:
Set_Expression.Item(Index) — returns the member/tuple at position Index
Set_Expression.Item(String_Expression)— returns by unique name
Input: A set and an index (integer) or string identifier
Output: The member or tuple at that position
Business Use Case: Retrieve the first or specific member from a ranked set for use in calculations.
WITH MEMBER [Measures].[Top Product Sales] AS (TopCount(
[Product].[Product Categories].[Product].Members, 1,
[Measures].[Internet Sales Amount]).Item(0), [Measures].[Internet Sales Amount])
SELECT
[Measures].[Top Product Sales] ON COLUMNS
FROM [Adventure Works]Variation — Item(1) for second member:
WITH MEMBER [Measures].[Second Category Sales] AS ([Product].[Product Categories].[Category].Members.Item(1),
[Measures].[Internet Sales Amount]) SELECT
[Measures].[Second Category Sales] ON COLUMNS
FROM [Adventure Works]ParallelPeriod
Description: Returns a member in a prior period at the same relative position within a higher-level period — used for period-over-period comparisons.
Syntax: ParallelPeriod([Level_Expression [, Lag_Number [, Member_Expression]]])
Input: Optional level (default: year), optional lag count (default: 1), optional member (default: current)
Output: Member at the parallel position in the previous period
Business Use Case: Compare current month sales to the same month last year.
WITH MEMBER [Measures].[Same Month Last Year] AS ([Measures].[Internet Sales Amount],
ParallelPeriod(
[Date].[Calendar].[Calendar Year], 1,
[Date].[Calendar].CurrentMember)) MEMBER [Measures].[YoY Growth] AS
IIF([Measures].[Same Month Last Year] = 0, NULL,
([Measures].[Internet Sales Amount] - [Measures].[Same Month Last Year])
/ [Measures].[Same Month Last Year])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Same Month Last Year], [Measures].[YoY Growth]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Variation — quarter-over-quarter:
WITH MEMBER [Measures].[Prior Quarter Sales] AS ([Measures].[Internet Sales Amount],
ParallelPeriod( [Date].[Calendar].[Calendar Year], 1,
[Date].[Calendar].CurrentMember))
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Prior Quarter Sales]} ON COLUMNS,
[Date].[Calendar].[Calendar Quarter].Members ON ROWS
FROM [Adventure Works]StrToMember
Description: Converts a string expression to an MDX member reference. Useful for dynamic member selection.
Syntax: StrToMember(String_Expression)
Input: A string representing a valid MDX member unique name
Output: The corresponding member object
Business Use Case: Dynamically reference a dimension member from a string parameter or variable.
WITH MEMBER [Measures].[Dynamic Member Sales] AS (StrToMember("[Product].[Product Categories].[Category].[Bikes]"),
[Measures].[Internet Sales Amount]) SELECT
[Measures].[Dynamic Member Sales] ON COLUMNS
FROM [Adventure Works]FirstChild
Description: Returns the first child of a member.
Syntax: Member_Expression.FirstChild
Input: A member expression
Output: The first child member
Business Use Case: Get the first month of a quarter for range calculations.
WITH MEMBER [Measures].[First Month Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2012].FirstChild) SELECT
[Measures].[First Month Sales] ON COLUMNS, [Date].[Calendar].[Calendar Quarter].Members ON ROWS
FROM [Adventure Works]LastChild
Description: Returns the last child of a member.
Syntax: Member_Expression.LastChild
Input: A member expression
Output: The last child member
Business Use Case: Find the most recent month in a quarter for end-of-period reporting.
WITH MEMBER [Measures].[Last Month in Quarter Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.LastChild) SELECT
{[Measures].[Internet Sales Amount],
[Measures].[Last Month in Quarter Sales]} ON COLUMNS, [Date].[Calendar].[Calendar Quarter].Members ON ROWS
FROM [Adventure Works]FirstSibling
Description: Returns the first sibling of a member (the first member at the same level under the same parent).
Syntax: Member_Expression.FirstSibling
Business Use Case: Navigate to the first month of the current month's quarter.
WITH MEMBER [Measures].[Quarter Start Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.FirstSibling) SELECT
{[Measures].[Internet Sales Amount], [Measures].[Quarter Start Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]LastSibling
Description: Returns the last sibling of a member.
Syntax: Member_Expression.LastSibling
Business Use Case: Reference the last month of the current quarter for period-end calculations.
WITH MEMBER [Measures].[Quarter End Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.LastSibling)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Quarter End Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Ancestor
Description: Returns the ancestor of a member at a specified level or distance up the hierarchy.
Syntax:
Ancestor(Member_Expression, Level_Expression)
Ancestor(Member_Expression, Distance)
Input: A member, and either a level expression or integer distance
Output: The ancestor member at that level/distance
Business Use Case: For each month in context, reference the parent year for year-level calculations.
WITH MEMBER [Measures].[Year Sales] AS ([Measures].[Internet Sales Amount],
Ancestor([Date].[Calendar].CurrentMember, [Date].[Calendar].[Calendar Year]))
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Year Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]DefaultMember
Description: Returns the default member of a dimension or hierarchy — typically the "All" member.
Syntax:
Dimension_Expression.DefaultMember
Hierarchy_Expression.DefaultMember
Business Use Case: Reference the "All Products" total in a calculation to compute percentage of total.
WITH MEMBER [Measures].[Pct of Total Products] AS [Measures].[Internet Sales Amount]
/ ([Measures].[Internet Sales Amount],
[Product].[Product Categories].DefaultMember)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Pct of Total Products]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]Lag
Description: Returns the member N positions before the current member along the same level.
Syntax: Member_Expression.Lag(Index)
Input: A member and a positive integer lag
Output: Member at N positions before
Business Use Case: Compare current month to the month 3 periods ago (seasonal shift comparison).
WITH MEMBER [Measures].[3 Month Ago Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.Lag(3)) SELECT
{[Measures].[Internet Sales Amount],
[Measures].[3 Month Ago Sales]} ON COLUMNS, [Date].[Calendar].[Month].Members ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Lead
Description: Returns the member N positions after the current member along the same level.
Syntax: Member_Expression.Lead(Index)
Input: A member and a positive integer lead
Output: Member at N positions after
Business Use Case: Show next quarter's forecast alongside current quarter actuals.
WITH MEMBER [Measures].[Next Quarter Forecast] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.Lead(1)) SELECT
{[Measures].[Internet Sales Amount], [Measures].[Next Quarter Forecast]} ON COLUMNS,
[Date].[Calendar].[Calendar Quarter].Members ON ROWS FROM [Adventure Works]NextMember
Description: Returns the immediately next member at the same level.
Syntax: Member_Expression.NextMember
Input: A member expression
Output: The next member in the level order
Business Use Case: Reference the next month in a sequence for forecasting or trend arrows.
WITH MEMBER [Measures].[Next Month Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.NextMember) SELECT
{[Measures].[Internet Sales Amount], [Measures].[Next Month Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]PreviousMember / PrevMember
Description: Returns the immediately previous member at the same level. PrevMember is an alias.
Syntax:
Member_Expression.PreviousMember
Member_Expression.PrevMember
Input: A member expression
Output: The previous member in the level order
Business Use Case: Calculate month-over-month change.
WITH MEMBER [Measures].[Prior Month Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.PrevMember)
MEMBER [Measures].[MoM Change] AS IIF([Measures].[Prior Month Sales] = 0, NULL,
[Measures].[Internet Sales Amount] - [Measures].[Prior Month Sales])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Prior Month Sales], [Measures].[MoM Change]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]FirstMember
Description: Returns the first member at the current level within the current hierarchy context.
Syntax: Hierarchy_Expression.FirstMember
Business Use Case: Reference the earliest date in the calendar for range calculations.
WITH MEMBER [Measures].[First Year Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].[Calendar Year].FirstMember) SELECT
{[Measures].[Internet Sales Amount],
[Measures].[First Year Sales]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].Members ON ROWS FROM [Adventure Works]LastMember
Description: Returns the last member at the current level within the current hierarchy context.
Syntax: Hierarchy_Expression.LastMember
Business Use Case: Reference the most recent year for latest-period comparisons.
WITH MEMBER [Measures].[Latest Year Sales] AS ([Measures].[Internet Sales Amount],
[Date].[Calendar].[Calendar Year].LastMember) SELECT
{[Measures].[Internet Sales Amount], [Measures].[Latest Year Sales]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].Members ON ROWS FROM [Adventure Works]IsLevel
Description: Returns TRUE if a member is at a specified level.
Syntax: Member_Expression.IsLevel(Level_Expression)
Input: A member and a level expression
Output: Boolean
Business Use Case: Apply different calculations at different hierarchy levels.
WITH MEMBER [Measures].[Level-Based Label] AS CASE
WHEN [Product].[Product Categories].CurrentMember.IsLevel( [Product].[Product Categories].[Category]) THEN "Category"
WHEN [Product].[Product Categories].CurrentMember.IsLevel( [Product].[Product Categories].[Subcategory]) THEN "Subcategory"
ELSE "Product"
END SELECT
{[Measures].[Internet Sales Amount], [Measures].[Level-Based Label]} ON COLUMNS,
Hierarchize(
{[Product].[Product Categories].[Category].Members, [Product].[Product Categories].[Subcategory].Members}) ON ROWS
FROM [Adventure Works]
Level.Name
Description: Returns the name of the level containing a member.
Syntax: Member_Expression.Level.Name
Business Use Case: Display the hierarchy level name alongside each row for context.
WITH MEMBER [Measures].[Member Level] AS
[Product].[Product Categories].CurrentMember.Level.Name SELECT
{[Measures].[Internet Sales Amount], [Measures].[Member Level]} ON COLUMNS,
Hierarchize(
{[Product].[Product Categories].[Category].Members, [Product].[Product Categories].[Subcategory].Members}) ON ROWS
FROM [Adventure Works]Level.UniqueName
Description: Returns the unique name of a level.
Syntax: Level_Expression.UniqueName
Business Use Case: Log or debug level identifiers in dynamic queries.
WITH MEMBER [Measures].[Level Unique Name] AS
[Product].[Product Categories].CurrentMember.Level.UniqueName SELECT
{[Measures].[Internet Sales Amount], [Measures].[Level Unique Name]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]Member.Level
Description: Returns the level object that a member belongs to.
Syntax: Member_Expression.Level
Business Use Case: Compare members' levels programmatically.
WITH MEMBER [Measures].[Is Category Level] AS IIF([Product].[Product Categories].CurrentMember.Level
IS [Product].[Product Categories].[Category], 1, 0)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Is Category Level]} ON COLUMNS,
Hierarchize(
{[Product].[Product Categories].[Category].Members, [Product].[Product Categories].[Subcategory].Members}) ON ROWS
FROM [Adventure Works]Member.Dimension / Level.Dimension
Description: Returns the dimension that a member or level belongs to.
Syntax:
Member_Expression.Dimension
Level_Expression.Dimension
Business Use Case: Dynamically identify which dimension a member belongs to.
WITH MEMBER [Measures].[Dimension Name] AS
[Product].[Product Categories].CurrentMember.Dimension.Name SELECT
{[Measures].[Internet Sales Amount], [Measures].[Dimension Name]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]LinkMember
Description: Returns the equivalent member in a different hierarchy based on the same key value.
Syntax: LinkMember(Member_Expression, Hierarchy_Expression)
Input: A member from one hierarchy and the target hierarchy
Output: The corresponding member in the target hierarchy
Business Use Case: Map a member from the Calendar hierarchy to its equivalent in the Fiscal hierarchy.
WITH MEMBER [Measures].[Fiscal Sales] AS ([Measures].[Internet Sales Amount],
LinkMember(
[Date].[Calendar].CurrentMember, [Date].[Fiscal]))
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Fiscal Sales]} ON COLUMNS,
[Date].[Calendar].[Calendar Quarter].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]