Hi,
I'm a newbie at MDX. I am trying to generate a report that displays top 10
salespersons by revenue and by year. I begin with the revenue measure on the
column axis and a cross product of time and salesperson on the row axis.
I have to apply topcount() to the salesperson for every member of the
[time].[year] dimension. I use the generate function to repeat the resultant
set of topcount() for every member of the Year hierarchy. I however, don't
get the result I want.

I find that the generate function gets the result of topcount for the first
year and repeats that same set for every year. I see the same set of
salespersons repeated for every year when those salespersons may not be the
top selling ones for the year.
The MDX I am using is as follows:
SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS,
NON EMPTY {( [TimePeriod].[StartYear].[StartYear] *
GENERATE([TimePeriod].[StartYear].AllMembers,
TOPCOUNT([Partner].[Hierarchy].[Partner],10,[Measures].[Revenue])))} ON ROWS
FROM [ABC DW 1]
WHERE [Proposal Status].[Proposal Status].&[5.]

Would appreciate some help.
Thanks,
Supriya.

Hi Supriya,

You can try something like:

NON EMPTY Generate([TimePeriod].[StartYear].[StartYear],
[TimePeriod].[StartYear].CurrentMember *
TOPCOUNT([Partner].[Hierarchy].[Partner],
10, [Measures].[Revenue])) ON ROWS
FROM [ABC DW 1]
WHERE [Proposal Status].[Proposal Status].&[5.]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

This worked to a T, Deepak. Thanks very much.
Regards,
Supriya

"Deepak Puri" wrote: