Wednesday, 17 September 2014

Calculating Running Total in MDX

WITH MEMBER [MEASURES].[Previous Year Frequency]
AS
([Measures].[Frequency], [Dimension Calendar].[Hierarchy_Year_Month_Date].PREVMEMBER)

MEMBER [MEASURES].[RunningTotal]
AS
[Measures].[Frequency] + [MEASURES].[Previous Year Frequency]

SELECT
     NON EMPTY
     {
        [Measures].[Frequency]
        , [MEASURES].[Previous Year Frequency]
        , [MEASURES].[RunningTotal]
     } ON 0
     , NON EMPTY
     {
        [Dimension Calendar].[Hierarchy_Year_Month_Date].ALLMEMBERS
     } ON 1
FROM (SELECT({[Dimension Calendar].[Hierarchy_Year_Month_Date].[Year Number].&[2013]}) ON 0
FROM [SampleCube])


No comments:

Post a Comment