none
SUM() a measure for the currentmember

    Question

  • I have two dimensions [D projects] and [D services]. Each project can include one or more services. the fact [F Cost] contains the cost of a service in a project ... that simple.

    I want to generate a simple report like this:

    Project Service service cost project cost
    P1 S2 12 30
    P1 S5 7 30
    P1 S11 11 30
    P4 S5 8 25
    P4 S7 7 25
    P4 S8 5 25
    P4 S11 5 25

    so I wrote a MDX query like this:

    with member [Measures].[project cost] as sum([D porject].[project code].currentmember,[Measures].[service cost])

    select {[Measures].[service cost],[Measures].[project cost]} on 0,

    nonempty(([D project].[project code].children, [D Services].[service code].members),[Measures].[service cost]) on 1

    from [F cost]

    but I got this:

    Project Service service cost project cost
    P1 S2 12 12
    P1 S5 7 7
    P1 S11 11 11
    P4 S5 8 8
    P4 S7 7 7
    P4 S8 5 5
    P4 S11 5 5

    how can I rewrite the query to get the desired?

    Saturday, June 14, 2014 10:56 AM

Answers