Microsoft KB Archive/301934

= INF: MDX: How to Determine the First or Last Member with Data =

Article ID: 301934

Article Last Modified on 2/21/2007

-

APPLIES TO


 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q301934



SUMMARY
In some applications, it is useful to find the first or last dimension member that has data associated with it. This article illustrates how to use the HEAD, TAIL, and UNION functions to return the first and last members of a dimension that have data. The article also illustrates the use of the NonEmptyCrossJoin function.



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

Additional query words: HEAD TAIL NONEMPTYCROSSJOIN UNION MDX OLAP BIHowto

Keywords: kbinfo KB301934

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.