INF: MDX: How to Determine the First or Last Member with Data (301934)

MORE INFORMATION

Assume that your task is to find the first and last members of the time dimension with data from the FoodMart 2000 sample. For many, the first thought for finding the first member with data would be to use the FirstChild() function as follows:

SELECT
   {[Time].FirstChild} ON COLUMNS
FROM SALES
				

Likewise, the first thought for finding the last member of the time dimension with data would be to use the LastChild() function as follows:

SELECT
   {[Time].[1998].[Q4].LastChild} ON COLUMNS
FROM SALES
				

The first multidimensional expression (MDX) query, however, returns the value associated with [1997].[Q1] and not the value associated with [1997], which is the first member with data. The second MDX query returns the value associated with [1997].[Q4].[12], which is the last member of the dimension, but not the last member with data.

As an alternative, the HEAD() function returns the first specified number of elements in a set, and can be used to return the first member of the dimension. Likewise, the TAIL() function returns a subset from the end of a set and can be used to return the last member of the dimension. The MDX query to return the first member of the time dimension would take the following form:

SELECT
   HEAD([Time].Members,1) ON COLUMNS
FROM SALES
				

This query does return 1997 as the first member of the dimension with data.

The MDX query to return the last member of the dimension would take the following form:

SELECT
   TAIL([Time].Members,1) ON COLUMNS
FROM SALES
				

This MDX query returns [1998].[Q4].[12] as the last member of the dimension. However, the member returned is not the last member of the dimension with data. In order to eliminate members with no data, the NonEmptyCrossJoin() function should be used to filters out all the members in the dimension that don't have data associated with them.

The MDX query to find the first member with data then takes the form

SELECT
   HEAD(NonEmptyCrossJoin([Time].Members,1),1) ON COLUMNS
FROM SALES
				

and the MDX query to find the last member with data then takes the form:

SELECT
   TAIL(NonEmptyCrossJoin([Time].Members,1),1) ON COLUMNS
FROM SALES
				

The UNION() function can then be used to combine the two MDX queries into a single query:

SELECT
   UNION(HEAD(NonEmptyCrossJoin([Time].Members,1),1),
   TAIL(NonEmptyCrossJoin([Time].Members,1),1)) ON COLUMNS
FROM SALES