Hi all,

ok, what I really want is the Sales Amount (from AdventureWorks) of the 10
best-selling products by Sales Amount. Sounds simple, but using the new MDX
subcube statements it is hard to make it work! This is the obvious solution
that I tried first:

select [Measures].[Sales Amount] on columns
from (SELECT TOPCOUNT([Product].[Product].Members, 10, [Measures].[Sales
Amount]) on columns
from [Sales Summary])

Doesn't work at all, I get the value for "all products".

OK, let's try it some other way:

select [Measures].[Sales Amount] on columns
from (SELECT HEAD(ORDER( [Product].[Product].Members, [Measures].[Sales
Amount], DESC), 10) on columns
from [Sales Summary])

Still no success. But here comes the funny part, this way 'round it does
work:

select [Measures].[Sales Amount] on columns
from (SELECT TAIL(ORDER( [Product].[Product].Members, [Measures].[Sales
Amount], ASC), 10) on columns
from [Sales Summary])

What is the difference between HEAD and TAIL here? Is there any place in BOL
that would have told me which MDX function can be used in a subcube
statement and which one cannot?

By the way: I know several other ways to rewrite the query in MDX, so that
it would work using TOPCOUNT (and be much faster), but I'm trying to create
a named set for the "Top 10 Products" that can be used in the graphical MDX
query builder in Reporting Services, so that my report designers don't have
to know MDX! If you check it out with AdventureWorks, the named set "Long
Lead Products" that's already there does work in a subcube statement, the
other one called "Core Product Group" doesnt. Why, oh why?

Markus.

I think the key issue here is that you are specifying a dimension and
hierarchy, but not the level in your TOPCOUNT set. The [Product]
hierarchy includes an all member, if you get the [Product] level of the
[Product] hierarchy of the [Product] dimension, it does not include the
All member.

eg.

select [Measures].[Sales Amount] on columns
from (SELECT TOPCOUNT([Product].[Product].[Product].Members, 10,
[Measures].[Sales Amount]) on columns
from [Sales Summary])

Another possible solution is to use a good old "WITH MEMBER..."

eg.

WITH MEMBER Measures.Top10Sales as 'SUM(TOPCOUNT([Product].[Product].
[Product].Members, 10, [Measures].[Sales Amount]),Measures.[Sales
Amount])'
select {[Measures].[Top10Sales]} on columns
FROM [Sales Summary]

There was not much noticeable difference between the 2 queries, the
first query did hit the cache twice, where as the second only hit it
once, so the second is probably minutely faster.

Your TAIL query works because the All member always displays as the
first member of the hierarchy because you did not specify to break the
hierarchy when sorting. If you wanted to break the TAIL query you would
just have to use BASC as the sort option to see the same behaviour as
the HEAD and TOPCOUNT queries.

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

In article <eYWgozNSGHA.4300 (AT) TK2MSFTNGP14 (DOT) phx.gbl>,
mraatz_doesnt_want_spam (AT) ixto (DOT) de says...

Hi Darren,

Wow, that was a quick response indeed, and it solved my problems 100

percent!!! It's also such a logical explanation, makes it easy to

understand why it didn't work...

Sorry it took such a long time for me to thank you for your posting, but

I had to spend the last days redesigning and upgrading my cubes, now

that the "Top Products"-problem is solved and our report designers can

actually start designing their reports with the MDX query builder. Looks

like my cubes (and the reports based on them) will be a real success

now!

So thanks very much again,

Yours

Markus.

"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> schrieb im Newsbeitrag
news:MPG.1e83fda62bb218b69898bd (AT) news (DOT) microsoft.com...