MDX Usage Examples

Basic Examples

This topic shows several basic MDX queries. For detailed information on the MDX functions used in these examples see Basic MDX Queries and Syntax and MDX Functions.

The data that is used in these simple examples is from a company that sells various makes and models of cars. The company needs to report sales figures for different months.

Example of a simple two-dimensional query:

SELECT
   { [cars].[all cars].[chevy], [cars].[all cars].[ford] } ON COLUMNS,
   { [date].[all date].[march], [date].[all date].[april] } ON ROWS
FROM mddbcars

Example of how you can flip the rows and columns:

SELECT
   { [cars].[all cars].[chevy], [cars].[all cars].[ford] } ON ROWS,
   { [date].[all date].[march], [date].[all date].[april] } ON COLUMNS
FROM mddbcars

Example of selecting a different measure (sales_n) to be the default:

SELECT
   { [cars].[all cars].[chevy], [cars].[all cars].[ford] } ON COLUMNS,
   { [date].[all date].[march], [date].[all date].[april] } ON ROWS
FROM mddbcars
WHERE ([measures].[sales_n])

Example of using ":" to get a range of members:

SELECT
   { [cars].[all cars].[chevy], [cars].[all cars].[ford] } ON COLUMNS,
   { [date].[all date].[january] : [date].[all date].[april] } ON _ROWS
FROM mddbcars

Example of the .MEMBERS function:

SELECT
   { [cars].[all cars].[chevy], [cars].[all cars].[ford] } ON COLUMNS,
   { [date].members } ON ROWS
FROM mddbcars

example of the .CHILDREN function:

SELECT
   { [cars].[all cars].[ford].children } ON COLUMNS,
   { [date].members } ON ROWS
FROM mddbcars

Example of selecting more than one dimension in a tuple:

SELECT
   { ( [cars].[all cars].[chevy], [measures].[sales_sum] ),
     ( [cars].[all cars].[chevy], [measures].[sales_n] ),
     ( [cars].[all cars].[ford], [measures].[sales_sum] ),
     ( [cars].[all cars].[ford], [measures].[sales_n] )
   } ON COLUMNS,
   { [date].members } ON ROWS
FROM mddbcars

Example of how the CROSSJOIN function makes tuple combinations for you:

SELECT
   { CROSSJOIN ( { [cars].[all cars].[chevy],  [cars].[all cars].[ford] },
                     { [measures].[sales_sum], [measures].[sales_n] }  )
   } ON COLUMNS,
   { [date].members } ON ROWS
FROM mddbcars

Example of using the NON_EMPTY keyword to discard the row with no sales:

SELECT
   { CROSSJOIN ( { [cars].[all cars].[chevy],  [cars].[all cars].[ford] },
                     { [measures].[sales_sum], [measures].[sales_n] }  )
   } ON COLUMNS,
   NONEMPTY { [date].members } ON ROWS
FROM mddbcars

Additional Basic Examples

Example of a basic two-dimension table:

SELECT {[time].[all yqm]}  ON COLUMNS  , 
{[geography].[global].[all global] } ON ROWS 
FROM [orionstar]

Example of a basic two-dimension table with an analysis variable (Measures):

 SELECT {[time].[all yqm]}  ON COLUMNS  , 
{[geography].[global].[all global]} ON ROWS 
FROM [orionstar]  
WHERE [measures].[total_retail_pricesum]

Example of a basic two-dimension table with specific columns selected within the same level:

SELECT {[time].[all yqm].[2001] , [time].[all yqm].[2002]}  ON COLUMNS  , 
{[geography].[global].[all global] } ON ROWS 
FROM [orionstar] 
WHERE [measures].[total_retail_pricesum]

Example of a basic two-dimension table with specific rows selected within the same level:

SELECT {[time].[all yqm].[2001] , [time].[all yqm].[2002]}  ON COLUMNS  , 
{[geography].[global].[all global].[europe] , 
[geography].[global].[all global].[asia] } ON ROWS 
FROM [orionstar] 
WHERE [measures].[total_retail_pricesum]

Joins and Extractions for Queries Examples

Example of the CROSSJOIN function:

SELECT {CROSSJOIN({[yqm].[all yqm]}, 
{[measures].[actualsalessum]})} ON COLUMNS,  
{[geography].[all geography]} ON ROWS 
FROM [booksnall]

Example of the UNION function:

SELECT { union([yqm].[all yqm].[1999], 
[yqm].[all yqm].[2000],all)} ON COLUMNS, {
[geography].[all geography] } ON ROWS   
FROM [booksnall]  
WHERE [measures].[predictedsalessum]

Example of the EXCEPT function as a query:

SELECT {except({[yqm].[qtr].members}, 
{([yqm].[all yqm].[1998].[1]): ([yqm].[all yqm].[2001].[1])})} ON COLUMNS,  
{[geography].[all geography]} ON ROWS  
FROM [booksnall]

Example of the EXTRACT function:

SELECT {extract ({([yqm].[all yqm].[1998]), 
([yqm].[all yqm].[2000])},time )} ON COLUMNS,  
{[geography].[all geography]} ON ROWS   
FROM [booksnall]

Examples of Displaying Multiple Dimensions on Columns and Eliminating Empty Cells

Example of displaying a measure as a column by using the CROSSJOIN function:

SELECT  
CROSSJOIN ([time].[yqm].[year_id].members ,
[measures].[total_retail_pricesum]) ON COLUMNS,  
{[geography].[global].[all global].children } ON ROWS   
FROM [orionstar]

Example of eliminating empty values by using the NON EMPTY function (in the previous example there are several missing values for Year and Regions):

SELECT  
NONEMPTY (CROSSJOIN ([time].[yqm].[all yqm].children ,
[measures].[total_retail_pricesum])) ON COLUMNS,  
NONEMPTY({[geography].[global].[all global].children }) ON ROWS   
FROM [orionstar]

Example of using a second CROSSJOIN to combine all three values:

SELECT  
non_empty(CROSSJOIN(CROSSJOIN ([time].[yqm].[all yqm].children ,
[measures].[total_retail_pricesum]),{[demographics].[all demographics].
female} )) ON COLUMNS,  
non_empty({[geography].[global].[all global].children }) ON ROWS   
FROM [orionstar]

Example of executing the previous step with one function and adding a third set:

Note: The number controls which columns are viewed as well as the crossjoins. [cautionend]

SELECT  
NONEMPTY CROSSJOIN ([time].[all yqm].children,[measures].[total_retail_pricesum],
{[demographics].[all demographics].female},3) ON COLUMNS,  
NONEMPTY({[geography].[global].[all global].children }) ON ROWS   
FROM [orionstar]

Example of the COALESCE EMPTY function:

WITH MEMBER [measures].[quantity_nomiss] as 'COALESCE EMPTY
(measures.[quantitysum], 0)'

Top of Page