DrilldownLevel (MDX)

Drills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set.

Syntax

Level expression syntax
DrilldownLevel(Set_Expression [ , Level_Expression ] )

Numeric expression syntax
DrilldownLevel(Set_Expression [ , ,Index} ] )

Arguments

• Set_Expression
  A valid Multidimensional Expressions (MDX) expression that returns a set.

• Level_Expression
  A valid Multidimensional Expressions (MDX) expression that returns a level.

• Index
  A valid numeric expression that specifies the hierarchy number to drill down into within the set.

Remarks

The DrilldownLevel function returns a set of child members in a hierarchical order, based on the members included in the specified set. Order is preserved among the original members in the specified set, except that all child members included in the result set of the function are included immediately under their parent member.

If a level expression is specified, the function constructs a set in a hierarchical order by retrieving the children of only those members that are at the specified level. If a level expression is specified and there is no member of at the specified level represented in the specified set, the specified set is returned.

If an index value is specified, the function constructs a set in a hierarchical order by retrieving the children of only those members that are at the next lowest level of the specified hierarchy referenced in the specified set, based on a zero-based index.

If neither a level expression nor an index value is specified, the function constructs a set in a hierarchical order by retrieving the children of only those members that are at the next lowest level of the first dimension referenced in the specified set.

Examples

The following example counts the number of products in the Product dimension by using the DrilldownLevel function in conjunction with the Countfunction.

Count(DrilldownLevel ( 
   [Product].[Product].[Product]))

The following example uses the numeric expression syntax to drilldown into the first hierarchy, the Customer Geography hierarchy.

SELECT DRILLDOWNLEVEL
   ( {[Customer].[Customer Geography].[Country].&[Canada]} * {[Customer].[Gender].[All Customers]},,0)
   ON 0
FROM [Adventure Works]

The following example uses the numeric expression syntax to drilldown into the second hierarchy, which is the Gender hierarchy.

SELECT DRILLDOWNLEVEL
   ( {[Customer].[Customer Geography].[Country].&[Canada]} * {[Customer].[Gender].[All Customers]},,1)
   ON 0
FROM [Adventure Works]

The following example returns the count of the resellers whose sales have declined over the previous time period, based on user-selected State-Province member values evaluated by using the Aggregatefunction. The Hierarchize and DrilldownLevel functions are used to return values for declining sales for product categories in the Product dimension. The DrilldownLevel function is used to drill down to the next lowest level of the Product attribute hierarchy (because no level is specified).

WITH MEMBER Measures.[Declining Reseller Sales] AS 
   Count(
      Filter(
         Existing(Reseller.Reseller.Reseller), 
            [Measures].[Reseller Sales Amount] < ([Measures].[Reseller Sales Amount],
            [Date].Calendar.PrevMember)
            )
         )
MEMBER [Geography].[State-Province].x AS 
   Aggregate ( 
      {[Geography].[State-Province].&[WA]&[US], 
      [Geography].[State-Province].&[OR]&[US] } 
         )
SELECT NON EMPTY Hierarchize (
   AddCalculatedMembers (
      {DrilldownLevel ({[Product].[All Products]})}
         )

See Also

Reference

MDX Function Reference (MDX)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	Changed content: Updated syntax and arguments to improve clarity. Added updated examples.