Microsoft KB Archive/320733

= How to retrieve the descendants of all the members of a dimension at a specific level or a specific distance by using the Descendants function in SQL Server 2000 Analysis Services Service Pack 3 =

Article ID: 320733

Article Last Modified on 2/5/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q320733



SUMMARY
This article describes how to retrieve the descendants of all the members of a dimension at a specific level or a specific distance by using an enhanced feature of the Descendants function in Microsoft SQL Server 2000 Analysis Services Service Pack 3 (SP3).



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(|,[<>[, <>]])

To retrieve the descendants for all the members that are at a specific distance, use the following syntax:

Descendants(|, <>[, <>])

The syntax in the following two lines is equivalent:

Descendants(, ...)

Generate(, Descendants(s.CurrentMember, ...))

Note 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  argument of the Descendants function is in the following format.

. .Members

A Descendants function that uses the .Members format for the Set argument can appear in cell calculations and in sub-cube definitions. Using the .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 Sales

When 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.

Additional query words: MDX query OLAP descendant

Keywords: kbdatabase kbquery kbinfo kbsqlserv2000sp3fea KB320733

-

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

© Microsoft Corporation. All rights reserved.