I have a single dimension with 3 levels:

1) Country (top)
2) Agent
3) Reviewer

I want to write an MDX query that will give me an output like this:

USA -> Agent 100 -> Jane Doe
USA -> Agent 200 -> William Smith
John Jones

I know I need a non-empty cross join, but I'm not sure what syntax to use
when attempting a join on different levels of the same dimension. I can see
the hierarchy just fine when I browse the dimension in AS 2000 and I really
want to replicate the layout via MDX.

All the samples I've found assume that cross joins are only done across
different dimensions.

Thanks in advance.

Alternate techniques, such as using the MDX Crossjoin function, may not
provide the desired results because non-related joins can occur. Since
the Country and agent and reviewer levels are within the same
dimension, they cannot be cross-joined. The values for the Agent &
Country columns are taken from the Agent & country levels of what I
named the Store dimension (since it was not provided), so both columns
are constructed as calculated members as part of the MDX query, using
the MDX Ancestor and Name functions to return the country & agent names
relative to your dimension (in this case, Store).

I dont know the name of your cube, so I used [Sales]. I also did not
know the specific name of the dimension (so I used Store), as I
mentioned above -- Also, you didnt mention what your measure name was
for your scenario, so I plugged in a fictious example named [Unit
Sales]. The key:

The MDX Descendants function is used to construct a set consisting of
only those members at the Reviewer Name level in the Store dimension.
Then, the MDX TopCount function is used to return only the topmost
reviewers based on the Unit Sales measure. The MDX Generate function
then constructs a set based on the topmost reviewers, following the
hierarchy of the fictious Store dimension that you outlined.

*//Replace the example dimension name Store below with the actual name
of the dimension. Also, replace the cube name with your actual cube
name.Also, if you want to see ALL, you will need to remove the Generate
and TopCount functions/braces and ().//*

WITH MEMBER
Measures.[Country Name]
AS
'Ancestor(Store.CurrentMember, [Country]).Name'
MEMBER
Measures.[Agent Name]
as
'Ancestor(Store.CurrentMember, [Agent]).Name'


SELECT
{Measures.[Country Name], Measures.[Agent Name], Measures.[Unit Sales]}

ON COLUMNS,
GENERATE(
[Country].MEMBERS,
TOPCOUNT(
DESCENDANTS(
[Store].CURRENTMEMBER,
[Store].[Reviewer]
),100,[Measures].[Unit Sales]
)
)
ON ROWS
FROM
Sales

This depends a little on what front end you are using to display the
results of the MDX. Reporting Services 2000 returns "flattened" rowsets
in which the ancestor information is available by default. So doing a
simple query with Reviewer.Members on the rows will give you access to
the other higher levels.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#ysdd9O$FHA.2264 (AT) tk2msftngp13 (DOT) phx.gbl>, ian-c-wright (AT) msn (DOT) com
says...