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

The latter expression uses fully qualified or unique names, namely, the
dimension, hierarchy, and level to which the specific member belongs. When
member names are uniquely identifiable, fully qualified member names are not
required. Nevertheless, to remove any ambiguities in formulating expressions,
the use of unique names is recommended.
To retrieve the states of the countries above, we may use the function
CHILDREN as follows:

SELECT [Order Date].Year.MEMBERS ON COLUMNS,
NON EMPTY Customer.France.CHILDREN,

Customer.Italy.CHILDREN

{

} ON ROWS

FROM Sales
WHERE Measures.[Sales Amount]

The result is shown below, where the first two lines correspond to departments
in France and the last two lines correspond to provinces in Italy.

All 1996 1997 1998
Bas-Rhin $18,534.07 $9,986.20 $7,817.87 $730.00
Bouches-du-Rhone $19,373.10 $2,675.88 $10,809.36 $5,887.86
··· ··· ··· ··· ···

Reggio Emilia $6,641.83 $80.10 $3,000.84 $3,560.89

Torino $1,545.70 $249.70 $1,296.00

The MEMBERS and CHILDREN functions seen above do not provide the
ability to drill down to a lower level in a hierarchy. For this, the function
DESCENDANTS can be used. For example, the following query shows the
sales amount for German cities:

SELECT [Order Date].Year.MEMBERS ON COLUMNS,
NON EMPTY DESCENDANTS(Customer.Germany, Customer.City)
ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]

The result of the above query is given next.

All 1996 1997 1998
Mannheim $2,381.80 $1,079.80 $1,302.00
Stuttgart $8,705.23 $2,956.60 $4,262.83 $1,485.80
Munchen $26,656.56 $9,748.04 $11,829.78 $5,078.74
··· ··· ··· ··· ···

By default, the function DESCENDANTS displays only members at the
level specified as its second argument. An optional flag as third argument
states whether to include or exclude descendants or children before and after
the specified level as follows: