MDX Essentials: Basic Set Functions: The Filter() Function - Page 3

Practice

Let's reinforce our understanding of the basics we have covered so far, by using the Filter() function in a manner that illustrates its operation in reducing a specified set to return only a subset whose tuples meet its search condition. As we have done throughout the series, we will employ the MDX Sample Application for constructing and executing the MDX we examine, and for viewing the result datasets we obtain.

We will next compose a straightforward query, to gain an understanding of the use of the Filter() function to return a subset of a specified set that we will illustrate through a business need scenario. Let's say that a group of information consumers within the organization come to us with a requirement: they wish to see a simple report that lists the Product Categories that have experienced a decrease in shipping volume between the first and second quarters of 1998. Our query will focus on Shipping Units, a value that is captured monthly within the FoodMart organization and which is stored in the Warehouse cube.

First, we will create a core query to retrieve the data for all Product Categories. This will generate a result dataset which we can next compare to the same query with the Filter() function in place, to reinforce our understanding of its operation.

-- MDX16-1:  Tutorial Query No. 1
SELECT
    {[Time].[1998].[Q1],
     [Time].[1998].[Q2]} ON COLUMNS,
          {[Product Category].MEMBERS} ON ROWS
FROM  
     [Warehouse]
WHERE
     (Measures.[Units Shipped])

NOTE: A word of explanation for those that might be joining the MDX Essentials series with this article: The layout I use in my code illustrations might seem unlike that of other references (indeed, it's rare to find any two references that illustrate MDX expressions and queries with identical presentations!); my intent here is to attempt to make the components of the coding easily understandable by organizing them in a clear manner.

The code will execute in virtually any case, whether it is arranged exactly as shown or not, assuming that the elements contained in the example are typed in correctly. I have learned from experience with large audiences that there are, beyond doubt, a few "purists" out there that maintain there is a "correct" way to arrange the code - and everything else; I don't belong to any such "clubs," however, and am focused more on clarity in my examples. Please do not take my eccentricities as being reflective of any "manual of style."

Analysis Services populates the Results pane, presenting the dataset partially shown in Illustration 1.

We see the total Units Shipped volume for all Product Categories, returned for the first two quarters of year 1998. A cursory review reveals that some categories experienced increases in volumes shipped between quarters, while others experienced a decline in volume.

Let's add the Filter() function to our ON ROWS specification next, and confirm its effects on the returned dataset.

{[Product Category].MEMBERS} ON ROWS

{FILTER (
    [Product].[Product Category].MEMBERS,
        (([Time].[1998].[Q1] , [Measures].[Units Shipped]) >
            ([Time].[1998].[Q2],[Measures].[Units Shipped])))} ON ROWS

We are thus placing the Filter() function within our rows axis. The modified query appears in the Query pane as depicted in Illustration 2.

-- MDX16-2:  Tutorial Query No. 2

The filtered dataset is returned, as shown in Illustration 3.