Advanced MDX - Microsoft SQL Server 2008 Analysis Services - page 165

but [Customer].[Gender].&[M] on the WHERE clause, the tuple [Nina Metz] will be
removed from the set projected on the COLUMNS axis:

SELECT { [Nina Metz], [David Wall] } ON COLUMNS
FROM [Warehouse and Sales] WHERE ([Measures].[Unit Sales],[Customer].[Gender].&[M])

Figure 11.7 shows the results of this query.

David Wall

44

FIGURE 11.7 Analysis Services applies auto-exist between the sets used on each axis and a
set used in a WHERE clause.

You don't have to rely on the system's auto-exist operation to find out which members
exist with each other: You can use the MDX function Exists , which was introduced in
Analysis Services 2005. Exists takes two sets as parameters and returns a set of tuples
from the first set that exist with one or more tuples from the second set:

SELECT Exists({[Nina Metz], [David Wall]}, [Customer].[Gender].&[M]) ON COLUMNS
FROM [Warehouse and Sales] WHERE [Measures].[Unit Sales]

For example, to retrieve a set of occupations of customers who have a graduate degree,
you could write the following query:

SELECT Exists([Customer].[Occupation].members
[Customer].[Education].&[Graduate Degree])} on COLUMNS
FROM [Warehouse and Sales]

Figure 11.8 shows the results of this query.

All Management Professional

626866 626866 626866

FIGURE 11.8

Results of the Exists function.