Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 189

WITH SET TopFiveProducts AS
TOPCOUNT ( Product.Categories.Product.MEMBERS, 5,
Measures.[Sales Amount] )

SELECT { Measures.[Unit Price], Measures.Quantity, Measures.Discount,

Measures.[Sales Amount] ON COLUMNS,

TopFiveProducts ON ROWS
FROM Sales

}

The result of the query is shown below:

Unit Price Quantity Discount Sales Amount

Cote de Blaye $256.63 623 4.78% $141,396.74

Raclette Courdavault $53.17 1,369 3.96% $65,658.45

Thuringer Rostbratwurst $115.24 596 6.21% $63,657.02

Tarte au sucre $46.56 1,068 5.53% $46,643.97

Camembert Pierrot $34.32 1,498 7.21% $44,200.68

6.1.8 Relative Navigation

It is often necessary to relate the value of a member to those of other
members in a hierarchy. MDX has many methods that can be applied
to a member to traverse a hierarchy, the most common ones are PRE-
VMEMBER , NEXTMEMBER , CURRENTMEMBER , PARENT , FIRSTCHILD ,
and LASTCHILD . Suppose we want to calculate the sales of a member of the
Geography hierarchy as a percentage of the sales of its parent, as shown in
the following query:

WITH MEMBER Measures.[Percentage Sales] AS
(Measures.[Sales Amount], Customer.Geography.CURRENTMEMBER) /
(Measures.[Sales Amount],
Customer.Geography.CURRENTMEMBER.PARENT),
FORMAT STRING = ' #0.00% '
SELECT { Measures.[Sales Amount], Measures.[Percentage Sales] }
ON COLUMNS, DESCENDANTS(Customer.Europe,
Customer.Country, SELF AND BEFORE) ON ROWS
FROM Sales

In the WITH clause, the CURRENTMEMBER function returns the current
member along a dimension during an iteration, while the PARENT function
returns the parent of a member. In the SELECT clause, the measures for
European countries are displayed. The expression defining the calculated
member can be abbreviated as follows:

(Measures.[Sales Amount]) / (Measures.[Sales Amount],
Customer.Geography.CURRENTMEMBER.PARENT)