Cross joins across user defined hierarchies aren't supported .?

Question

 

 

Hello all,

I have some confusion on crossjoin function within MDx.

while I try to crossjoin the different level sets of same Hierarchy. It shows error as

For example.

‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’

select {

{[Customer].[Customer Geography].[Country].&[United States]}*

{[Customer].[Customer Geography].[State-Province].members}} on 0

FROM [Adventure Works]

WHERE Measures.[Internet Sales Amount]

Cannot we Cross joins across user defined hierarchies ,or they aren't supported .?

Coz I really need to implement as above MDx within my real Cube.

I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.

with member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]

select {

{[Customer].[Country].[United States ]}*

{[Customer].[Customer Geography].[State-Province].members}} on 0

FROM [Adventure Works]

WHERE Measures.[Internet Sales Amount]

 

 

This is just an example ,so Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

 

 

Thanks,

 

 Anil Maharjan 

 

 

 

Answer 1

Hi Anil,

No, you can't use two sets containining members from the same user hierarchy in the Crossjoin function. When you're working with a single hierarchy you need to use fuctions like .Children and Descendants() to do what you want. So something like this will give you all the states in the USA:

select {

[Customer].[Customer Geography].[Country].&[United States].CHILDREN on 0

FROM [Adventure Works]

WHERE Measures.[Internet Sales Amount]

HTH,

Chris

---

Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

Answer 2

Hello Chris,

Thanks for your response ,but the solution is not that what I am looking for. I simply need to crossjoin the tow sets containing members from same user define hierarchy but sad that we can’t do it.

 I will try alternative solution then ,but really thanks for your response because I am just researching a lot on this.

I simply need like this within from same user defined Hierarchy.It's just an example by using user define member of different attribute  i can display as below but the value doesn't match or valid.

with member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]

member [Customer].[State-Province].[Alabama ]as [Customer].[Customer Geography].[State-Province].&[AL]&[US]

select

{Measures.[Internet Sales Amount]} on 0,

{

{[Customer].[Country].[United States ]}*

{[Customer].[State-Province].[Alabama ]}*

{[Customer].[Customer Geography].[Postal Code].members}} on 1

FROM [Adventure Works]

 

 

Country	State-Province	Postal Code(can be top 10)	Internet Sales  Amount	Other Measures
United States	Alabama	2015	$9,389,789.51	111
		2450	$9,389,789.51	222
		-----	-----	- ----

 

Regards,

Anil Maharjan

 

Answer 3

Is the reason you want to crossjoin these two sets because you want to display two columns on the left hand side of your report, one containing the Country America and the second a list of all the States in America? If so, then this is an issue with formatting your resultset rather than with MDX. What tool are you using to display the results of this query? Why does crossjoining the sets from the Country and the State hierarchy not give you what you want?

Chris

---

Blog: http://cwebbbi.wordpress.com/ Consultancy: http://www.crossjoin.co.uk/

Answer 4

Hello Chris,

Well you are right I need to display the 3 columns with sets containing members from same user define hierarchy on my left side and that three columns are from same user defined Hierarchy .The above MDx is just as an example I am trying to solve such case within my real cube. It seems this is an issue with formatting on my result set rather than with MDX as you say .

So, I had figure out alternative solution for this by displaying as ‘Multi Tone Tiered’ Grid format and simply know that we cannot crossjoin on the tow sets containing members from same user define hierarchy.

 So thanks a lot for your help and I am also following your blog’s which are really great blogs for the developers who are in the field of BI mostly.

Regards,

Anil Maharjan

Answer 5

Hi Anil,

I understand that you want this from the same user defined hierarcies, but it's not possible. Another way to achieve this is directly use the attributes.

Select [Measures].[Internet Sales Amount] ON COLUMNS,
([Customer].[Country].[Country],
[Customer].[State-Province].[State-Province]) ON ROWS
FROM [Adventure Works]

Result for the above query,

Hope it helps!

---

Thameem

Answer 6

Consider using DMX to flatten the MDX hierarchy

http://blogs.msdn.com/b/jamiemac/archive/2008/03/10/unwinding-mdx-flattening-semantics-with-dmx.aspx

Answer 7

Hello Thameem,

I have already viewed the structure of Customer Dimension from Adventure Works cube and there is already an separate attribute as country,state-Province so that we can obtain as you said and had already figured it out too.

Also , i had posted in BIDN too as link 

http://www.bidn.com/blogs/Anil/ssas/2202/cross-joins-across-user-defined-hierarchies-aren-t-supported

but in my case I haven't created separate attribute so I need to create some dummy/hidden attributes for that particular dimension in other to obtain the solution for my case which is alternative solution.

But thanks for your response

Anil

Answer 8

Hi Anil,

The requirement you want is to show different hierarchy level in different columns retrieving from the queries, the matter of how many columns are displayed are somewhat a client issue, the convention of most clients is to put each hierarchy level in it's own column. If you use SSRS. Excel 2007 & 2010, by default, different hierarchy level will show on different columns.

Thanks,
Challen Fu

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 9

Hi Anil,

I am into same situation, I need to cross join on same user defined hierarchy. Could you please let me the solution you have implemented?

Thanks,

Praveen