MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
May 2, 2005
About the Series ...
This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.
For more information about the series in general, as well as the software and systems requirements for getting the most out of the lessons included, please see my first article, MDX at First Glance: Introduction to MDX Essentials.
Note: Current updates are assumed for MSSQL Server, MSSQL Server Analysis Services, and the related Books Online and Samples.
In this article, we will examine the TopCount() function, whose highest value lies in its capability to enable us to isolate best performers from among hundreds or thousands of fellow members. This ranking capability is critical in data analysis and decision support scenarios: In many business situations, we seek to report upon "best performers" for various reasons. TopCount() facilitates our doing so, allowing us to sort on a numeric value expression that we can provide. We can tell the function how many "top" members we wish to retrieve (say, the "top ten," or the "top twenty"), for a "custom-fit" approach, that matches our needs precisely.
In this article, we will introduce and overview the TopCount() function. We will first comment upon the general operation of TopCount(), and then we will: