BottomSum

The MDX BottomSum function for Essbase returns the smallest possible subset of a set for which the total results of a numeric evaluation are at least a given sum. Elements of the result set are listed from smallest to largest.

Syntax

BottomSum ( set, numeric_value_expression, numeric_value_expression )

Parameters

set: The set from which the lowest-summing elements are selected.
numeric_value_expression1: The given sum (see MDX Grammar Rules).
numeric_value_expression2: The numeric evaluation (see MDX Grammar Rules).

Notes

If the total results of the numeric evaluation do not add up to the given sum, an empty set is returned.
This function ignores negative and missing values.

Example

The following query selects Qtr1 and Qtr2 sales for the lowest selling products in Qtr1 (where Sales totals at least 10000).

SELECT
 {[Year].[Qtr1], [Year].[Qtr2]} 
ON COLUMNS,
 {
  BottomSum(
  [Product].Members, 10000, [Year].[Qtr1]
  ) 
 }
ON ROWS
FROM Sample.Basic
WHERE ([Measures].[Sales])

This query returns the grid:

Table 4-42 Output Grid from MDX Example

(axis)	Qtr1	Qtr2
200-40	2807	2922
100-30	3187	3182
400-30	3763	3962
300-20	4248	4638