Hello all,
It’s not appropriate to post as this a blog but it might help to most of us in field of BI having similar kind of confusion /issue.
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]
Though while looking on Adventure Works Cube ,it has attribute as Country, State-Province, and others along with the User define Hierarchy as Customer Geography with in a Customer Dim.
So while we crossjoin in the members of attribute of same Customer dimension it gives result
SELECT
{ {[Customer].[Country].[United States]}*
[Customer].[State-Province].Members
} ON 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
But similarly, while we try to crossjoin with in the attribute members of user define Hierarchy from Customer Geography it gives error.
Does that mean we need to create some dummy/hidden attributes for that particular dimension in other to obtain the solution for my case. ?-may be this is the solution
Does this mean we cannot crossjoin across user defined hierarchies?
It is just an example ,I simply need like this within from same user defined Hierarchy. By defining as user define calculated member of different attribute/dimension I am able to obtain the output as below but the values doesn’t valid or match.
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 members) | Internet Sales Amount | Other Measures |
| United States | Alabama |
2015 |
$9,389,789.51 |
111 |
|
2450 |
$9,389,789.51 |
222 | ||
|
2010 |
$9,389,789.51 |
333 |
Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.
I also posted on MSDN as link
Thanks,
Anil Maharjan