MDX Usage Examples

Session-Level Calculated Member Examples

The data that is used in these examples is from a company that sells electronics and outdoor and sporting goods equipment.

Example 1

This example creates the session-level calculated member called avg_price in the sales cube on the Measures dimension. This calculated measure shows the average price:

create session
    member [sales].[measures].[avg_price] as
      '[Measures].[total] / [Measures].[qty]'

Nothing is returned when you create a session-level calculated member.

Example 2

This example uses the session-level calculated member called "avg_price." It shows the quantity, total, and average price of goods sold from 1998 through 2000.

SELECT
    {[measures].[qty], [measures].[total],
 [measures].[avg_price]} ON COLUMNS,
    {[time].[all time].children} ON ROWS
 FROM sales

Here is the resulting output:

Year	Qty	Total	Average Price
1998	440,852	10,782,352.94	24.4579880322648
1999	539,433	14,080,419.58	26.1022584454418
2000	32,267	859,108.83	26.6249986053863

Example 3

This example uses the session-level calculated member called "avg_price." It shows the quantity, total, and average price of goods sold in different customer regions.

SELECT
    {[measures].[qty], [measures].[total],
 [measures].[avg_price]} ON COLUMNS,
    {[customer].[all customer].children} ON ROWS
 FROM sales

Here is the resulting output:

Region	Qty	Total	Average Price
Central	157,659	3,942,290.26	25.0051710336866
Mid-Atlantic	79,555	2,011,008.77	25.2782197222048
Midwest	259,759	6,614,999.09	25.4659091311562
Mountains	32,768	838,064.62	25.5757025146485
Northeast	143,934	3,658,452.99	25.4175732627454
South-Central	64,943	1,662,479.79	25.5990605607995
Southeast	122,888	3,134,589.55	25.5076944046611
West	151,046	3,859,996.28	25.5551042728705

Example 4

This example uses the session-level calculated member called "avg_price." It shows the quantity, total, and average price of goods sold in the different product groups.

 SELECT
    {[measures].[qty], [measures].[total],
 [measures].[avg_price]} On COLUMNS,
    {[product].[all product].children} ON ROWS
 FROM sales

Here is the resulting output:

Product	Qty	Total	Average Price
Doing	191,321	4,850,302.26	25.3516459771797
Electronics	330,977	8,426,846.64	25.4605203382712
Health & Fitness	185,909	4,717,790.80	25.3768822380842
Outdoor & Sporting	304,345	7,726,941.65	25.3887583170415

Top of Page