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.

Hierarchy Equivalent of a Crossjoin

I want to pull back a cellset with two levels of a hierarchy on the same dimension. Using AdventureWorks as an example, let's say I want "Account Level 01" and "Account Level 02" both on the rows dimesion.

When I send MDX like this:

SELECT

{[Account].[Accounts].[Account Level 01].Members * [Account].[Accounts].[Account Level 02].Members}

DIMENSION PROPERTIES MEMBER_TYPE ON AXIS(0) FROM [Adventure Works]

I get this error message:

The Accounts hierarchy is used more than once in the Crossjoin function.

What MDX would I need in this situation? I've tried spying on the MDX generated by BIDs, but it uses so many temporary SETs that it's nearly impossible to follow.

Thanks for any help,

Terry

Terry,

If my understanding of what you are trying to do is correct, one possible solution would be to use the "Generate()" function:

Generate([Account].[Accounts].[Account Level 01].Members,

{[Account].[Accounts].CurrentMember,[Account].[Accounts].CurrentMember.Children})

HTH,

Steve