Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 6, 2005

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

By William Pearson


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.

1.  Start the MDX Sample Application.

2.  Clear the top area (the Query pane) of any queries or remnants that might appear.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the Warehouse cube in the Cube drop-down list box.

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.


Warehouse Profit

Warehouse 1


(Highest Profit in CA)

Warehouse 2


(Next Highest Profit in CA)

Warehouse 3


(Third Highest Profit in CA)

Warehouse 1


(Highest Profit in OR)

Warehouse 2


(Next Highest Profit in OR)

Warehouse 3


(Third Highest Profit in OR)

Warehouse 1


(Highest Profit in WA)

Warehouse 2


(Next Highest Profit in WA)

Warehouse 3


(Third Highest Profit in WA)

Table 1: Results Dataset, with TopCount() Defining Columns

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.

5.  Type the following query into the Query pane:

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

6.  Execute the query by clicking the Run Query button in the toolbar.

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

Illustration 1: Results Dataset - Simple Use of the TopCount() Function

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM