MDX: TopSum Function?

[G.OKeefe]

Another Question for Bill Pearson:

As I've stated elsewhere, I'd like to thank you for providing the number one "go to" resource for those of us that are learning MDX; your series goes so much further than the documentation, and is highly valuable in that it teaches functions through the use of practical examples, based upon a sample database that is accessible to anyone who has installed Analysis Services. I have a couple of questions about "pure MDX" (results I want to obtain through direct queries, versus obtaining them at the reporting layer of the solution - where performance is often negatively impacted to obtain these same desired effects, as you so clearly and consistently say in your articles)

I'm posting each separately to this forum - hope I'm in the right place for Bill to see this!

I've read your articles on TopCount and Bottomcount ... and derived a great deal of hands-on insight from them, I must say. I have a little trouble understanding TopSum however, and wondered if you could explain its use with the same degree of detail - and within your typical context of "practical business perspective"?

Thanks, again, as always.

George

[Island1]

George:

Sorry about the delay getting back on this one - I must have overlooked it somehow.

(I do have an article out on this in the coming weeks ... stay tuned, and I'll try to remind you when it's published.

The TopSum() function calculates the sum of a specified measure, evaluated from the perspective of a specified set, then sorts the specified set in descending order. TopSum() then returns the elements with the highest values whose total of the specified numeric expression is at least the specified value. TopSum() returns the smallest subset of a set whose cumulative total is at least the specified value, ordering The returned elements from largest to smallest.

So, as an Adventure Works cube example, if we wanted to retrieve the top-performing Resellers whose totals make up the top five million dollars in Reseller Sales, we could do so using TopSum() in a manner similar to this query:

/*
Example: Use TopSum() to retrieve the top-performing Resellers whose totals make up the top five million dollars in Reseller Sales for Calendar Year 2004
*/


SELECT

{[Measures].[Internet Gross Profit]} ON AXIS(0),

TOPSUM([Customer].[Customer Geography].[Country].[United States].CHILDREN,
2500000, [Internet Gross Profit])
ON AXIS(1)

FROM
[Adventure Works]

WHERE
([Date].[Calendar].[Calendar Year].&[2004])

HTH. Let me know if you need further ...

Bill

[G.OKeefe]

Thanks for the insight, Bill. I especially appreciate the "hands-on" examples!

George