Basic Examples

This topic shows several basic MDX queries. For detailed information about 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

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]

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]   

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:

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)'