Business Intelligence Architect Bill Pearson introduces the MDX .Ordinal function, as a means for generating lists and for conditionally presenting calculations.  He also demonstrates the use of the function in creating datasets to support report parameter picklists.

In SQL Server Analysis Services (SSAS), The MDX .Ordinal function is useful within many activities, from generating simple lists to supporting sophisticated conditional calculations and presentations, or even supporting parameter picklists. The purpose of the .Ordinal function is to return the ordinal value of a specified dimensional level. The .Ordinal function is particularly useful in combination with other functions such as the IIF() logical function. This allows us to drive conditional generation, and display, of calculations and other values.

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

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.  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. The .Ordinal function is best used in combination with other functions, particularly “relative” functions, to generate lists of names, and so forth

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

Let’s try it out.

If you are not sure how to set up SSMS in Analysis Services to access the Adventure Works DW 2008R2  database, then click here for a guide.

Using the .Ordinal Function to Generate a Self-Explanatory “Contents” Results Dataset

Let’s construct a simple query to provide a conceptual “starting point” for the query that we will next provide for parameter picklist dataset support.  The idea is to generate a dataset that displays the Name of the level and the zero-based number (or index) for each level, for a given dimensional hierarchy within the Adventure Works cube.  This initial display will show the concepts behind using the .Ordinal function and, we hope, make clear some of the ways we can employ it effectively.  Then we’ll show how it can be extended to provide a parameter picklist support.

The Query pane appears, with our input.

Illustration 1:  Our Initial Query in the Query Pane …

The above query sets the stage for our demonstrations of some of the uses of .Ordinal.  The idea is to simply generate a dataset that illustrates exactly the data that we can expect to see.

Illustration 2:  Click Execute to Run the Query…

The Results pane is populated by Analysis Services, and the dataset appears.

Illustration 3:  Results Dataset  – Initial “Listing” Scenario

In the returned dataset, we see that the names, and respective index numbers, of the hierarchical levels appear as expected.  This simple dataset provides a great “beginner” basis for picklist support, as the picklist display labels appear within it.  The level values can serve as a basis for ordering the picklist display, and with minimal alteration, .Ordinal can be used (in conjunction with a little more logic in the query) to provide indentation based upon level, etc.

1.    Type (or cut and paste) the following query into the Query pane:
2.    Execute the query by clicking the Execute button in the toolbar...
3.    Select File -- Save MDXQuery1.mdx As …, name the file SMDX002-001, and place it in a meaningful location.

Use the .Ordinal Function to Generate a Dataset to Support a Report Parameter Picklist

The point of our next effort is to generate the fields we need for report parameter picklist support, this time for the Geography dimension of the cube.  The authors / developers have asked specifically for all geographical levels to show up as selections within the parameter picklists for various reports in SQL Server Reporting Services.

When we venture upon parameter picklist support in an OLAP scenario (at least via this method), we need to provide two main ingredients within the returned dataset:  a name that consumers can select at report runtime and the MDX-equivalent, qualified name, which is passed to Analysis Services via placeholders in the underlying dataset query.  An index (again, the zero-based numeric value of a given dimensional level), can also be useful for grouping, sorting, and other presentation purposes.  As we shall see in the steps that follow, the .Ordinal function is again useful in helping us to meet the business need.

Illustration 4:  Create a New Query with the Current Connection …

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

The Query pane appears, with our input

Illustration 5:  Our Second Query in the Query Pane …

The Results pane is, once again, populated by Analysis Services.  This time, the dataset partially depicted appears.

Illustration 6:  Results Dataset (Partial View) – Report Parameter Picklist Support

In the returned dataset, we see the columns we have discussed.  Of primary importance are DisplayName and UniqueName.  We might certainly simply display the UniqueName to consumers for selection, as well as for insertion into our MDX query (I see this done often when a solution is developed by a practitioner new to MDX, or perhaps to components of the Microsoft integrated BI solution, particularly when they are attempting to generate the parameter support wholly within Reporting Services, etc.). Of course, more user-friendly names (hopefully those chosen for the member captions during development of the Analysis Services components) might meet with more consumer acceptance.

1.    Select File -- New from the main menu.
2.    Select Query with Current Connection from the cascading menu that appears next.
3.    Type (or cut and paste) the following query into the Query pane:
4.    Execute the query by clicking the Execute button in the toolbar.
5.    Select File -- Save MDXQuery2.mdx As …, name the file SMDX002-002.mdx, and place it in the same location used to store the earlier query.

Using the .Ordinal Function as a Basis for Conditionally Displaying a Value

We will undertake an example to show how we might approach the presentation of data containing a “conditional moving average,” which is calculated and presented at the month level only, while the pre-existing measure is shown at all hierarchical Date levels down to month in the same results dataset.

Our intent is to craft a query to generate a dataset that shows an already existing cube measure, Internet Sales Amount in one column, with a calculation, “Monthly Moving Avg,” (based upon a rolling six months’ activity) alongside it.  With the levels of the Calendar Year dimension (for 2007 only, in our example) – that is months, quarters, half-years and year - as the “Y” axis, in order to show total Internet Sales Amount for each Date dimension level, but to show the rolling average only on rows representing months, as the average is (at least to the management audience) only relevant at the monthly level.  Finally, we’ve been told, instead of displaying a blank space or a zero within the rows where Monthly Moving Avg does not appear, we want the display to be “N/A.”

Another new tab again appears, with a connection to the Adventure Works cube, in the Query pane.

The Query pane appears, with our input.

Illustration 7:  Our Final Query in the Query Pane …

The Results pane is, once again, populated by Analysis Services.  The dataset appears.

Illustration 8:  Results Dataset – Conditional Generation and Display of Calculation

In the returned dataset, we see that the moving average appears only at the monthly levels, thanks to the comparison we perform through the use of the .Ordinal function.

Summary …

In this article, we explored the MDX .Ordinal function, which can be called upon in activities that range from generating simple lists and supporting parameter picklists to constructing a basis upon which we can drive conditional generation and display of calculations and other values. We introduced the function, commenting upon its operation and touching upon the creative effects we can deliver through its use.

We examined the syntax involved with .Ordinal, and showed some business uses for the function by generating queries that capitalized upon its capabilities.

1.    Select File -- New from the main menu.
2.    Select Query with Current Connection from the cascading menu that appears next, as we did to begin the query in the last section.
3.    Type (or cut and paste) the following query into the Query pane:
4.    Execute the query by clicking the Execute button in the toolbar.
5.    Select File -- Save MDXQuery3.mdx As …, name the file SMDX002-003.mdx, and place it in the same location accessed to store the earlier query files.
6.    Select File -- Exit to leave the SQL Server Management Studio, when ready.