Running total in MDX

Here is a sample on how you can get running total in MDX. The query below gives sales and running total for january 2011 per date.

WITH MEMBER [Measures].[Running Total] AS
  Aggregate (
    { [Date].[Calendar].CurrentMember.Parent.FirstChild : [Date].[Calendar].CurrentMember },
    [Measures].[Internet Sales Amount]
  )
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Running Total] } ON 0,
[Date].[Calendar].[Month].&[2011]&[1].Children ON 1
FROM [Adventure Works]

And the result will look like this:

Runningtotal MDX 1

A query for month level can look like this:

WITH MEMBER [Measures].[Running Total] AS
  Aggregate (
    { [Date].[Calendar].CurrentMember.Parent.FirstChild : [Date].[Calendar].CurrentMember },
    [Measures].[Internet Sales Amount]
  )
SELECT
{ [Measures].[Internet Sales Amount], [Measures].[Running Total] } ON 0,
[Date].[Calendar].[Calendar Quarter].&[2011]&[1].Children ON 1
FROM [Adventure Works]

This will look like this

Runningtotal MDX 2

Because the parent level of month is quarter you will only get 3 months. In the cubes I make for customers I always have hierarchies for “Year – Month – Date” and then it should not be any problem 🙂

Please note that this sample has to be run against a multidimensional instance of Analysis Services.

Advertisements