CustomRollupColumn and aggregates

  • 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

  • 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)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply