Returns an ascending sorted set which cumulative sum is more or equal than the specified 'sum' value.
In our example we return the first two members [1],[2] as their value is below the requested 2 of the total and with one member less it is not enough to reach 2.
Query
WITH
MEMBER [Geography].[Geo].[1] AS 1.0
MEMBER [Geography].[Geo].[2] AS 2.0
MEMBER [Geography].[Geo].[3] AS 3.0
SELECT
BottomSum( {[Geography].[Geo].[3],[Geography].[Geo].[2],[Geography].[Geo].[1]},2, [Measures].[Amount] ) ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Geography].[Geo].[1] AS 1.0
MEMBER [Geography].[Geo].[2] AS 2.0
SELECT
{[Geography].[Geo].[1],[Geography].[Geo].[2]} ON 0
FROM
[Sales]
Assertion : MDX Equals
WITH
MEMBER [Geography].[Geo].[1] AS 1.0
MEMBER [Geography].[Geo].[2] AS 2.0
MEMBER [Geography].[Geo].[3] AS 3.0
SELECT
// exact match
BottomSum( {[Geography].[Geo].[3],[Geography].[Geo].[2],[Geography].[Geo].[1]},3, [Measures].[Amount] ) ON 0
FROM
[Sales]
Members/Tuples of the set with empty values are included into the returned set. They count as 0 values.
Query
WITH
MEMBER [Geography].[Geo].[E] AS NULL
MEMBER [Geography].[Geo].[1] AS 1.0
MEMBER [Geography].[Geo].[2] AS 2.0
MEMBER [Geography].[Geo].[3] AS 3.0
SELECT
BottomSum( {[Geography].[Geo].[E],[Geography].[Geo].[1],[Geography].[Geo].[2],[Geography].[Geo].[3]},2, [Measures].[Amount] ) ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Geography].[Geo].[E] AS NULL
MEMBER [Geography].[Geo].[1] AS 1.0
MEMBER [Geography].[Geo].[2] AS 2.0
SELECT
{[Geography].[Geo].[E],[Geography].[Geo].[1],[Geography].[Geo].[2]} ON 0
FROM
[Sales]
If the specified 'sum' value is not reached then all members will be returned, including empty cells.
In this case, the function behaves as the Order(set,numeric2,BASC) function.
Query
WITH
MEMBER [Geography].[Geo].[1] AS 1.0
MEMBER [Geography].[Geo].[2] AS 2.0
MEMBER [Geography].[Geo].[3] AS 3.0
SELECT
BottomSum( {[Geography].[Geo].[1],[Geography].[Geo].[2],[Geography].[Geo].[3]}, 100, [Measures].[Amount] ) ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Geography].[Geo].[1] AS 1.0
MEMBER [Geography].[Geo].[2] AS 2.0
MEMBER [Geography].[Geo].[3] AS 3.0
SELECT
{[Geography].[Geo].[1],[Geography].[Geo].[2],[Geography].[Geo].[3]} ON 0
FROM
[Sales]
This function returns at least one member even if its value is higher than specified 'sum' value.
Query
WITH
MEMBER [Geography].[Geo].[1] AS 1.0
MEMBER [Geography].[Geo].[2] AS 2.0
MEMBER [Geography].[Geo].[3] AS 3.0
SELECT
BottomSum( {[Geography].[Geo].[1],[Geography].[Geo].[2],[Geography].[Geo].[3]}, 0.2, [Measures].[Amount] ) ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Geography].[Geo].[1] AS 1.0
SELECT
{[Geography].[Geo].[1]} ON 0
FROM
[Sales]
BottomSum makes no difference with duplicated members. Our example is selecting twice the [Geography].[Geo].[1] calculated member.
Query
WITH
MEMBER [Geography].[Geo].[1] AS 1
MEMBER [Geography].[Geo].[2] AS 2
SELECT
BottomSum( { [Geography].[Geo].[1], [Geography].[Geo].[2], [Geography].[Geo].[1] }, 2, [Measures].[Amount] ) ON 0
FROM
[Sales]
Result
Assertion : MDX Equals
WITH
MEMBER [Geography].[Geo].[1] AS 1
SELECT
{ [Geography].[Geo].[1], [Geography].[Geo].[1] } ON 0
FROM
[Sales]