Cross join same hierarchy columns

Question

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

That hierarchy looks a bit odd.... – Mitch Wheat Feb 05 '15 at 06:49 — Mitch Wheat, Feb 05 '15 at 06:49
what do you mean by that? Any suggestions – udaya726 Feb 05 '15 at 06:50 — udaya726, Feb 05 '15 at 06:50

score 2 · Accepted Answer · answered Feb 05 '15 at 08:25

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

I want to use this query in the SSIS. Its work fins when I select the last level — udaya726, Feb 05 '15 at 09:11
one more question how can I pass current date to the &[20141031] value — udaya726, Feb 05 '15 at 09:38

score 0 · Answer 2 · answered Feb 05 '15 at 09:47

0

You can pass last date in your set by using Tail(Existing [Calendar].[Report Days].[All Members].&[All].&[WantInReport].members, 1).item(0)

Or you can use Format(Now()), which gives you string representation of current system date. However, it depends on locale, so you probably need to remove dots/slashes. See here

answered Feb 05 '15 at 09:47

George

692
5
10

I used following query to get the value you mentioned. But it gives me an error with member [Measures].[Yesterday] as Tail(Existing [Calendar].[Report Days].[All Members].&[All].&[WantInReport].MEMBERS, 1).item(0) , FORMAT_STRING='yyyyMMdd' select { [Measures].[Yesterday]} ON Columns FROM [InventoryAge] – udaya726 Feb 06 '15 at 09:05
Could you please attach picture of your Calendar Hierarchy? In general, you get last date by this: Tail(Existing [Calendar].[Days].members, 1).Item(0) However, you structure seems different. To get its value in Measures, you need Tail(Existing [Calendar].[Days].members, 1).Item(0).memberValue – George Feb 06 '15 at 10:29

Cross join same hierarchy columns

2 Answers2