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

￿ SELF , which is the default, displays values for the City level as above.
￿ BEFORE displays values from the state level up to the Country level.
￿ SELF AND BEFORE displays values from the City level up to the Country
level.
￿ AFTER displays values from the Customer level, since it is only level after
City .
￿ SELF AND AFTER displays values from the City and Customer levels.
￿ BEFORE AND AFTER displays values from the Country level to the
Customer level, excluding the former.
￿ SELF BEFORE AFTER displays values from the Country level to the
Customer level.
￿ LEAVES displays values from the City level as above, since this is the only
leaf level between Country and City . On the other hand, if LEAVES is used
without specifying the level, as in the following query
DESCENDANTS(Customer.Geography.Germany, ,LEAVES)

then the leaf level, that is, Customer , will be displayed.

The ASCENDANTS function returns a set that includes all the ancestors
of a member and the member itself. For example, the following query asks
for the sales amount for the customer Du monde entier and all its ancestors in
the Geography hierarchy, that is, at the City , State , Country , Continent ,and
All levels:

SELECT Measures.[Sales Amount] ON COLUMNS,
ASCENDANTS(Customer.Geography.[Du monde entier]) ON ROWS
FROM Sales

The result of the query is as follows:

Sales Amount
$1,548.70

Du monde entier

Nantes $4,720.86

Loire-Atlantique $4,720.86

France $77,056.01

Europe $683,523.76

All Customers $1,145,155.86

The function ANCESTOR can be used to obtain the result for an ancestor at
a specified level, as shown next:

SELECT Measures.[Sales Amount] ON COLUMNS,
ANCESTOR(Customer.Geography.[Du monde entier],
Customer.Geography.State) ON ROWS
FROM Sales

Here, only the line corresponding to Loire-Atlantique in the table above will
be shown.