I am trying to write an MDX query that will return the top 10 products by
volumne, (lets just make it easy and say for all time) That's the easy
part. The other requirement is that I also know what "Collection" the
products is from. The product dimension is [All Products], [Collection],
[Name]

The query I have so far is:

SELECT { [Measures].[Count] } ON COLUMNS ,
{ TOPCOUNT( {Descendants( [All Products],[name]) }, 10, (
[Measures].[Count] ) ) } ON ROWS
FROM [PRODUCT_SALES]

This works great to find the top products across all collections, but I also
want the output to contain the Collection name that the product belongs to
as well......
Any Ideas????

TIA,
Scott

One option is to use a text calculated measure, like:

'Ancestor([Product].CurrentMember,
[Product].[Category]).Name'
SELECT { [Measures].[ProductCategory],
[Measures].[Count] } ON COLUMNS ,
{ TOPCOUNT( {Descendants( [All Products],[name]) },
10, ( [Measures].[Count] ) ) } ON ROWS
FROM [PRODUCT_SALES]

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

That worked! It has the product name first and then collection, but I can
deal with that.....

Thanks,
BTW, I had to add the "MEMBER" clause to the first line.... like "With
Member [measures]......etc


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote