MDX Concepts - Microsoft SQL Server 2008 Analysis Services - page 153

FIGURE 10.16 The WHERE clause affects the calculation of the filter expression and result of
the Filter function.
expression. When Analysis Services executes the filter expression, it would use the Unit
Sales measure; but to calculate the cell values, it would use the Store Sales measure (see
Figure 10.17).
SELECT Filter( [Store].[Store].[Store Country].members,
[Measures].[Unit Sales].Value >1000) ON COLUMNS FROM [Warehouse and Sales]
WHERE ([Time].[Time].[Year].[1997],[Measures].[Store Sales])
FIGURE 10.17 When the same attribute is used in the WHERE clause and in the filter expres-
sion, the Filter function is calculated using the attribute specified in the filter expression, but
the cell value is calculated using the attribute specified in the WHERE clause.
NOTE
The rules we just discussed are true for all MDX expressions, not just for the Filter
function.
Functions for Ordering Data
When you're analyzing data, you quite often need to see it in some particular order associ-
ated with the data values. For example, perhaps you want to see the stores that are
performing well first and lower-performing stores last. The Order function sorts the tuples
in the set according to the value of the expression you provide as a parameter. For
example, if we are ordering stores according to the values of the Store Sales measure, we
can write the following MDX statement:
SELECT Order( [Store].[Stores].[Store].members, [Measures].[Store Sales], BDESC) ON
➥ COLUMNS FROM [Warehouse and Sales]
That statement returns the set shown in Figure 10.18.
FIGURE 10.18 When you pass the BDESC keyword to the Order function, it returns a set of
members sorted in descending order.