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

February 9, 2004


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.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

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.

5.  Type the following query into the Query pane:

-- MDX16-1:  Tutorial Query No. 1
     [Time].[1998].[Q2]} ON COLUMNS,
          {[Product Category].MEMBERS} ON ROWS
     (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."

6.  Execute the query by clicking the Run Query button in the toolbar.

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

Illustration 1: Result Dataset - Without Filtering (Partial View)

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.

7.  Select File -> Save As, and name the query MDX16-1, leaving the query open.

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

8.  Modify the ON ROWS specification of the query from the following:

{[Product Category].MEMBERS} ON ROWS

to the following:

    [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.

Illustration 2: The Query - with the Addition of the Filter() Function

9.  Replace the existing comment line for the query with the following:

-- MDX16-2:  Tutorial Query No. 2

10.  Execute the query by clicking the Run Query button on the toolbar.

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

Illustration 3: Result Dataset - With Filter() Function in Place

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers