MDX Puzzle #5 - Solution

Again I want to apologize for the slow posting of the solution to this puzzle, but I have been hard at work on SQL Saturday #28.  Fortunately, I carved out some time to write it up.  The solution to this puzzle could have been accomplished a couple of ways.  I chose to use the SUM and YTD MDX functions, but before I discuss these functions I will start with the basic query, which satisfies these requirements:

1.  Internet Sales Amount as a Column

2.  Delivery Date Calendar Month as a Row

3.  Applies a filter to limit the rows from the Delivery Date of January 2006 to December 2006.

SELECT

    NON EMPTY(

        {

            [Measures].[Internet Sales Amount]

        }

    )ON COLUMNS,

     [Delivery Date].[Calendar].[Month].&[2006]&[1]:[Delivery Date].[Calendar].[Month].&[2006]&[12]ON ROWS

FROM [Adventure Works]

Next I created a calculated member whose purpose is to return the YTD or Running Total.  To accomplish this I coupled the SUM and YTD functions.  See below:

WITH MEMBER Measures.[YTD Internet Sales]

AS

SUM (YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

Using the YTD function I was able to obtain a set of members from the same level as the given member, which in this case was the [Delivery Date].[Calendar].CurrentMember.  I then used the SUM function to accurately calculate the YTD aggregations for the [Internet Sales Amount] measure.  The solution to the puzzle should resemble this:

WITH MEMBER Measures.[YTD Internet Sales]

AS

SUM (YTD([Delivery Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount])

SELECT

    NON EMPTY(

        {

            [Measures].[Internet Sales Amount],

            Measures.[YTD Internet Sales]

        }

    )ON COLUMNS,

    [Delivery Date].[Calendar].[Month].&[2006]&[1]:[Delivery Date].[Calendar].[Month].&[2006]&[12]ON ROWS

FROM [Adventure Works]

There are several ways to accomplish this, but this solutions does work.  Stay tuned for Puzzle #6.

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.