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.

   MEMBER[measures].[avg price] AS
 '[measures].[sales_sum] / [measures].[sales_n]'
   { [measures].[sales_sum] , [measures].[sales_n], [measures].[avg price] }
   { [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.

   MEMBER [measures].[avg price] as '[measures].[sales_sum] / 
   { [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
20000 10000 8000
March 17000
April 10000

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.

  MEMBER[cars].[all cars].[us] AS '
      SUM( { [cars].[all cars].[chevy],
                 [cars].[all cars].[chrysler],
                 [cars].[all cars].[ford]
               } ) '
   { [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