MDX Puzzle #4 - Solution

This puzzle may not have been much of a challenge, but it introduced two new MDX concepts.  I have to admit, one of my colleagues and I chatted about this puzzle, and I realized that my requirements were a little unrealistic.  As a result, I changed the requirements a little.  Instead of returning data between the Calendar Years of 2006 and 2008, the data should be filtered by Ship Date Calendar Year.

So, here is what I started with:

SELECT 

    NON EMPTY

    (

        [Ship Date].[Calendar Year].Children,

        [Measures].[Internet Sales Amount] 

    )ON COLUMNS,

        [Product].[SubCategory].Members ON ROWS

FROM [Adventure Works]

This satisfied all of the requirements except the filters, which are the reason for the post.  My first try at limiting the result set to date between Ship Date Calendar Year 2006 and 2008 resembled this query:

This query did work.  It only returned the years from my requirements, but I was sure there was a better way to accomplish this.  Notice that I included a set that implicitly limited the query for years 2006, 2007, and 2008.  What if I needed a larger range, say between 2000 and 2010?  No way am I typing all of that.  After a little digging I figured out how to perform the range operations (BETWEEN) in MDX.  Here is the query:

Using the ranger operator (:), which returns an ordered set based on the two specified member endpoints, I was able to return all members from the Ship Date Calendar Year between 2006 and 2008.  Next I needed to limit the result to only Product Categories that had sales greater than $1,000,000.  Thinking like a T-SQL programmer I went directly to a where clause using a greater than operator (>).  See the query below:

That did not quite work out for reasons that will be explained in later postings.  So, how did I get it to work.  I had to use the FILTER function to limit the result set.  The FILTER function returns a filtered set based on a search condition.  It accepts two arguments.  The first is a Set_Expression, which is an MDX expression that returns a set.  The second i a Logical_Expression that evaluates to true or false, which could be considered the WHERE clause.  Here is the final query:

The FILTER function is used on the Rows axis in the query to return on the Product Subcategories where Internet Sales Amount is greater than $1,000,000.  As I mentioned earlier, I modified the requirements a little because my initial set did not make logical sense.  Stay tuned for Puzzle #5.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.