Hierarchy is used more than once in the Crossjoin function

When I do this in Adventure Works:

SELECT NON EMPTY {
[Customer].[Customer Geography].[State-Province].Members *
[Customer].[Customer Geography].[City].Members
}
ON AXIS(0)
FROM [Adventure Works]

I get this: "The Customer Geography hierarchy is used more than once in the Crossjoin function."

How do I do the equivalent in an MDX statement that works? I want users to be able to drag multiple hierarchy levels onto the same axis. I have spyed on the MDX that BIDS is generating, and it seems to be cheating by not getting the states and citys back in the same MDX statement. It looks like its combining the results of two queries at run-time as the user expands nodes in the tree. Isn't there a better way?

Thanks,

Terry

Hi Terry,

Any reason why cross-joining the attribute hierarchies instead won't work for you, like:

SELECT NON EMPTY {

[Customer].[State-Province].[State-Province].Members *

[Customer].[City].[City].Members

}

ON AXIS(0)

FROM [Adventure Works]

|||

Hi Deepak,

Thanks for your reply. Our application allows users to drag-and-drop whatever they want, so in this instance I don't want to prevent the user from putting two hierarchy levels on the same axis. Maybe the solution is somehow use metadata to find the attribute names that built the hierarchy levels and use the attribute names behind the scenes in the crossjoin function? Is that possible?

|||You can use AMO to retrieve the necessary metadata to identify which attribute underlies a hierarchy level and its corresponding attribute hierarchy.