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

May 10, 2004

10. Execute the query by clicking the Run Query button in the toolbar.

We receive a message box, as shown in Illustration 5, stating that the Sample Application is unable to display the opened cellset - essentially because the dataset is empty.

Click for larger image

Illustration 5: Message - Empty Cellset

11. Click OK.

The message box closes, and we are confronted with the empty cellset (perhaps only briefly), as expressed by the Sample Application (See Illustration 6.)

Illustration 6: Empty Cellset as Expressed by the Sample Application

12. Re-save the file as MDX19-3.

And so we see that a numerical expression less than 1 within the Head() function elicits the return of an empty cellset. Now, let's examine one last provision for oddball numeric input: what happens when we input a number that is higher than the total number of tuples in the specified set?

13. Within the query we have saved as MDX19-3, replace the top comment line of the query with the following:

-- MDX019-4, Use of Head() Function - Numeric Expression > Total Tuples in Set

14. Save the query as MDX19-4.

15. Replace the numeral -1 with the numeral 6, in the following line of the existing query:

              {Head([Time].[1998].Children, -1)} ON COLUMNS,

The Query pane appears as shown in Illustration 7, with the inserted coding circled in red.

Illustration 7: The Query with Numerical Expression Larger than Set

16. Execute the query by clicking the Run Query button in the toolbar.

The Results pane is populated, and the dataset shown in Illustration 8 appears.

Illustration 8: Results of the Modified Numeric Expression

We see that, even though the numerical expression that we input exceeds the number of tuples in the specified set (only four Quarters exist in any single Year), Head() returns the Full Set only.

17. Re-save the file as MDX19-4.

We have explored examples of the behavior of the Head() function under various scenarios of numeric expression input for a specified set. We saw that the function manages potential input by providing a "default" numeric expression to drive its behavior, with regard to the results it produces.