MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

 » 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 Search

## MS SQL

Posted May 10, 2004

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

By William Pearson

### 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:

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.

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