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

for customers who live in Belgium and who bought products in the category
beverages:
SELECT Measures.MEMBERS ON COLUMNS,
[Order Date].Year.MEMBERS ON ROWS
FROM Sales
WHERE (Customer.Country.Belgium, Product.Categories.Beverages)
To use multiple members from the same hierarchy, we need to include a set
in the WHERE clause. For example, the following query shows the values of
all measures for all years for customers who bought products in the category
beverages and live in either Belgium or France:
SELECT Measures.MEMBERS ON COLUMNS,
[Order Date].Year.MEMBERS ON ROWS
FROM Sales
WHERE ( { Customer.Country.Belgium, Customer.Country.France } ,
Product.Categories.Beverages)
Using a set in the WHERE clause implicitly aggregates values for all members
in the set. In this case, the query shows aggregated values for Belgium and
France in each cell.
Consider now the following query, which requests the sales amount of
customers by country and by year:
SELECT [Order Date].Year.MEMBERS ON COLUMNS,
Customer.Country.MEMBERS ON ROWS
FROM Sales
WHERE Measures.[Sales Amount]
Here, we specified in the WHERE clause the measure to be displayed. If no
measure is stated, then the default measure is used. The result is given below:

All 1996 1997 1998
Austria $115,328.31 $24,467.52 $55,759.04 $35101.7502
Belgium $30,505.06

$5,865.10 $9,075.48 $15,564.48

Denmark $32,428.93 $2,952.40 $25,192.53 $4,284.00

Finland $17,530.05 $2,195.760 $13,077.29 $2,257.00

··· ··· ··· ··· ···

The WHERE clause can combine measures and dimensions. For example,
the following query will show a result similar to the one given above, but now
with the figures restricted to the category beverages:
SELECT [Order Date].Year.MEMBERS ON COLUMNS,
Customer.Country.MEMBERS ON ROWS
FROM Sales
WHERE (Measures.[Sales Amount], Product.Category.[Beverages])
If a dimension appears in a slicer, it cannot be used in any axis in the
SELECT clause. We will see later that the FILTER functioncanbeusedto
filter members of dimensions appearing in an axis.