Having introduced the Head() function in Stairway to MDX - Level 14: Basic Set Functions: The Head() Function, we will focus, in this Level, upon a function that behaves in a manner opposite to that of the Head() function. As to "type", the Tail() function is another set function, and therefore, like the Head() function, can be used to construct sets in several ways, returning data by traversing locations (both absolute and relative) among dimensions, hierarchies and levels. As we have noted elsewhere in this series, set functions are crucial to the navigation of the multidimensional structures that make their home in Analysis Services. Moreover, because set expressions define the axes involved, the very capability of MDX queries to deliver the data we request depends upon set functions.

The general purpose of the Tail() function is to return the last specified number of elements in a set. We will introduce Tail(), making observations about how it works, and then, as in the other levels of this Stairway, we will examine the syntax involved in its use. Once we've completed this overview, we'll be in a position to undertake some hands-on examples of uses of the function in practice examples, discussing the results we obtain with each as a part of the exercise.

The Tail() Function

According to the Microsoft Developer Network ("MSDN"), the Tail() function "returns the specified number of tuples from the end of the specified set." As we'll see, the order of the set elements is kept intact within the operation of the function as it returns the last << Count >> elements. We control the range of the function by providing a numeric expression, in a similar manner to the way we control the "reach," or "span," we obtain in other MDX functions. Again, in accomplishing its mission of returning a subset from the end of a set, Tail() works in a manner identical to the Head() function, differing only in the fact that it returns a subset from the end of a set, versus from the beginning of the set, after the manner of Head().

Starting at the end of a set we designate, Tail() returns the last n tuples from the specified set, where n is the number that we specify. Tail(), like Head(), manages the absence of a specified numeric expression by "defaulting" to the return of a single element (that is, a default "count of 1"). In addition to substituting 1 in cases where we do not specify a numeric expression, Tail() - again like Head() - also returns an empty set if the numeric expression (the count of tuples) provided is less than the number 1, as well as returning the full, original set of elements if we supply it a number that is greater than the number of the tuples of the specified set.

Syntactically, the set upon which we seek to perform the Tail() operation is specified within the parentheses to the right of the Tail keyword. The syntax is shown in the following string.

Tail ( <<Set_Expression >> [ ,Count ] ) 

We follow the set specification with a comma, which is, in turn, followed by the count, a numeric expression.

We can get a feel for the operation of the Tail() function with the following illustration. Let's say that a group of corporate-level information consumers within the Adventure Works organization wish to see the total Reseller Sales Amount by Reseller for the "last" ten resellers in the cube.

The basic Tail() function, which would specify the "last ten Resellers" portion of the required result dataset, would be constructed as follows:

Tail ([Reseller].[Reseller].CHILDREN, 10)

Assuming that we placed the Reseller Sales Amount measure within the column axis definition, and the Tail() function within the row axis definition, of a query, our returned dataset would look like this:

Illustration 1: Results Dataset with Tail() in the Rows Specification

Tail() enables us to compactly express, in MDX, that we wish to display the Resellers as shown, from "the end" (in their natural order), for ten elements. Much as we saw to be the case with the Head() function in Stairway to MDX - Level 14: Basic Set Functions: The Head() Function, Tail() can be particularly useful in working with the Date dimension, as can other "subset" functions like it. In addition, obvious efficiencies can be obtained when it is used in conjunction with "family" functions, as with the .Children function above.

We'll get some hands-on practice with the Tail() function in the section that follows. Moreover, we'll take a look at the way in which the function manages omitted numeric expressions, as well as values that exceed the number of tuples in the specified set. In each scenario we expose, we'll discuss briefly the results we obtain to confirm our overall understanding of the Tail() function.

The Basics

To reinforce our understanding of the basics we have covered so far, let's put the Tail() function to work in a manner that illustrates its operation in the simplest of scenarios: We will construct a SELECT query with a clearly defined set within our function.

Using Tail() in a Basic Query

