MORE INFORMATION
In versions of Microsoft SQL Server 2000 Analysis Services that are earlier than Service Pack 2 (SP2), you can use the
Descendants function in a Multidimensional Expressions (MDX) statement to retrieve the set of descendants for a specific member. However, when you use the
Descendants function with these earlier versions, you cannot specify that you want the descendants for all the members at a specific level or a specific distance. Retrieving the descendants for all the members at a specific level or a specific distance is important when you want to display all the members in a dimension that are at or below a certain level.
Starting with SQL Server 2000 Analysis Services SP3, you can now use a unidimensional set as the first argument with the
Descendants function. Some examples of how to use a unidimensional set with the
Descendants function follow.
To retrieve the descendants for all the members that are at a specific level, use the following syntax:
Descendants(<Member>|<Set>,[<<Level>>[, <<Desc_flags>>]])
To retrieve the descendants for all the members that are at a specific distance, use the following syntax:
Descendants(<Member>|<Set>, <<Distance>>[, <<Desc_flags>>])
The syntax in the following two lines is equivalent:
Generate(<Set>, Descendants(s.CurrentMember, ...))
If you specify a multidimensional set as the first argument of the
Descendants function, you may receive an error message when you run an MDX query that contains the
Descendants function.
The descendants of all the members at the specified level are returned in the result set when the
Set argument of the
Descendants function is in the following format.
A
Descendants function that uses the
Level.Members format for the
Set argument can appear in cell calculations and in sub-cube definitions. Using the
Level.Members format for the
Set argument in the
Descendants function increases the flexibility of calculated cells in SQL Server 2000 Analysis Services.
For example, the following MDX statement passes the members of the
State Province level of the
Customers dimension as a parameter of the
Descendants function.
select
{[Measures].[Unit Sales]} on columns,
Descendants([Customers].[State Province].members) on rows
from SalesWhen you run this MDX statement, the MDX statement returns a result set that contains all the members for all the levels that are under the
State Province level.