MDX Numeric Functions: The .Ordinal Function
September 5, 2006
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 lesson, we will expose another useful function in the MDX toolset, the .Ordinal function. The general purpose of the .Ordinal function is to return the ordinal value of a specified dimensional level. The .Ordinal function is particularly useful within scenarios where we employ it with the IIF() function to determine the position of a level within a dimensional hierarchy for various reasons, as we shall see in our practice session.
The .Ordinal function can be leveraged in activities that range from generating simple lists to supporting sophisticated conditional calculations and presentations. We will introduce the function, commenting upon its operation and touching upon the creative sorts of effects that we can employ it to deliver. As a part of our discussion, we will:
The .Ordinal Function
According to the Analysis Services Books Online, the .Ordinal function "returns the zero-based ordinal value associated with a level." .Ordinal has many applications, and, as is the case with many MDX functions, pairing it with other functions allows us to leverage its power. As an illustration, we often use .Ordinal with the IIF() logical function; we will see an example of this combination within our practice exercises later.
We will examine the syntax for the .Ordinal function after a brief discussion in the next section. We will then explore some of the "conditional logic leverage" it offers the knowledgeable user, within practice examples constructed to support hypothetical business needs. This will allow us to activate what we explore in the Discussion and Syntax sections, where we will get some hands-on exposure in creating expressions that employ the .Ordinal function.
To restate our initial explanation of its operation, the .Ordinal function, when acting upon a level expression, returns the zero-based index of the level expression to which it is appended with the period (".") delimiter. .Ordinal can be used for a great deal more than the support of simple lists of level indices, as we have intimated. When coupled with other functions, as we shall see, we can leverage .Ordinal to deliver a wide range of analysis and reporting utility.
Let's look at some syntax illustrations to further clarify the operation of .Ordinal.
Syntactically, in using the .Ordinal function to return the associated level value, the level upon which we seek to apply the function is specified to the left of .Ordinal. The function takes the level expression to which it is appended as its argument, and returns a zero-based value for the level specified. The general syntax is shown in the following string:
Putting .Ordinal to work is straightforward. When using the function to return the value of the level with which it works, we simply append it to the right of the level under consideration. As an example, within a query executed against the sample Adventure Works cube, for a dimension named Sales Territory (with a hierarchy of the same name), with three levels, named Sales Territory Group, Sales Territory Country, and Sales Territory Region, the following pseudo-expression:
[Sales Territory].[Sales Territory].[ Sales Territory Group].[North America].ORDINAL
returns 1, the zero-based value (or index) of the Sales Territory Group level (the top Sales Territory level itself or "All Groups" is level 0). As another example, the following:
[Sales Territory].[Sales Territory].[ Sales Territory Region].[Central].ORDINAL
returns 3, the numeric value of the Sales Territory Region hierarchical level. As is probably obvious, the .Ordinal function can be best leveraged by combining it with other functions, particularly "relative" functions, to generate lists of names, and so forth, as we shall see in short order.
NOTE: For information on several of the "relative" functions, see my article MDX Member Functions: "Relative" Member Functions, within the Database Journal MDX Essentials series.
We will practice some uses of the .Ordinal function in the section that follows.