As we usually do within the Levels of the Stairway to MDX series, we will rely upon the MDX Query Editor in SQL Server Management Services ("SSMS") as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain. Let's assume, for purposes of our practice example, that we have received a request, involving shipping metrics, from corporate Logistics at the Adventure Works Cycles organization, a hypothetical client. The logistics information consumers wish to know total Reseller Order Quantity shipped over the last three quarters of calendar year 2007, by Reseller Business Type.

We'll set up the query to return the Reseller Order Quantity for all quarters first, and then apply the Tail() function to deliver the measure for the specified three quarters.

1. Type (or copy and paste) the following query into a blank Query pane:

-- SMDX015-001 Use of the Tail() Function in a Basic Query
SELECT
   
   {DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
      [Date].[Calendar].[Calendar Quarter], SELF)} ON AXIS(0),
   {[Reseller].[Business Type].CHILDREN} ON AXIS(1)
FROM
   [Adventure Works]
WHERE 
   ([Measures].[Reseller Order Quantity])

The preparatory query appears within the Query pane.

Illustration 2: The Preparatory Query in the Query Pane

2. Select Query - Execute from the Main Menu, as shown.

Illustration 3: Select Query - Execute from the Main Menu

The Results pane is populated with the dataset.

Illustration 4: The Query Results – Preliminary "Core" Query Results Dataset

We see that we have obtained a summary, by Quarter for Calendar Year 2007. This will serve as the "starting point" for asking for "the last three quarters," for purposes of this simple scenario. We have applied the Descendants() function as one approach to working around the existing hierarchy of Year – Half Year (Semester) – Quarter within the preparatory step. This will enable us to meet the client need in a straightforward manner with the Tail() function in the next steps, where we will make a couple of small adjustments.

3. Within the SELECT clause, modify the existing Columns / Axis(0) specification from the existing syntax:

{DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
      [Date].[Calendar].[Calendar Quarter], SELF)} ON AXIS(0),

… to the following syntax:
{
    TAIL(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        3) 
 }  ON AXIS(0),

Note that we are merely enclosing the pre-existing selection of calendar quarters within the Tail() function on the Columns axis. The complete query should appear, with modifications, as follows:

-- SMDX015-001 Use of Tail() Function in a Basic Query
SELECT
   
   {
    TAIL(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        3) 
 }  ON AXIS(0),
   {[Reseller].[Business Type].CHILDREN} ON AXIS(1)
FROM
   [Adventure Works]
WHERE 
   ([Measures].[Reseller Order Quantity])

In the foregoing, we have simply changed the Column specification to return "the last three quarters in calendar year 2007."

The Query pane appears, with our modifications in place.

Illustration 5:  Modified Query in the Query Pane: Applying Tail() Function

4. Select Query --> Execute from the Main Menu, once again.

Analysis Services populates the Results pane, and the smaller dataset appears, as shown.

Illustration 6: New Result Dataset: "Ending Three" (Chronologically) Quarters for Reseller Types

We obtain confirmation from our client colleagues that the dataset retrieved meets the business need they have communicated: a summary of the total Reseller Order Quantity shipped over the last three quarters of calendar year 2007, by Reseller Business Type.

5. Save the query by selecting File Save MDXQuery1.mdx As …, naming the file SMDX015-001.mdx, and placing it in a convenient location.

Having gained some basic exposure to the Tail() function, let's explore additional considerations surrounding its use.

Handling of the Numeric Value

Next, let's explore the handling of omitted numeric values, among other "default" treatments rendered by the Tail() function. We can see these treatments in action by simulating some quick scenarios, much as we accomplished within our examination of the Head() function in Stairway to MDX - Level 14: Basic Set Functions: The Head() Function.

1. Within the query we have saved as SMDX015-001, replace the top comment line of the query with the following:

-- SMDX015-002, Use of Tail() Function – Omitted Numeric Expression

2. Save the query as SMDX015-002, to prevent unintentionally modifying SMDX015-001.
3. Remove the comma (",") and numeral 3 from the fourth and fifth line, respectively, of the Column axis specification of the query:

{
    TAIL(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        3) 
 }  ON AXIS(0),

… so that the modified Column axis specification looks like this: 
{
    TAIL(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF)
        ) 
 }  ON AXIS(0),

The affected characters in the query – those to be removed – appear circled in the illustration below:

Illustration 7:  Eliminate the Comma (",") and "3" from the Tail() Function

The Query pane appears, after the specified modifications, as depicted.

Illustration 8: The Query with Omitted Numeric Expression

4. Select Query --> Execute from the Main Menu, once again.

Analysis Services repopulates the Results pane, and an even smaller dataset appears, as expected.

Illustration 9: Result Dataset – Tail() Function with Omitted Numeric Expression

5. Re-save the file as SMDX015-002.
6. Leave the query open for the next step.

We see, in the foregoing result dataset that the "default" value assumed by the Tail() function, in the absence of a specified numeric expression, is 1. Q4 only, the "single last element," therefore, is returned, on the column axis, as a result.

Let's extend our work with the numeric function to the use of a negative number.

7. Within the query we have saved as SMDX015-002, replace the top comment line of the query with the following:

-- SMDX015-003, Use of Tail() Function – Numeric Expression < 1

8. Save the query as SMDX015-003, to prevent unintentional modification to SMDX015-002.
9. With the Column axis specification of the query, which currently appears as shown here:

{
    TAIL(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF)
        ) 
 }  ON AXIS(0),

…add a comma (",") at the end of the fourth line of the Column axis specification of the query (after the syntax "SELF)", and add a "-1" on the fifth line of the Column axis specification, just before the right parenthesis ( ")" ) that currently occupies line five alone, so that the modified Column axis specification looks like this:

{
    TAIL(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        -1) 
 }  ON AXIS(0),

The affected characters in the query – those we have added – appear circled in the illustration below:

Illustration 10:  Adding a "Less-than-1" Numeric Expression to the Tail() Function …

The full Query pane, after the specified modifications, appears as shown:

Illustration 11: The Query with Modified Numeric Expression

10. Right-click any point within the Query pane.
11. Select Execute from the context menu that appears, as depicted.

Illustration 12: Executing the Query via the Context Menu …

Analysis Services attempts to populate the Results pane, but this time we are confronted with an empty cellset.

Illustration 13: Empty Cellset – As Expressed by the SSMS MDX Query Editor

12. Re-save the file as SMDX015-003.
13. Leave the query open for the next step.

And so we see, in the foregoing result dataset (or, perhaps more tellingly, in the absence of same) that a numeric expression "less than 1" within the Tail() function elicits the return of an empty cellset, as we noted in our general syntax discussion earlier.

Now, let's examine one last provision for "slightly offbeat" numeric input: what happens when we input a number that is higher than the total number of tuples in the specified set?

14. Within the query we have saved as SMDX015-003, replace the top comment line of the query with the following:

-- SMDX015-004, Use of Tail() Function – Numeric Expression > Total No. Tuples in Set

15. Save the query as SMDX015-004, to prevent unintended changes to SMDX015-003.
16. Replace the numeral -1 with the numeral 6, in the following lines of the existing query:

{
    TAIL(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        -1) 
 }  ON AXIS(0),

… so that the modified Column axis specification looks like this:

{
    TAIL(
         DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
            [Date].[Calendar].[Calendar Quarter], SELF),
        6) 
 }  ON AXIS(0),

The affected character in the query – the one we have modified – appears circled in the illustration below:

Illustration 14:  Intentionally Exceeding the Known Number of Tuples in a Set within the Numeric Expression of the Tail() Function …

The full Query pane, after our latest modification, appears as shown:

Illustration 15: The Query with Modified Numeric Expression

17. Press the F5 button.

Analysis Services populates the Results pane, generating the following dataset:

Illustration 16: Results of the Modified Numeric Expression in the Tail() Function

18. Re-save the file as SMDX015-004.

