sqlsql-server-2012data-cube

Cross join same hierarchy columns

I have a SQL data cube with following hierarchy

enter image description here

I want to cross join Warehouse division and Code warehouse Desc. I wrote a MDX as follows

SELECT NON EMPTY 
{ [Measures].[Total  Value]} 
DIMENSION PROPERTIES CHILDREN_CARDINALITY, 
PARENT_UNIQUE_NAME ON COLUMNS, 
NON EMPTY 
{      
   [Combined].[Drill Down Path 4].[Warehouse Division].MEMBERS*   [Combined].[Drill Down Path 4].[Code Warehouse Desc].MEMBERS
} 
DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [InventoryAge]
WHERE ( [Calendar].[Report Days].[All Members].&[All].&[WantInReport].& [2].&[20141031] )

It gives me an error as follows

Query (13, 8) The Drill Down Path 4 hierarchy is used more than once in the Crossjoin function.

Can any body suggests a better way to do this

Please find the calender hierarchy

enter image description here

Solution

  • You don't need to crossjoin hierarchy (this is impossible) to do what you need. Just query the lowest level of it, you will get all parents also

    SELECT NON EMPTY 
{ [Measures].[Total  Value]} 
DIMENSION PROPERTIES CHILDREN_CARDINALITY, 
PARENT_UNIQUE_NAME ON COLUMNS, 
NON EMPTY 
{      
     [Combined].[Drill Down Path 4].[Code Warehouse Desc].allMEMBERS
} 
 DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [InventoryAge]
 WHERE ( [Calendar].[Report Days].[All Members].&[All].&[WantInReport].& [2].&[20141031] )

    PS. You might not be able to see them in SSMS query result viewer, but they will appear if you use query in cube browser or as dataset in SSRS/other tool