I am trying to get the Top N sales for the customers for a datawarehouse
using the following MDX:

="SELECT {[Measures].[Net Sales],[Measures].[Qty Lb]} ON COLUMNS,
TopCount(ORDER({[CustomerSalesPerson].[Name].Members},[2004], DESC),"&
Parameters!TopCustomer.Value &") ON ROWS,
{[TimeByMonth].[2004].[1]:[TimeByMonth].[2004].[12]} ON PAGES FROM
[CustomerTopSales]"

having a parameter to determinate the Top N.

This works fine when my dimension (CustomerSalesPerson) has one level in the
report, but when i want to show two levels of my dimension, it counts Top N
considering both levels, like the following will be Top 5 when showing only 2
customers:

Customer 1
Sales Person 1
Sales Person 2
Customer 2
Sales Peron 3

How can i made a Top N using the first level of the dimension?

Any help

Thanks

First of all - don't use Order inside TopCount, because TopCount returns set
ordered by the expression, so you are going to lose performance on the inner
Order.
If you only want to do topcount on the first level of the dimension - you
can use

TopCount(CustomerSalesPerson.Levels(1).MEMBERS, ...

Also your dimension seems unusual - you have Sales Person level under
Customer ? Why not vice versa, or even better - have two dimensions -
Customer and Sales Person ?

--
==================================================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLog/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

"Carlos Krauss" <CarlosKrauss (AT) discussions (DOT) microsoft.com> wrote