While we might have thought it rather intuitive, we recognize immediately that, even though the numeric expression that we input exceeds the number of tuples in the specified set (obviously, only four quarters exist in any single year), Tail() returns the full set, and nothing more.

We have examined instances of the behavior of the Tail() function under various scenarios of numeric expression input for a specified set. To conclude our examination of Tail(), and to activate what we have learned further, let's construct a query to meet another hypothetical business need.

Another Example of Tail() at Work

Let's say that Logistics Department is pleased with the illustrations that we have provided in the foregoing examples. In the same breath within which they make this known to us, however, they outline a slightly more elaborate requirement. We're told that Logistics would like to be able to present, within a single report, an "adjusted" Internet Sales gross profit. The calculation is simple: They want to present the gross profit value as it is currently calculated (Internet Sales Amount minus Internet Total Product Cost), but with Internet Freight Cost also subtracted, as the organization began offering free shipping to all customers in calendar year 2007.

Our client colleagues tell us that they want to generate Internet Gross Profit minus Internet Freight Cost for the last two quarters of calendar year 2007, preferring that we label this derived metric "Adjusted Internet Gross Profit." They state that they wish to see all three measures totaled side-by-side, for easy verification of the new calculation amount, and by customer country, at least in this request. (The ultimate objective is to perform parameterization in Reporting Services reports they generate based upon test examples such as this one.)

Let's return to the MDX Query Editor to craft a new query to handle this request.

1. Select File --> New from the main menu.
2. Select Query with Current Connection from the cascading menu that appears, as depicted.

Illustration 17: Creating a New Query with Current Connection …

3. Type (or copy and paste) the following into the new blank Query pane that appears:

-- SMDX015-005, Use of Tail() Function – Bonus Example
WITH 
MEMBER
   [Measures].[Adjusted Internet Gross Profit] 
AS
   [Measures].[Internet Gross Profit] - [Measures].[Internet Freight Cost]
SELECT
   CROSSJOIN 
      (
        { TAIL(
            DESCENDANTS([Date].[Calendar].[Calendar Year].[CY 2007],
               [Date].[Calendar].[Calendar Quarter], SELF), 
2) },  
 
 {[Measures].[Internet Gross Profit], [Measures].[Internet Freight Cost], 
    [Measures].[Adjusted Internet Gross Profit]}
      )  ON AXIS(0),
   {[Customer].[Customer Geography].[Country].MEMBERS} ON AXIS(1)
FROM
   [Adventure Works]

Our input appears within the Query pane, as shown.

Illustration 18: The Query in the Query Pane

In the query above, we employ the WITH keyword to create a calculated measure - our new Adjusted Internet Gross Profit measure. We apply the Descendants() function, once again, as an approach to working around the existing hierarchy of Year – Half Year (Semester) – Quarter, to enable us to meet the client need to present "the last two quarters of calendar year 2007" in a straightforward manner, via the Tail() function, once again, for our column axis. Moreover, we exploit the CrossJoin() function to order our three measures (two base and one calculated) under each Quarter we present.

4. Select Query - Execute from the Main Menu, as we did in earlier steps.

The Results pane is populated with the dataset.

Illustration 19: The Query Results Dataset

We see that we have obtained a summary, by Quarter for Calendar Year 2007, of "the last two quarters," courtesy of the Tail() function. The base and calculated measures are presented, as requested, by customer country. Our client colleagues express their satisfaction with our handiwork.

5. Save the query as SMDX011-005.mdx.
6. Exit the Query Editor when ready.

We will gain practical exposure to additional "subset" functions in subsequent levels of Stairway to MDX.

Summary

In this article, we introduced the Tail() function, whose general purpose is to return a specified number of elements from the end of a given set, preserving natural order. We commented upon the operation of the function, and then examined its syntax.

We undertook practice examples with the Tail() function, within which we acted to meet illustrative business requirements. Moreover, we demonstrated, in detail, the manner in which Tail() handles various numeric expression input scenarios. Throughout the practice examples, we briefly discussed the results datasets we obtained via our use of the Tail() function, together with other surrounding considerations.