The TopCount() Function
According to the Analysis Services
Books Online, the TopCount() function
returns a specified number of items from the topmost members of a specified
set, optionally ordering the set first." The TopCount() function
stands out as an excellent general example of the potential power of MDX. We
specify three parameters, a set, a count, and a measure,
in most cases, and TopCount() returns the number of top performers (or "top
anything," in effect), based upon our input.
As we shall see, TopCount() sorts
the set we specify by another expression we provide within the function, thus
breaking the natural hierarchy of the set. The basis of sorting by TopCount()
closely resembles that used by the Order() function. We should
therefore keep in mind that, in the absence of a specified sort, the function
will institute a sort based upon the default member.
NOTE: For information surrounding the Order()
function, see my article Basic
Set Functions: The Order() Function,
a member of the MDX Essentials series at Database Journal.
We will examine the syntax for the
TopCount() function, and then look at its behavior based upon parameter input
we might provide. Next, we will undertake practice examples constructed to
support hypothetical business needs that illustrate uses for the function.
This will allow us to activate what we explore in the Discussion
and Syntax sections, by getting some handson exposure in
creating expressions that leverage the function. Our next article will build
upon the basics we expose here, and leverage TopCount() within more
sophisticated examples of business usage.
Discussion
To restate our initial explanation of its operation, the TopCount()
function sorts a set based upon a numerical expression we
provide, and then picks the top (whatever number we provide in the Count
parameter of the function) items in that set, based upon the rank order
established by the function. The frequent requirement to examine a subset of
members at the top of the general set is easily answered by the TopCount()
"sort, and then pick this number of members from the top down"
approach.
Let's look at some syntax illustrations to further clarify
the operation of TopCount().
Syntax
Syntactically, the set upon
which we seek to perform the TopCount() operation is specified within
the parentheses to the right of TopCount, a common arrangement within
MDX functions, as we have seen in our previous articles. The syntax is shown in
the following string.
TopCount(&Lt&Lt Set >>, &Lt&Lt Count >> [,&Lt&Lt Numeric Expression >>])
We follow <<Set>>, the set
specification, with a comma, which is followed by <<Count>>, the numeric
expression we provide to dictate the number of "top" members we wish for
the function to return. <<Count>> is, in turn, followed by a numeric
expression whereby we specify the "sort by" criteria. As we have
mentioned, TopCount() always breaks the natural hierarchy of the set
 due to the fact that the set is sorted upon the third argument we
provide as <<Numeric Expression>>, before returning the top
(specified number of) members from that sort.
The items returned by TopCount()
are limited to the << Count >> input that we provide, even
in the case of "ties" in the <<Numeric Expression>> value.
This may be important to consider when evaluating the data retrieved by the
function, in addition to default behavior of the function when no sort criteria
(in the form, again, of the <<Numeric Expression>>) is
provided.
The following example
expression illustrates the use of the TopCount() function, within a simple
business context. Let's say that a group of information consumers within the
FoodMart Planning organization wishes to see the top ten Warehouse
Cities for operating year 1997, based upon total Warehouse Profit
for each city.
The basic TopCount()
function specifies the "top ten Warehouse Cities" (with the
number "10" as the Count specification, and [Warehouse].[City].Members
as the Set specification, of the function). TopCount()
assembles the top ten members from the perspective of Warehouse Profit
(the Numeric Expression upon which the complete set of Warehouse
Cities will first be sorted by the function). The function with arguments is
represented in the ON ROWS line of the following query:
SELECT
{[Measures].[Warehouse Profit]} ON COLUMNS,
{TOPCOUNT([Warehouse].[City].Members, 10,
[Measures].[Warehouse Profit])} ON ROWS
FROM
[WAREHOUSE]
WHERE
([Time].[Year].[1997])
Assuming that we placed the TopCount() function within
the query as constructed above, our returned dataset would resemble that shown
in Table 1.

Warehouse Profit

Salem

17,041.57

Tacoma

16,725.63

Los Angeles

12,789.25

San Diego

12,536.28

Bremerton

12,063.17

Seattle

12,021.58

Spokane

5,835.46

Beverly Hills

5,674.85

Yakima

5,635.98

Portland

4,186.58

Table 1: Results Dataset, with TopCount() Defining Columns
As we expected, the top ten Warehouse Cities are
returned, having been sorted by Warehouse Profit, and having had the top
ten values in that sort isolated as we requested in the function.
As is the case with many MDX functions, TopCount()
can be used to deliver sophisticated analysis datasets, particularly when we
employ it along with other MDX functions. We will practice the use of TopCount()
in the section that follows, building from a simple example, similar to
that which we saw above, to a scenario where we apply the function, in
conjunction with a calculated member, to reveal an additional analytical
perspective. In our next article, we will examine even more sophisticated
applications of the function, combining its use with that of other functions we
have explored in articles of the series.