The query below returns a set of Products with the lowest values whose cumulative Sales Amount total is less than or equal to 10,000. Notice the BottomSum function returns products with empty values.
|
|
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
{BOTTOMSUM([Product].[Product].[Product].Members, 10000, [Measures].[Sales Amount])} ON ROWS
FROM [Adventure Works]
|
|
|
|
|
|
|
The following query returns a non empty set of Products with the lowest values whose cumulative Sales Amount is less than or equal to 10,000.
|
|
SELECT {[Measures].[Sales Amount]} ON COLUMNS,
BOTTOMSUM(FILTER([Product].[Product].[Product].Members, NOT ISEMPTY([Measures].[Sales Amount])), 10000,[Measures].[Sales Amount]) ON ROWS
FROM [Adventure Works]
|
|
|
|
|
|
|