MDX Essentials: Basic Set Functions: The TopCount() Function, Part II - Page 2

Practice

Perform a Topcount() within the Context of Another Dimensional Level

Because we discussed syntax and other considerations that accompany the basic use of Topcount() in our previous article, we will move directly into the practice examples in this article. The examples are a bit more elaborate, and will require somewhat detailed explanations, as they involve the juxtaposition of TopCount() with other MDX functions. Consistent with our routine approach within the MDX Essentials series, we will use TopCount() within the context of meeting illustrative business needs for a group of hypothetical information consumers in the examples that follow.

Let's return to the MDX Sample Application as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

Let's assume, for our practice example, that we have been contacted by a group of information consumers within the FoodMart organization requesting some assistance with a new reporting requirement. The group, composed of analysts from the Finance and Accounting departments of the organization, wishes to be able to present 1998 Warehouse Profit for the three Warehouses with the highest Warehouse Profit in each of the States (the group is only concerned with U.S. operations from the perspective of this business need). We are told that the Warehouse cube, which houses the lion's share of the FoodMart Warehouse data, contains the information we need to meet the business requirement.

Working with the consumers, we draft a sketch of the proposed report layout, illustrated in Table 1, to confirm our understanding of the requirement.

The challenge in the requirement we have described lies within the fact that we are working within multiple levels within the Warehouse dimension to produce the requested results. We are, in effect, being asked to classify the three top Warehouse performers by a higher level in the Warehouse dimension (State Province). In order to do this, we will employ the TopCount() function as we have done in previous scenarios, but this time in conjunction with other MDX functions, to present the State information associated with each of the top performing Warehouses.

Let's construct part of the query to get started. First, we will construct the part with which we are familiar, from our previous article at least, the core TopCount() function.

-- MDX032-1-1, TopCount() and Descendants() Combination to retrieve "3 Most 
--      Profitable Warehouses in 1998"
SELECT
     {[Measures].[Warehouse Profit]}  ON COLUMNS,
     {TOPCOUNT(
          DESCENDANTS(
               [Warehouse].CURRENTMEMBER, [Warehouse].[Warehouse Name]
          ),3,[Measures].[Warehouse Profit])} ON ROWS
FROM
     [WAREHOUSE]
WHERE
      ([Time].[Year].[1998])

The Results pane is populated by Analysis Services, and the dataset shown in Illustration 1 appears.