MDX Descendants of filtered Descendants

0
votes

I am new to MDX and Cubes.

I need to slice my data with descendants of the descendants of a given member having a particular attribute.

DIM1 is a dimension with Parent being its parent child hierarchy and it has an attribute Attr1.

Given the following parent tree. Members 601, 711, 712, and 811 must be used to slice. It is ok if any of their parents are returned, measure data only exists at the leaf level.

  Id | Parent | Attr1
   1 |        |   1
   2 |     1  |   3
 501 |     2  |   5
 601 |    501 |  11
 701 |    501 |  11
 711 |    701 |  15
 712 |    701 |  16
 810 |    501 |   7
 811 |    810 |  11

I am receiving the error "The DESCENDANTS function expects a member or set of a single hierarchy for the 1 argument. A set of multiple hierarchies was passed." when trying

WHERE 
  DESCENDANTS ( 
     { { DESCENDANTS ( [DIM1].[PARENT].&[501].CHILDREN ) } * { [DIM1].[Attr1].&[11] } } 
)
2
sql-serverssasmdx

2 Answers

0
votes

You can try using sub query for from clause instead of where condition.

e.g. 

SELECT 
  NON EMPTY { [Measures].[Sale Amt] } ON COLUMNS, 
  NON EMPTY  {[Dim1].[Attr].children} * {[Dim1].[Parent].children } on rows 
from 
(
  SELECT ( { [Dim1].[ParentH].[Parent].&[501], [Dim1].[ParentH].[Parent].&[501].children} ) ON COLUMNS 
  FROM [DBSF Test]
)
0
votes

Currently you have a cross-join inside the outer DESCENDANTS function - so this will create a set of multiple hierarchy tuples - hence the exception. If you move the cross-join outside does it still error?

WHERE 
  DESCENDANTS ( 
     DESCENDANTS ( 
       [DIM1].[PARENT].&[501].CHILDREN 
     ) 
  ) * { [DIM1].[Attr1].&[11] }