Filtering Members on Axes

Filtering members on axes is a pretty common requirement. The filtering process can be extremely simple or an advanced MDX expression. The Filter function is one of the most common uses of filtering sets and projecting onto axes. You saw some examples of the FILTER function in Chapter 3. To refresh, assume you want to look at the gross profit of all the products whose Sales have been greater than $50,000. You will have a simple FILTER condition in the MDX query as shown in the following example:

     SELECT {[Measures].[Gross Profit]} on 0,     FILTER ( [Product].[Product Categories].[SubCategory].members,[Sales Amount] >     50000) on 1     from [Adventure Works] 

You have already learned that you can eliminate empty cells in a variety of ways. Assume you have a large crossjoin on one of the axes and you want to apply complex filter conditions. In such a case the filter condition needs to evaluate over all the cells being represented by the crossjoin, and it might be quite a performance hit. It is more efficient to eliminate cells that have null values and then apply the filter condition on the resulting set. Analysis Services provides a new clause called the HAVING clause, which allows you to do this. The syntax for the HAVING clause is:

     SELECT <axis_specification> ON 0,     NON EMPTY <axis_specification> HAVING <filter condition> ON 1     FROM <cube identifier> 

The following MDX query uses the HAVING clause to analyze the gross profit of all the products that have sales amounts of $5,000 for a product in any city within a month:

     SELECT {[Measures].[Gross Profit]} on 0,     NON EMPTY [Product].[Product Categories].[Product] * [Customer].[Customer     Geography].[City]     * [Date].[Fiscal].[Month].members     HAVING [Sales Amount] > 5000 on 1     from [Adventure Works]