Various way to calculate running total from All Period to Currentmember

Various way to calculate running total from All period to currentmember

As MDX is a very vast and dynamic language we can write same query using multiple way
One of the regular demand in MDX is to sum running total from starting of time period to current time  member. We can do it using various way.



How ever I will try to get performance comparison and some more  test , I have put the samples here for the same


1.If you are using SQL 2005 or later version you can use null to simplify the expression

WITH MEMBER [Measures].[Sum from start]
AS
SUM ({NULL:[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]


2.Periodstodate function


WITH MEMBER [Measures].[Sum from start]
AS
SUM (PERIODSTODATE([Date].[Calendar].[All Periods].LEVEL,[Date].[Calendar].CurrentMember),
[Measures].[Internet Sales Amount])

SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]


3.opening period function

WITH MEMBER [Measures].[Sum from start]
AS
SUM ({OPENINGPERIOD ([Date].[Calendar].CurrentMember.level,[Date].[Calendar].[All Periods]):
[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount])

SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

4.Using FirstChild

WITH MEMBER [Measures].[Sum from start]
AS
SUM ({[Date].[Calendar].CurrentMember.Parent.FirstChild:[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

5.Using Item

WITH MEMBER [Measures].[Sum from start]
AS
SUM ({[Date].[Calendar].CurrentMember.level.Members.Item(0):[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]


6.Using Item again :-)

WITH MEMBER [Measures].[Sum from start]
AS
SUM ({[Date].[Calendar].CurrentMember.Parent.Item(0).Item(1):[Date].[Calendar].CurrentMember},
[Measures].[Internet Sales Amount])
SELECT {[Measures].[Internet Sales Amount],[Measures].[Sum from start]} ON 0 ,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]