Named Sets
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
Named sets allow you to define a reusable collection of members. They can be query-scoped (WITH SET) or session-scoped (CREATE SESSION SET).
WITH SET — Query-Scoped Named Set
Description: Defines a set of members available only within the current query. Syntax:
Business Use Case: Define a top products set once and reuse it on multiple axes or in calculations.
WITH SET [TopProducts] AS TopCount(
[Product].[Product Categories].[Product].Members,
10,
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]} ON COLUMNS,
[TopProducts] ON ROWS FROM [Adventure Works]Dynamic Named Set
Re-evaluated for each cell context — the set membership can change depending on the context.
WITH SET [TopCategoriesPerYear] AS TopCount(
[Product].[Product Categories].[Category].Members, 2,
[Measures].[Internet Sales Amount])
SELECT
[Date].[Calendar].[Calendar Year].Members ON COLUMNS,
[TopCategoriesPerYear] ON ROWS FROM [Adventure Works]CREATE SESSION SET
Description: Creates a named set persisted for the duration of the current session. Can be referenced in subsequent queries without redefining.
Syntax: CREATE SESSION SET [SetName] AS Set_Expression
-- Define once for the session CREATE SESSION SET [TopProducts] AS
TopCount(
[Product].[Product].[Product].Members, 10,
[Measures].[Internet Sales Amount])
-- Use in subsequent query SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [TopProducts] ON ROWS
FROM [Adventure Works]CREATE SESSION STATIC SET
Description: Creates a static session set — evaluated once at creation time. Members are fixed and do not change with query context.
Syntax: CREATE SESSION STATIC SET [SetName] AS Set_Expression
Business Use Case: Lock in the top-5 products at report generation time; same members shown regardless of filters applied later.
CREATE SESSION STATIC SET [StaticTopProducts] AS TopCount(
[Product].[Product].[Product].Members, 5,
[Measures].[Internet Sales Amount])
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [StaticTopProducts] ON ROWS
FROM [Adventure Works]Key Difference: Dynamic vs Static Sets
Aspect | Dynamic (WITH SET / SESSION SET) | Static (SESSION STATIC SET) |
Evaluation | Re-evaluated per cell context | Evaluated once at creation |
Member stability | Can vary by context | Fixed at creation time |
Use case | Context-sensitive rankings | Snapshot/audit reporting |
Performance | More computation | Faster for repeated use |