CustomRollupColumn and aggregates

• kaimu_boy

  Grasshopper

  Points: 15

  More actions

  September 28, 2008 at 2:18 am

  #193750

  How to Sets CustomRollupColumn's Express to get data as follow:

  DimItem

  Year month week ItemName

  2008 1 1 100.10

  2008 1 2 100.20

  2008 1 3 100.40

  2008 1 4 100.90

  2008 1 sum 100.10

  2008 2 1 100.50

  2008 2 2 100.20

  2008 2 3 100.40

  2008 2 4 100.90

  2008 2 sum 100.50

  2008 sum 100.10

  I use the express as follow, but the cell show null

  case

  when [Date].[CalendarDate].CurrentMember.Level.Ordinal = 0

  then

  null

  when [Date].[CalendarDate].CurrentMember.Level.Ordinal = 1

  then

  ([Measures].[Amount],[AmountItem].[FinanceSubjectItem].&[1],[Date].[CalendarDate].CurrentMember.FirstChild.FirstChild.FirstChild)

  when [Date].[CalendarDate].CurrentMember.Level.Ordinal = 2

  then

  ([Measures].[Amount],[AmountItem].[FinanceSubjectItem].&[1],[Date].[CalendarDate].CurrentMember.FirstChild.FirstChild)

  when [Date].[CalendarDate].CurrentMember.Level.Ordinal = 3

  then

  ([Measures].[Amount],[AmountItem].[FinanceSubjectItem].&[1],[Date].[CalendarDate].CurrentMember.FirstChild)

  else

  ([Measures].[Amount],[AmountItem].[FinanceSubjectItem].&[1],[Date].[CalendarDate].CurrentMember)

  end

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  September 28, 2008 at 7:10 pm

  #878229

  You'd get better help quicker if you'd follow the methods in the article in the link in my signature below... 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)