MDX Function and Operator Reference - Professional SQL Server Analysis Services 2005 with MDX - page 1123

FRUIT VALUE

Strawberries 12

Cantaloupes 10

Peaches 8

Apples 8

Kiwis 8

Bananas 4

In this case, selecting the top three or bottom two fruits based on values will
cause an arbitrary choice to be made at the value of 8. The results are func-
tionally equivalent to Head (Order ( set, numeric value expression, BDESC),
index ) and Head (Order ( set, numeric value expression, BASC), index ).

Note that Essbase always removes tuples from the set whose numeric ex-
pression is NULL. If you want them included at some position, use Coales-
ceEmpty () in the numeric expression to convert the NULL to some value.

See also: TopSum (), BottomSum (), TopPercent (), BottomPercent ()

TopPercent ( set , percentage , numeric expression ) Returns: set Standard

BottomPercent ( set , percentage , numeric expression ) Returns: set Standard

TopPercent () returns the top percentage tuples of set, based on numeric ex-
pression if specified. The cells or expression are summed over the set, and
the top set of elements whose cumulative total of the numeric expression is at
least percentage is returned. Percentage is a numeric expression. For ex-
ample, using the sorted set of fruits and values, TopPercent (fruit, 50, Value)
will result in {Strawberries, Cantaloupes}. Strawberries is 24 percent of the
total, Cantaloupes 1 Strawberries is 44 percent of the total, and Peaches
would push the set over the 50 percent limit to 56 percent.

BottomPercent () behaves similarly, except that it returns the bottom set of
elements whose cumulative total from the bottom is less than the specified
percentage. TopPercent () returns elements ordered from largest to smallest