MDX Usage Examples

Query-Calculated Member Examples

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

Example 1

This query creates a calculation for the average price of a car. The average price of a car is calculated by dividing the sales_sum by the count (sales_n). The query returns the sales_sum, sales_n, and the average price for March and April.

WITH
   MEMBER[measures].[avg price] AS
 '[measures].[sales_sum] / [measures].[sales_n]'
SELECT
   { [measures].[sales_sum] , [measures].[sales_n], [measures].[avg price] }
 ON COLUMNS,
   { [date].[all date].[march], [date].[all date].[april] } ON ROWS
FROM mddbcars

Here is the resulting output:

Date	Sales_sum	Sales_n	Avg Price
March	$59,000.00	4	14750
April	$34,000.00	3	11333.33

Example 2

This query has the same calculation that was created in example 1. This time the calculation is put in the slicer instead of an axis. In this query, the types of cars that were sold are on the column and the months that the cars were sold are on the rows. The value in the cells is the average price of the car for that month.

WITH
   MEMBER [measures].[avg price] as '[measures].[sales_sum] / 
[measures].[sales_n]'
SELECT
   { [cars].[car].members } ON COLUMNS,
   { [date].members } ON ROWS
FROM mddbcars
WHERE ([measures].[avg price])

Here is the resulting output:

Date	Chevy	Chrysler	Ford	Toyota
All date	13500	20000	12285.71	8444.45
January		20000	10000	8000
February		20000		11000
March	17000		14000
April	10000		12000
May			10000	4000

Example 3

This query adds the values of the Chevy, Chrysler, and Ford cars and combines them into one calculation called US. The query shows the sales SUM for the U.S. cars and the Toyota for January through May.

WITH
  MEMBER[cars].[all cars].[us] AS '
      SUM( { [cars].[all cars].[chevy],
                 [cars].[all cars].[chrysler],
                 [cars].[all cars].[ford]
               } ) '
SELECT
   { [cars].[all cars].us, [cars].[all cars].toyota } ON COLUMNS,
   { [date].members } ON ROWS
FROM mddbcars

Here is the resulting output:

Date	U.S.	Toyota
All Date	$153,000.00	$76,000.00
January	$ 30,000.00	$24,000.00
February	$ 20,000.00	$44,000.00
March	$ 59,000.00
April	$ 34,000.00
May	$ 10,000.00	$ 8,000.00

Top of Page