This is the last one in the series“Top to Bottom in MDX”. In the first part we have seen Topcount and Bottomcount functions and in the second part, Toppercent and Bottompercent (Toppercent and Bottompercent in MDX). In this part lets check out Topsum and Bottomsum.
Topsum
Like Toppercent, Topsum returns the topmost tuples in the set whose cumulative total hits the specified value by sorting the data in descending order.
select
[Measures].[Reseller Sales Amount] on columns,
topsum
(
[Reseller].[Reseller].[Reseller], --Set_Expression
2500000, --Value
[Measures].[Reseller Sales Amount] --Numeric_Expression
) on rows
from
[Adventure Works]
In the query we have specifies value of 2500000 for the second parameter. The query first sorts the Resellers by Sales Amount from largest to smallest and then return the top N Resellers till the sum of their Sales Amount is at least $2500000. In our example, the first two Resellers sold worth $1730956.37 (877107.19 + 853849.18) which is still less than $2500000. So third Reseller is added with Sales Amount $841908.77 which brings the total sum to $2572865.14and we got ourbest Resellers.
Bottomsum
This is almost as simple and works exactly opposite to Topsum. It returns the tuples with the smallest values whose cumulative total hits the specified value by sorting the data in ascending order. The following example will illustrates the same.
select
[Measures].[Reseller Sales Amount] on columns,
Nonempty(bottomsum
(
[Reseller].[Reseller].[Reseller], --Set_Expression
500, --Value
[Measures].[Reseller Sales Amount] --Numeric_Expression
)) on rows
from
[Adventure Works]
Again the nonempty() functionis added to eliminate null. The result showing is the worst eight Resellers with total sales of only $500.
Summary
Hope you have enjoyed the series. Below is the quick recap of what we have learned so far.
| Function | Description |
| Topcount () | Selects the top N tuples of a set by sorting a set in descending order. |
| Bottomcount () | Selects the bottom N tuples of a set by sorting a set in ascending order. |
| Toppercent () | Selects the tuples of a set corresponding to the top N% of all in set by sorting a set in descending order |
| Bottompercent () | Selects the tuples of a set corresponding to the bottom N% of all in set by sorting a set in ascending order. |
| Topsum () | Selects the top tuples of a set whose sum meets a threshold by sorting a set in descending order. |
| Bottomsum () | Selects the bottom N tuples of a set whose sum meets a threshold by sorting a set in ascending order. |
Regards
Amit Karkhanis
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
