MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function - Page 2

The Tail() Function

According to the Analysis Services Books Online, the Tail() function "returns the last << Numeric Expression >> elements in a set." As we shall see, the order of the set elements remains intact within the operation of the function. We control the range of the function by providing a numeric expression, similar to the way we control the "reach" we obtain in other MDX functions - and identical to the way we use the numeric expression in the Head() function that we explored in our previous article.

We will examine the syntax for the Tail() function, then look at its behavior based upon different numeric expressions we might provide. Next, we will undertake practice examples built around hypothetical business needs that illustrate logical uses for the function. We will also experience hands-on the manner in which Tail() handles the numeric expression scenarios we have explored in the Discussion and Syntax sections.

Discussion

To expand our explanation of its general operation, the Tail() function starts at the end of a set we designate. Its straightforward operation is to return the n elements from the specified set, where n is the number that we specify, counting backward from the end of the set, and preserving set order.

In a manner similar to Head(), as we saw in our previous article, Tail() manages the absence of a specified numeric expression by "defaulting" to the return of a single element. In addition to substituting 1 in cases where we do not specify a numeric expression, it again replicates the action of Head(), and returns an empty set if the numerical expression provided is less than the number 1. Further, Tail() returns the full set of elements, in a way again identical to Head(), if the number we supply it is greater than the number of the elements of the specified set.

Let's look at some syntax illustrations to further clarify the operation of Tail().

Syntax

Syntactically, the set upon which we seek to perform the Tail operation is specified within the parentheses to the right of Tail, just as we saw with the Head() function in our previous article. The syntax is shown in the following string.

Tail(<< Set >> [,<< Numeric Expression >>])

We follow the set specification with a comma, which is, in turn, followed by the numeric expression. As we have discovered, the omission of the numeric expression means that the function simply does its job with a default of 1. For a scenario where the numeric expression is greater than the number of tuples in the set we specify, the original set itself is returned. Moreover, the input of a number less than 1 as the numeric expression results in a "default answer" of empty set. These "defaults" for "numeric expression management" are identical to those we found in the Head() function.

The following example expression illustrates a use of the Tail() function, within the context of an expression we used in discussing the syntax of the Head() function in our last article. This will illustrate the similarities in the construction of the functions, while exposing the differences in the datasets that they return.

Let's say that a group of corporate-level information consumers within the FoodMart organization wish to see the total Profits by U.S. Warehouse-Country for the last three Quarters of 1998.

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

Tail([1998].Children, 3)

Assuming that we placed the Tail() function within the column axis definition of a query, and the Warehouse-Country information defined the row axis, our returned dataset would resemble that shown in Table 1.

Just as we saw with the Head() function in our previous session, Tail() has the effect of compactly expressing that we wish to display the Quarters as shown, from "the end" of the set (the Quarters of 1998), in their natural order, for three elements "distance."

As is probably obvious, Tail() can be particularly useful in working with the Time dimension, as can other "subset" functions, including Head(), as we saw in our last article (using the very same example as above, for comparative purposes). In addition, obvious efficiencies can be obtained when it is used in conjunction with "family" functions, as with the .Children function above. More compact, reusable coding is often the result.

We will practice the use of the Tail() function in the section that follows. Moreover, we will 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, just as we did in the previous article with the first of the subset functions, Head(). In each scenario we expose, we will discuss briefly the results we obtain to reinforce our overall understanding of the Tail() function.