The Head() Function
According to the Analysis Services
Books Online, the Head() function "returns
the first << Numeric Expression >> elements in a set."
As we shall see, the order of the set elements is kept 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.
We will examine the syntax for the Head()
function, looking 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.
Discussion
To expand our explanation of its general operation, the Head()
function starts at the beginning of a set we designate. Its
straightforward operation is to return the first n elements from
the specified set, where n is the number that we specify.
Head() 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 also returns an empty set if the numerical expression
provided is less than the number 1, as well as returning the full set
of elements if we supply it a number that 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 Head().
Syntax
Syntactically, the set upon
which we seek to perform the Head operation is specified within the
parentheses to the right of Head. The syntax is shown in the following
string.
Head(<< 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 a numeric expression means that the
function simply does its job with a default of 1. Numeric expressions
that are 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.
The following example
expression illustrates a use of the Head() function. 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 first three quarters of 1998.
The basic Head()
function, which would specify the "first three quarters" portion of
the required result dataset, would be constructed as follows:
HEAD([1998].Children, 3)
Assuming that we placed the Head() 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.
|
|
Q1
|
Q2
|
Q3
|
|
Canada
|
4,186.22
|
4,949.88
|
4,196.32
|
|
Mexico
|
16.925.52
|
19,625.45
|
16,477.01
|
|
USA
|
25,358.40
|
26,093.90
|
24,912.75
|
Table 1: Results Dataset with Head() Defining Columns
The function has the effect of compactly expressing that we
wish to display the Quarters as shown, from "beginning" (in
their natural order), for three elements. As is probably obvious, Head()
can be particularly useful in working with the Time dimension, as can
other "subset" functions. In addition, obvious efficiencies can be
obtained when it is used in conjunction with "family" functions, as
with the .Children function above.
NOTE: For information surrounding the .Children
function, see MDX Member Functions: The "Family" Functions.
We will practice the use of the Head() 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. In each scenario we
expose, we will discuss briefly the results we obtain to reinforce our overall
understanding of the Head() function.
» 
Comment and Contribute
