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.

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:

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:

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: