Search code examples
ssasmdx

MDX custom consolidation: consolidate only last level descendants


I'm looking for a elegant MDX expression that sum values only of the elements of last level dimension:

I have a measure M , also I have a hierarchical parent - child dimension U that is non balanced tree:

R -> ( M = R1 + R2 = 157 )
..R1 ->  ( M = R11 + R12 = 150 )
...R11 ->   ( M=R111 = 100 )
.....R111 ->   M=100
...R12 ->  M = 50
..R2 -> M = 7

I have a set that contains some elements from this dimension:

S contains R11, R111, R12

Now I need to take, for a U.currentMember the M value (that is, the sum of last level descendants)

I have written this expression, it works but perhaps they are a more elegant way to write it:

with member measures.XX as
sum ( 
       intersect( 
            [S], 
            Except( 
              descendants( [U].currentMember ),
              existing( descendants( [U].currentMember ).item(0) )
            )                 
       ) 
   , 
   [M]
)    
select 
 measures.xx on columns
from [CUBE]
 where [U].[R]         

Note: This MDX dont run:

with member measures.XX as
sum ( 
       intersect( 
            [S], 
            descendants( [U].currentMember )
       ) 
   , 
   [M]
)    
select 
 measures.xx on columns
from [CUBE]
 where [U].[R]         

because return 250 insteat 150.

Right result is 150: R11 + R12 (because R111 is included in R11).

Bad result is: 250: '100' value is taked for twice R11 + R111.


Final Solution:

with member measures.XX as
sum(    
    intersect ( 
       descendants([U].currentMember,,leaves),
       [S]
    ) 
   , 
   [M]
)    
select 
 measures.XX on 0,
descendants( [Unitats].[Unitat].[All] ) on 1
   from [C]

Solution

  • Not sure what you want to calculate but let's assume [Member] is the member you want to evaluate :

    I'd use the descendants, filter and isLeaf MDX functions :

     Sum( 
       Filter( Descendants( [Member] ), isLeaf(Member_hierarchy.currentmember) )
       ,[M])
    

    You're adding all descendants including itself that are leafs (no children).