MDX sum for year period

• Johan Brattås

  SSC Enthusiast

  Points: 123

  More actions

  February 7, 2012 at 5:36 am

  #264048

  Hi, I'm working on upgrading a calculated member from a working SQL 2000 cube to SQL 2008.

  I have a time dimension with Date as primary key. The dimension has two hierarchies - Year (Year only) and Period (Period consists of Month and Day)

  The calculated member looks like this:

  with member [Measures].[SumYear] as

  IIF([Time].[Year].Currentmember IS [Time].[Year].[Year].members(0) ,0,

  SUM({[Time].[Year].[Year].members(0) : [Time].[Year].Currentmember.Prevmember},

  [Measures].[Sales]),

  Format_String = 'Standard'

  Select {[Measures].[SumYear]} on 0,

  {[Time].[Period].Members} on 1

  From

  [Cube]

  When I run this in SQL 2000 MDX Sample application I get the same sum on all rows, regardless of whether it's a Day member, Month member or All.

  Running the query in SQL 2008 SSMS I get the correct sum on All, but different values on Month and Day members.

  I've tried experimenting with PeriodsToDate, but it doesn't give me the result I want - which is the total Sales of all previous years from inception to last year. It's used in combination with a PeriodsToDate - YearToDate if you will.

  Does anyone have any suggestions as to how this can be fixed?