## SQLDownSouth

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