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.