Tail

The MDX Tail function for Essbase returns the last n members or tuples present in a set.

Syntax

Tail ( set [,index ] )

Parameters

set

The set from which to take items.

index

The number of items to take from the end of the set. If omitted, the default is 1. If less than 1, an empty set is returned. If the value exceeds the number of tuples in the input set, the original set is returned.

Example

Example 1

This example uses the following part of the Sample Basic outline:

The following expression

[Product].children

returns the set:

{ [100], [200], [300], [400], [Diet] }

Therefore, the following expression

 Tail (
  [Product].children, 2) 

returns the last two members of the previous result set:

{ [400], [Diet] }

Example 2

This example uses the following parts of the Sample Basic outline:

The following expression

 Crossjoin ( [100].children, [South].children )

returns the set:

{ ([100-10], Texas), ([100-10], Oklahoma), ([100-10], Louisiana), ([100-10], [New Mexico]),
  ([100-20], Texas), ([100-20], Oklahoma), ([100-20], Louisiana), ([100-20], [New Mexico]),
  ([100-30], Texas), ([100-30], Oklahoma), ([100-30], Louisiana), ([100-30], [New Mexico]) }

And the following expression:

Tail ( Crossjoin ([100].children, [South].children), 8 )

returns the last 8 tuples of the previous result set:

{ ([100-20], Texas), ([100-20], Oklahoma), ([100-20], Louisiana), ([100-20], [New Mexico]),
  ([100-30], Texas), ([100-30], Oklahoma), ([100-30], Louisiana), ([100-30], [New Mexico]) } 

Additionally, the following expression

([Year].generations(2).members)

returns the set of members comprising the second generation of the Year dimension:

{ [Qtr1], [Qtr2], [Qtr3], [Qtr4] }

Therefore, the following query

SELECT 
  {([Year].generations(2).members)}
ON COLUMNS,
  Tail (
    Crossjoin ([100].children, [South].children),
    8)
ON ROWS
FROM Sample.Basic

returns the grid:

Table 4-130 Output Grid from MDX Example

(axis)	(axis)	Qtr1	Qtr2	Qtr3	Qtr4
100–20	Texas	206	199	152	82
	Oklahoma	84	66	55	79
	Louisiana	119	158	171	104
	New Mexico	–103	–60	–97	–18
100–30	Texas	#Missing	#Missing	#Missing	#Missing
	Oklahoma	#Missing	#Missing	#Missing	#Missing
	Louisiana	#Missing	#Missing	#Missing	#Missing
	New Mexico	#Missing	#Missing	#Missing	#Missing

To suppress the missing rows, use NON EMPTY at the beginning of the row axis specification:

SELECT 
  {([Year].generations(2).members)}
ON COLUMNS,
NON EMPTY
  Tail (
    Crossjoin ([100].children, [South].children),
    8)
ON ROWS
FROM Sample.Basic

This modified query returns as many of the 8 requested tuples as it can, without returning any that have entirely #Missing data:

Table 4-131 Output Grid from MDX Example

(axis)		Qtr1	Qtr2	Qtr3	Qtr4
100-20	Texas	206	199	152	82
100-20	Oklahoma	84	66	55	79
100-20	Louisiana	119	158	171	104
100-20	New Mexico	-103	-60	-97	-18

See Also

Head