Export (0) Print
Expand All
Avg
IIf
Lag
Max
Min
Mtd
Qtd
Sum
Var
Wtd
Ytd
Expand Minimize

DrilldownMemberBottom (MDX)

Drills down the bottommost members of a set, at a specified level, to one level below.

DrilldownLevelBottom(Set_Expression, Count [,[<level_expression>] [,[<numeric_expression>][,include_calc_members]]])

Set_Expression

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

Count

A valid numeric expression that specifies the number of tuples to be returned.

Level_Expression

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

Numeric_Expression

A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number.

Include_calc_members

A keyword to enable calculated members to be included in drilldown results

If a numeric expression is specified, the DrilldownLevelBottom function sorts, in ascending order, the children of each member in the specified set, according to the specified value, as evaluated over the set of child members. If a numeric expression is not specified, the function sorts, in ascending order, the children of each member in the specified set, according to the values of the cells represented by the set of child members, as determined by the query context; This behavior is similar to the BottomCount and Tail (MDX) functions which return a set of members in natural order, without any sorting.

After sorting, the DrilldownLevelBottom function returns a set that contains the parent members and the number of child members, specified in Count, with the lowest value.

The DrilldownLevelBottom function is similar to the DrilldownLevel function, but instead of including all children for each member at the specified level, the DrilldownLevelBottom function returns the bottom-most number of child members.

Querying the XMLA property MdpropMdxDrillFunctions enables you to verify the level of support that the server provides for the drilling functions; see Supported XMLA Properties (XMLA) for details.

The following example returns the bottom three children of the Product Category level, based on the default measure. In the Adventure Works sample cube, the bottom three children for Accessories are Tires and Tubes, Pumps, and Panniers. In Management Studio, in the MDX query window, you can navigate to Products | Product Categories | Members | All Products | Accessories to view the complete list. You can increase the Count argument to return more members.

SELECT DrilldownLevelBottom 
   ([Product].[Product Categories].children,
   3,
   [Product].[Product Categories].[Category])
   ON 0
   FROM [Adventure Works]

The next example illustrates using the include_calc_members flag, used to include calculated members in the drill down level. In order for the filter to work properly for all members, add the measure [Reseller Order Count] to the DrilldownLevelBottom statement. To see the calculated member, it's necessary to increase Count to at least 9.

INCLUDE_CALC_MEMBERS
WITH MEMBER 
[Product].[Product Categories].[Category].&[3].[PREMIUM CLOTHES] AS
[Product].[Product Categories].[Subcategory].&[18] +
[Product].[Product Categories].[Subcategory].&[21]
SELECT [Measures].[Reseller Order Count] ON 0,
DRILLDOWNLEVELBOTTOM(
  [Product].[Product Categories].children ,
  9,
  [Product].[Product Categories].[Category] ,
  [Measures].[Reseller Order Count],
  INCLUDE_CALC_MEMBERS ) 
  ON 1
FROM [Adventure Works]
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft