http://www.sqlservercentral.com/blogs/sqldownsouth/2010/08/10/mdx-puzzle-_2300_5-_2D00_-solution/

Printed 2014/04/20 01:43PM

MDX Puzzle #5 - Solution

By Patrick LeBlanc, 2010/08/10

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.