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 SQL Oracle DB2 MS Access MySQL » RESOURCES SQL Scripts & Samples Tips » Database Forum » Slideshows
 Database Journal |DBA Support |SQLCourse |SQLCourse2

## MS SQL

Posted Sep 5, 2006

# MDX Numeric Functions: The .Ordinal Function

By William Pearson

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.

MS SQL Archives

 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