Set Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
Set functions return a set of members. They are used on query axes (ON ROWS, ON COLUMNS) or within calculations that operate over sets (SUM, AVG, Filter, etc.).
AddCalculatedMembers
Description: Adds calculated members that exist in a dimension to an existing set. Syntax:
Input: A set expression Output: The original set with any applicable calculated members added
Business Use Case: Ensure that named calculated measures appear alongside regular measures when
using .Members.
WITH MEMBER [Measures].[Profit Margin] AS
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount] SELECT
AddCalculatedMembers([Measures].Members) ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]AllMembers
Description: Returns all members of a level or hierarchy including calculated members.
Syntax:
Hierarchy_Expression.AllMembers
Level_Expression.AllMembers
Input: A hierarchy or level expression Output: All members (regular + calculated)
Business Use Case: Retrieve every measure including those created via calculated members.
WITH MEMBER [Measures].[Profit Margin] AS
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount] SELECT
[Measures].AllMembers ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]Variation — AllMembers on a level:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [Product].[Product Categories].[Subcategory].AllMembers ON ROWS
FROM [Adventure Works]Ancestors
Description: Returns the set of ancestors of a member up to (and optionally including) a specified level. Syntax:
Ancestors(Member_Expression, Level_Expression)
Ancestors(Member_Expression, Distance)
Input: A member and either a level or numeric distance up the hierarchy Output: A set of ancestor members Business Use Case: Find the product category and subcategory that a specific product belongs to.
-- Get all ancestors of a product (subcategory, category) SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Ancestors(
[Product].[Product Categories].[Product].[Mountain-200 Black, 38], [Product].[Product Categories].[Category]) ON ROWS
FROM [Adventure Works]Variation — by distance:
-- Get parent 1 level up SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Ancestors(
[Product].[Product Categories].[Subcategory].[Mountain Bikes],
1) ON ROWS
FROM [Adventure Works]Ascendants
Description: Returns the full set of ascendants of a member, from the member up to the root of the hierarchy.
Syntax: Ascendants(Member_Expression)
Input: A member expression Output: Set of all ancestors including the member itself
Business Use Case: Show the full rollup path from a leaf product to the top of the hierarchy.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Ascendants(
[Product].[Product Categories].[Product].[Mountain-200 Black, 38]) ON ROWS FROM [Adventure Works]BottomCount
Description: Returns the bottom N members from a set, sorted ascending by a numeric expression.
Syntax: BottomCount(Set_Expression, Count [, Numeric_Expression])
Input: A set, a count integer, optional sort measure Output: The bottom N members sorted ascending
Business Use Case: Identify the lowest-performing product subcategories to flag for review or discontinuation.
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]} ON COLUMNS,
BottomCount(
[Product].[Product Categories].[Subcategory].Members, 5,
[Measures].[Internet Sales Amount]) ON ROWS FROM [Adventure Works]Expected Output: 5 subcategories with the lowest Internet Sales Amount, sorted ascending.
BottomPercent
Description: Returns the smallest subset of a set whose cumulative total is at least the specified percentage of the total.
Syntax: BottomPercent(Set_Expression, Percentage, Numeric_Expression)
Input: A set, a percentage (0-100), a numeric measure Output: Members forming the bottom N% of the total
Business Use Case: Find which products collectively account for the bottom 20% of revenue (the long tail).
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, BottomPercent(
[Product].[Product Categories].[Product].Members, 20,
[Measures].[Internet Sales Amount]) ON ROWS FROM [Adventure Works]BottomSum
Description: Returns the smallest subset of a set whose cumulative sum reaches a specified value.
Syntax: BottomSum(Set_Expression, Value, Numeric_Expression)
Input: A set, target sum value, numeric measure Output: Members whose cumulative sum from the bottom equals the target
Business Use Case: Find the products that together generate less than $500,000 in sales (low-value products).
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, BottomSum(
[Product].[Product Categories].[Product].Members, 500000,
[Measures].[Internet Sales Amount]) ON ROWS FROM [Adventure Works]Children
Description: Returns the set of immediate child members of a given member.
Syntax: Member_Expression.Children
Input: A member expression Output: Set of direct child members
Business Use Case: Drill down from a product category to see all subcategories within it.
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]} ON COLUMNS,
[Product].[Product Categories].[Category].[Bikes].Children ON ROWS FROM [Adventure Works]Expected Output:
| Internet Sales Amount | Internet Order Quantity |
Mountain Bikes | $9,952,759 | 3,399 |
Road Bikes | $14,520,584 | 3,578 |
Touring Bikes | $3,844,801 | 1,013 |
Variation — nested children:
-- Get subcategory children, then their children (products) SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
[Product].[Product Categories].[Subcategory].[Mountain Bikes].Children ON ROWS FROM [Adventure Works]Crossjoin
Description: Returns the cross product of two or more sets — all combinations of members from each set. Syntax:
Crossjoin(Set_Expression1, Set_Expression2)
Set_Expression1 * Set_Expression2
Input: Two or more set expressions Output: Set of all tuples combining one member from each input set Business Use Case: Analyze sales by both product category and customer country simultaneously.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
Crossjoin(
[Product].[Product Categories].[Category].Members, [Customer].[Customer Geography].[Country].Members) ON ROWS
FROM [Adventure Works]Variation — using * operator:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [Product].[Product Categories].[Category].Members
* [Date].[Calendar].[Calendar Year].Members ON ROWS
FROM [Adventure Works]Variation — NonEmptyCrossjoin (performance-optimized):
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, NonEmptyCrossjoin(
[Product].[Product Categories].[Category].Members, [Customer].[Customer Geography].[Country].Members, [Measures].[Internet Sales Amount]) ON ROWS
FROM [Adventure Works]Descendants
Description: Returns the set of all descendants of a member at a specified level or distance.
Syntax:
Descendants(Member_Expression, Level_Expression)
Descendants(Member_Expression, Distance)
Descendants(Member_Expression, Level_Expression, Flags)
Flags:
SELF — members at the exact level only (default)
BEFORE — members between the input member and the level
AFTER — members below the specified level
BEFORE_AND_AFTER — all members except those at the specified level
SELF_AND_BEFORE— members at the level and above
SELF_AND_AFTER— members at the level and below
LEAVES — leaf members only
Input: A member and a level or distance, optional flags
Output: Set of descendant members
Business Use Case: Get all individual products within the Bikes category for detailed product analysis.
-- All products under Bikes category SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Descendants(
[Product].[Product Categories].[Category].[Bikes],
[Product].[Product Categories].[Product]) ON ROWS
FROM [Adventure Works]Variation — leaf members only:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Descendants(
[Product].[Product Categories].[All Products],
[Product].[Product Categories].[Product], LEAVES) ON ROWS
FROM [Adventure Works]Variation — immediate children only (distance 1):
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Descendants(
[Product].[Product Categories].[Category].[Bikes],
1) ON ROWS
FROM [Adventure Works]
Distinct
Description: Removes duplicate tuples from a set.
Syntax: Distinct(Set_Expression)
Input: A set expression that may contain duplicates
Output: The set with duplicates removed (preserves order of first occurrence)
Business Use Case: Clean up a union of sets that might have overlapping members.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Distinct(
{[Product].[Product Categories].[Category].[Bikes], [Product].[Product Categories].[Category].[Clothing], [Product].[Product Categories].[Category].[Bikes]}) ON ROWS
FROM [Adventure Works]DrilldownLevel
Description: Drills down the members of a set at a specified level, or at the lowest level of the set, replacing each member with itself plus its children.
Syntax:
DrilldownLevel(Set_Expression)
DrilldownLevel(Set_Expression, Level_Expression)
DrilldownLevel(Set_Expression, , Index)
Input: A set, optional level expression or index
Output: Expanded set including members and their children
Business Use Case: Expand a category-level report to show subcategories inline.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, DrilldownLevel(
[Product].[Product Categories].[Category].Members) ON ROWS FROM [Adventure Works]DrilldownLevelTop
Description: Drills down the members of a set at a specified level, expanding only the top N children by measure value.
Syntax: DrilldownLevelTop(Set_Expression, Count [, Level_Expression [,Numeric_Expression]])
Input: A set, count, optional level, optional measure
Output: Set with top-N children expanded
Business Use Case: Show categories with only their top 3 subcategories expanded.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, DrilldownLevelTop(
[Product].[Product Categories].[Category].Members, 3,
[Product].[Product Categories].[Category], [Measures].[Internet Sales Amount]) ON ROWS
FROM [Adventure Works]DrilldownLevelBottom
Description: Drills down expanding only the bottom N children by measure value.
Syntax: DrilldownLevelBottom(Set_Expression, Count [, Level_Expression [,Numeric_Expression]])
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, DrilldownLevelBottom(
[Product].[Product Categories].[Category].Members, 3,
[Product].[Product Categories].[Category],
[Measures].[Internet Sales Amount]) ON ROWS FROM [Adventure Works]DrilldownMember
Description: Expands the members in a first set that are also present in a second set.
Syntax: DrilldownMember(Set_Expression1, Set_Expression2 [, RECURSIVE])
Input: Two sets, optional RECURSIVE flag
Output: First set expanded where members appear in second set
Business Use Case: Expand only Bikes in a category list to show its subcategories.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, DrilldownMember(
[Product].[Product Categories].[Category].Members,
{[Product].[Product Categories].[Category].[Bikes]}) ON ROWS FROM [Adventure Works]DrilldownMemberTop
Description: Expands members from the first set that appear in the second set, showing only the top N children.
Syntax: DrilldownMemberTop(Set_Expression1, Set_Expression2, Count [, Numeric_Expression [, RECURSIVE]])
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, DrilldownMemberTop(
[Product].[Product Categories].[Category].Members,
{[Product].[Product Categories].[Category].[Bikes]}, 2,
[Measures].[Internet Sales Amount]) ON ROWS FROM [Adventure Works]DrilldownMemberBottom
Description: Expands members from the first set that appear in the second set, showing only the bottom N children.
Syntax: DrilldownMemberBottom(Set_Expression1, Set_Expression2, Count [,Numeric_Expression [, RECURSIVE]])
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, DrilldownMemberBottom(
[Product].[Product Categories].[Category].Members,
{[Product].[Product Categories].[Category].[Bikes]}, 2,
[Measures].[Internet Sales Amount]) ON ROWS
FROM [Adventure Works]Except
Description: Returns the set difference — members from the first set that are not in the second set.
Syntax: Except(Set_Expression1, Set_Expression2 [, ALL])
Input: Two set expressions; optional ALL flag (retain duplicates)
Output: Members in Set1 but not in Set2
Business Use Case: Report on all categories except Components (exclude internal/OEM items from customer-facing reports).
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Except(
[Product].[Product Categories].[Category].Members,
{[Product].[Product Categories].[Category].[Components]}) ON ROWS
FROM [Adventure Works]Exists
Description: Returns members from the first set that have a non-empty intersection with tuples from the second set.
Syntax: Exists(Set_Expression1, Set_Expression2 [, MeasureGroupName])
Input: Two set expressions, optional measure group name
Output: Members from first set that co-exist with members from second set
Business Use Case: Find product subcategories that have actual sales in the United States.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Exists(
[Product].[Product Categories].[Subcategory].Members,
{[Customer].[Customer Geography].[Country].[United States]}) ON ROWS
FROM [Adventure Works]Variation — with measure group:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Exists(
[Product].[Product Categories].[Category].Members, [Date].[Calendar].[Calendar Year].[2012].Children, "Internet Sales") ON ROWS
FROM [Adventure Works]Extract
Description: Extracts tuples from a set, returning a new set containing only the members from a specified dimension.
Syntax: Extract(Set_Expression, Dimension_Expression [, Dimension_Expression ...])
Input: A set of tuples, one or more dimension expressions
Output: Unique members from the specified dimensions within those tuples
Business Use Case: From a cross-join result, extract only the distinct customer countries.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Extract(
Crossjoin(
[Product].[Product Categories].[Category].Members, [Customer].[Customer Geography].[Country].Members),
[Customer].[Customer Geography]) ON ROWS
FROM [Adventure Works]Filter
Description: Returns the subset of a set that satisfies a logical condition.
Syntax: Filter(Set_Expression, Logical_Expression)
Input: A set and a boolean condition
Output: Members from the set for which the condition is TRUE
Business Use Case: Show only product subcategories with more than $1 million in internet sales.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Filter(
[Product].[Product Categories].[Subcategory].Members, [Measures].[Internet Sales Amount] > 1000000) ON ROWS
FROM [Adventure Works]Variation — complex filter with multiple conditions:
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]} ON COLUMNS,
Filter(
[Product].[Product Categories].[Product].Members, [Measures].[Internet Sales Amount] > 500000
AND [Measures].[Internet Order Quantity] > 100) ON ROWS
FROM [Adventure Works]Generate
Description: Applies a set expression to each member of a set and returns the union of the resulting sets. Syntax:
Generate(Set_Expression1, Set_Expression2 [, ALL])
Generate(Set_Expression, String_Expression [, Delimiter])
Input: A seed set and an expression to apply per member; optional ALL to retain duplicates
Output: Union of all result sets (or concatenated string)
Business Use Case: For each product category, get its top 2 subcategories — returns a combined result across all categories.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Generate(
[Product].[Product Categories].[Category].Members, TopCount(
[Product].[Product Categories].CurrentMember.Children, 2,
[Measures].[Internet Sales Amount])) ON ROWS
FROM [Adventure Works]Variation — string generation:
WITH MEMBER [Measures].[Category Names] AS Generate(
[Product].[Product Categories].[Category].Members, [Product].[Product Categories].CurrentMember.Name, ", ")
SELECT
[Measures].[Category Names] ON COLUMNS
FROM [Adventure Works]Head
Description: Returns the first N elements from a set.
Syntax: Head(Set_Expression [, Count])
Input: A set and optional count (default 1)
Output: The first N members of the set
Business Use Case: Retrieve the first 5 months for a date range selector.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Head(
[Date].[Calendar].[Month].Members,
5) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Hierarchize
Description: Orders the members of a set in hierarchy order (parents before children).
Syntax: Hierarchize(Set_Expression [, POST])
Input: A set expression; optional POST flag (children before parents)
Output: Set ordered hierarchically (PRE order by default)
Business Use Case: After combining category and subcategory members, restore proper hierarchy order.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
Hierarchize(
{[Product].[Product Categories].[Category].Members, [Product].[Product Categories].[Subcategory].Members}) ON ROWS
FROM [Adventure Works]Variation — POST order (leaves before ancestors):
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Hierarchize(
Descendants(
[Product].[Product Categories].[All Products], [Product].[Product Categories].[Subcategory], SELF_AND_BEFORE),
POST) ON ROWS
FROM [Adventure Works]IIF (Set Form)
Description: Returns one of two sets based on a logical condition (set-returning form).
Syntax: IIF(Logical_Expression, Set_Expression1, Set_Expression2)
Input: A condition and two set expressions
Output: Set_Expression1 if condition is TRUE, Set_Expression2 if FALSE
Business Use Case: Dynamically switch between product categories and subcategories based on a parameter.
WITH MEMBER [Measures].[Sales Flag] AS IIF([Measures].[Internet Sales Amount] > 5000000, 1, 0)
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, IIF(
[Date].[Calendar].CurrentMember.Level.Name = "Calendar Year", [Date].[Calendar].CurrentMember.Children,
{[Date].[Calendar].CurrentMember}) ON ROWS FROM [Adventure Works]Intersect
Description: Returns the intersection of two sets — members that appear in both sets.
Syntax: Intersect(Set_Expression1, Set_Expression2 [, ALL])
Input: Two set expressions; optional ALL to retain duplicate tuples
Output: Members common to both sets
Business Use Case: Find product categories that had sales in both 2011 and 2012.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Intersect(
Filter(
[Product].[Product Categories].[Category].Members, ([Measures].[Internet Sales Amount],
[Date].[Calendar].[Calendar Year].[2011]) > 0), Filter(
[Product].[Product Categories].[Category].Members,
([Measures].[Internet Sales Amount], [Date].[Calendar].[Calendar Year].[2012]) > 0)) ON ROWS
FROM [Adventure Works]Members (Set Functions)
Description: Returns the set of all members in a dimension, hierarchy, or level.
Variations:
Dimension.Members — all members in a dimension
Hierarchy.Members — all members in a hierarchy
Level.Members— all members at a specific level
Input: A dimension, hierarchy, or level expression
Output: Set of all members
Business Use Case: Get all product categories for a summary report.
-- Level members SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]Variation — hierarchy members (includes all levels):
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [Product].[Product Categories].Members ON ROWS
FROM [Adventure Works]Variation — dimension members:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [Product].Members ON ROWS
FROM [Adventure Works]NonEmpty
Description: Returns the non-empty tuples from a set, based on non-empty values of a measure.
Syntax: NonEmpty(Set_Expression [, Set_Expression])
Input: A set; optional second set to define the non-empty context
Output: Members that have non-null values for the specified measure
Business Use Case: Show only customers that have actually placed orders (exclude customers with no purchase history).
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, NonEmpty(
[Customer].[Customer Geography].[Customer].Members, [Measures].[Internet Sales Amount]) ON ROWS
FROM [Adventure Works]Variation — with second set context:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, NonEmpty(
[Product].[Product Categories].[Product].Members,
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]}) ON ROWS
FROM [Adventure Works]NonEmptyCrossjoin
Description: Returns the non-empty cross product of two or more sets — eliminates tuples with null values. Syntax: NonEmptyCrossjoin(Set_Expression1, Set_Expression2 [, ...])
Input: Two or more set expressions
Output: Cross product set with empty tuples removed
Business Use Case: Analyze product and geography combinations — but only show combinations that actually have sales data.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, NonEmptyCrossjoin(
[Product].[Product Categories].[Category].Members, [Customer].[Customer Geography].[Country].Members) ON ROWS
FROM [Adventure Works]Order
Description: Sorts the members of a set by a numeric or string expression.
Sort flags:
ASC — ascending, hierarchy preserved (default)
DESC — descending, hierarchy preserved
BASC — ascending, hierarchy broken
BDESC — descending, hierarchy broken
Input: A set, sort expression, optional order flag
Output: Sorted set
Business Use Case: Rank product subcategories from highest to lowest sales.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Order(
[Product].[Product Categories].[Subcategory].Members, [Measures].[Internet Sales Amount],
BDESC) ON ROWS
FROM [Adventure Works]Variation — sort by name alphabetically:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Order(
[Product].[Product Categories].[Category].Members, [Product].[Product Categories].CurrentMember.Name, ASC) ON ROWS
FROM [Adventure Works]PeriodsToDate
Description: Returns a set of periods from the start of the current period at a specified level up to the current member.
Syntax: PeriodsToDate([Level_Expression [, Member_Expression]])
Input: Optional level (defaults to year), optional member (defaults to current)
Output: Set of period members from start of parent level to current member
Business Use Case: Year-to-date sales — sum all months from January through the current month.
WITH MEMBER [Measures].[YTD Sales] AS SUM(PeriodsToDate(
[Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[YTD Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Range ( : )
Description: Creates a set of contiguous members between two specified members at the same level.
Syntax: Member1 : Member2
Input: Two members at the same level
Output: All members between and including the two specified members
Business Use Case: Select Q1 and Q2 months for half-year analysis.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [Date].[Calendar].[Month].[January 2012]
: [Date].[Calendar].[Month].[June 2012] ON ROWS
FROM [Adventure Works]Siblings
Description: Returns the set of siblings of a member (all members at the same level with the same parent). Syntax: Member_Expression.Siblings
Input: A member expression
Output: All members at the same level under the same parent (including the input member)
Business Use Case: Compare Mountain Bikes performance against all other bike subcategories.
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]} ON COLUMNS,
[Product].[Product Categories].[Subcategory].[Mountain Bikes].Siblings ON ROWS FROM [Adventure Works]StripCalculatedMembers
Description: Removes calculated members from a set, leaving only regular (non-calculated) members. Syntax: StripCalculatedMembers(Set_Expression)
Input: A set that may include calculated members
Output: The set with calculated members removed
Business Use Case: When using .AllMembers and you need to exclude calculated measures from the axis.
WITH MEMBER [Measures].[Profit Margin] AS
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount] SELECT
StripCalculatedMembers([Measures].AllMembers) ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]Subset
Description: Returns a subset of a set starting from a specified position.
Syntax: Subset(Set_Expression, Start [, Count])
Input: A set, start index (0-based), optional count
Output: Members of the set from the start index, up to Count members
Business Use Case: Implement pagination — retrieve page 2 (records 11-20) of a product list.
-- Page 2 of products (items 11-20) SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Subset(
Order(
[Product].[Product Categories].[Product].Members, [Measures].[Internet Sales Amount],
BDESC),
10,
10) ON ROWS
FROM [Adventure Works]Tail
Description: Returns the last N members from a set.
Syntax: Tail(Set_Expression [, Count])
Input: A set and optional count (default 1)
Output: The last N members of the set
Business Use Case: Get the most recent 3 months in a date series.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Tail(
[Date].[Calendar].[Month].Members,
3) ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]TopCount
Description: Returns the top N members from a set, sorted descending by a numeric expression.
Syntax: TopCount(Set_Expression, Count [, Numeric_Expression])
Input: A set, count integer, optional sort measure
Output: The top N members sorted descending
Business Use Case: Identify the top 10 best-selling products for the executive dashboard.
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]} ON COLUMNS,
TopCount(
[Product].[Product Categories].[Product].Members, 10,
[Measures].[Internet Sales Amount]) ON ROWS FROM [Adventure Works]TopPercent
Description: Returns the smallest subset of a set whose cumulative total equals or exceeds the specified percentage of the grand total.
Syntax: TopPercent(Set_Expression, Percentage, Numeric_Expression)
Input: A set, percentage (0-100), numeric measure
Output: Members forming the top N% of the total (Pareto analysis)
Business Use Case: Find which products account for 80% of revenue (classic 80/20 Pareto analysis).
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, TopPercent(
[Product].[Product Categories].[Product].Members, 80,
[Measures].[Internet Sales Amount]) ON ROWS FROM [Adventure Works]TopSum
Description: Returns the smallest subset of a set whose cumulative sum reaches a specified value, sorted descending.
Syntax: TopSum(Set_Expression, Value, Numeric_Expression)
Input: A set, target sum value, numeric measure
Output: Top members whose cumulative sum reaches the target
Business Use Case: Find products that together generate the first $10 million in sales.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, TopSum(
[Product].[Product Categories].[Product].Members, 10000000,
[Measures].[Internet Sales Amount]) ON ROWS FROM [Adventure Works]Union
Description: Returns the union of two sets — all members from both sets, with duplicates removed by default.
Syntax:
Union(Set_Expression1, Set_Expression2 [, ALL])
{Set_Expression1, Set_Expression2} (implicit union)
Input: Two set expressions; optional ALL to retain duplicates
Output: All members from both sets
Business Use Case: Combine top-performing products from online and reseller channels into one analysis set.
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount]} ON COLUMNS,
Union(
TopCount(
[Product].[Product Categories].[Product].Members, 5,
[Measures].[Internet Sales Amount]), TopCount(
[Product].[Product Categories].[Product].Members, 5,
[Measures].[Reseller Sales Amount])) ON ROWS FROM [Adventure Works]Variation — implicit union using { }:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
{[Product].[Product Categories].[Category].[Bikes].Children, [Product].[Product Categories].[Category].[Accessories].Children} ON ROWS
FROM [Adventure Works]