Hi. I need to report on all 2004 sales by product. It sounds easy
enough, but the products are hierarchical and I have to report on
starting with the top level and working my way down to the last child
in the hierarchy, showing sales for each level.

So, here's my dilemma...

I can use the MDX query below to retrieve all 2004 sales (broken down
by month) by crossjoining the time dimension with my product hierarchy
dimension. Unfortunately, since the hierarchy consists of key values
and not descriptions, I need to also retrieve the descriptions (names
of the products). When I wrote my MDX query, I cross-joined the time
dimension with my product hierarchy, which was fine, but I couldn't
figure out how to also retreive the corresponding product description
without doing another cross-join (as shown below). There is a
one-to-one relationship between the hieararchy member and the
corresponding description member.

MDX query that gets the extra cross join:
SELECT { [Measures].[Amt Sold zero-filled], [Measures].[Num Sold
zero-filled] } ON COLUMNS,
{YTD([TimeDimension].[2003].[Quarter 4].[December]) *
[ProdHierarchy].[Level 04].ALLMEMBERS * [Prod
Description].[Description].ALLMEMBERS }
DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS
FROM [Sales]

As you see, I'm cross-joining too many things. I don't want to
cross-join the Product Description with the ProdHierarchy members. I
just want a one-to-one match. How do I do that? Once I figure this
out, I'll also want to retrieve the product ID, which is also a
one-to-one match, but I can add that once I get the methodology right.

By the way, my product hierarchy is a parent-child dimension, which
comes from my Products table. The Products table is keyed by ProdNum
and has a parent of ParentProdNum. The ProdNum is no value in the
report I'm building, so I need the Description and ProdID fields for
my report rows in place of the ProdNum values. That's why my MDX
query needs to retrieve the corresponding description info for my
hierarchy-based ProdNum values.