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

6.1.10 Filtering

As its name suggests, filtering is used to reduce the number of axis members
that are displayed. This is to be contrasted with slicing, as specified in the
WHERE clause, since slicing does not affect selection of the axis members,
but rather the values that go into them.
We have already seen the most common form of filtering, where the
members of an axis that have no values are removed with the NON EMPTY
clause. The FILTER function can be used for more specific filtering. This
function filters a set according to a specified condition. Suppose we want
to show sales amount in 1997 by city and by product category. If one were
only interested in viewing top-performing cities, defined by those whose sales
amount exceeds $25,000, a filter would be defined as follows:

SELECT Product.Category.MEMBERS ON COLUMNS,
FILTER(Customer.City.MEMBERS, (Measures.[Sales Amount],
[Order Date].Calendar.[1997]) > 25000) ON ROWS
FROM Sales
WHERE (Measures.[Net Sales Growth], [Order Date].Calendar.[1997])

As shown in the result below, only five cities satisfy the condition.

Beverages Condiments Confections Dairy Products ···

Graz $-2,370.58 $6,114.67 $8,581.51 $7,171.01 ···

Cunewalde $6,966.40 $2,610.51 $8,821.85 $7,144.74 ···

London $2,088.23 $683.88 $1,942.56 $83.13 ···

Montreal $9,142.78 $2,359.90 $213.93 $3,609.16 ···

Boise $1,871.10 $94.84 $4,411.46 $6,522.61 ···

As another example, the following query shows customers that in 1997 had
profit margins below the state average:

WITH MEMBER Measures.[Profit%] AS
(Measures.[Sales Amount] - Measures.[Freight]) /
(Measures.[Sales Amount]), FORMAT STRING = ' #0.00% '
MEMBER Measures.[Profit%City] AS
(Measures.[Profit%],
Customer.Geography.CURRENTMEMBER.PARENT),
FORMAT STRING = ' #0.00% '

SELECT { Measures.[Sales Amount], Measures.[Freight], Measures.[Net Sales],

Measures.[Profit%], Measures.[Profit%City] ON COLUMNS,

FILTER(NONEMPTY(Customer.Customer.MEMBERS),
(Measures.[Profit%]) < (Measures.[Profit%City])) ON ROWS
FROM Sales
WHERE [Order Date].Calendar.[1997]

}

The result of this query is shown below: