Previous Page | Next Page

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

Previous Page | Next Page | Top of Page