MDX Query Fundamentals
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
1.1 Basic Query Structure
An MDX SELECT statement defines what data to retrieve and how to organize it across axes.
[WITH <calculated_member_or_set_definitions>] SELECT
<axis_expression> ON COLUMNS,
<axis_expression> ON ROWS
[, <axis_expression> ON PAGES ...] FROM <cube>
[WHERE <slicer_expression>]Axis aliases:
ON COLUMNS = ON 0
ON ROWS = ON 1 ON PAGES = ON 2
ON SECTIONS = ON 3
ON CHAPTERS = ON 4
Minimal Query Example
Business Use Case: View internet sales by product category.
Input: Product Categories hierarchy members, Internet Sales Amount measure Output: Grid with categories as rows, sales as column.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]Expected Output:
| Internet Sales Amount |
Accessories | $700,759.96 |
Bikes | $28,318,144.65 |
Clothing | $339,772.61 |
Components | $11,799,076.66 |
Query with Slicer (WHERE clause)
The WHERE clause filters the entire result to a specific member — it does not appear as an axis.
Business Use Case: Show product category sales for year 2012 only.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]
Multi-Measure Columns
Business Use Case: Compare sales amount vs order quantity by category.
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]Non-Empty Results
Use to suppress rows/columns where all values are null or zero.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON EMPTY [Customer].[Customer Geography].[Country].Members ON ROWS FROM [Adventure Works]Curly Braces — Set Notation
Curly braces define an explicit set of members.
Description: Used to explicitly enumerate a set of members or combine multiple members into a set.
Syntax: { Member1, Member2, ... }
-- Select specific categories SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
{[Product].[Product Categories].[Category].[Bikes], [Product].[Product Categories].[Category].[Clothing]} ON ROWS
FROM [Adventure Works]