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.
Overview
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:
- Examine the syntax surrounding the function;
-
Undertake illustrative examples of the uses of the function in
practice exercises; -
Briefly discuss the results datasets we obtain in the practice
examples.
The .Ordinal Function
Introduction
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.
Discussion
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.
Syntax
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:
<<Level_Expression >>.Ordinal
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.
