BOTTOMCOUNT MDX function - TheHeatons

Hi,

I use the following query to retrieve the top 5 sales execs

SELECT MEASURES.Members ON COLUMNS,BOTTOMCOUNT({[Region].[Account
Exec].MEMBERS},5,MEASURES.[Gross Sales]) on Rows from Sales WHERE
(Period.[This MTD])

I wanted to also retrieve the bottom 5 so I replace TOPCOUNT with
BOTTOMCOUNT but this returned non empty values.

I have been trying in vain to insert the NON EMPTY function but am
having no success. I either get an error (depending on where it is
put) or the query returns no records.

Any help would be appreciated

David

BOTTOMCOUNT MDX function - Deepak Puri

Have you tried:

SELECT MEASURES.Members ON COLUMNS,
BOTTOMCOUNT(NonEmpty([Region].[Account Exec].MEMBERS,
{MEASURES.[Gross Sales]}),
5, MEASURES.[Gross Sales]) on Rows
from Sales
WHERE (Period.[This MTD])


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

BOTTOMCOUNT MDX function - TheHeatons

Hi deepak,
I havent tried that.  and will try on monday.

what is the purpose of adding the additional to the [Gross Sales]
Measure?

Thanks for you assistance

David

BOTTOMCOUNT MDX function - TheHeatons

Deepak ,

I have just tried your suggestion but I get a 'Token is not valid'
error.

Any suggestions?