DrilldownMemberTop (MDX)

Drills down members from the first specified set that are present in the second set limiting the number of items in the resulting set to the specified count parameter.

Parameters

Name	Type	Cardinality	Default value
set-1	set-expression
set-2	set-expression
count	numeric-expression
measure	numeric-expression	zero-one
	RECURSIVE	zero-one

Return

set

Description

The function drills down specified members from the first set (if they are present in the second set).

The number of returned drilled down members will be limited to the count parameter. Only count members from the top (i.e., that have the highest value for the measure) will be appended to the result set.

The optional measure will be applied while sorting members; the default measures is used otherwise.

The optional RECURSIVE flag can be used : in such case the function continues recursively to drill down members.

This function can be used with tuple sets as well.

Examples

DrilldownMemberTop(member-set,member-set,numeric,measure)

icCube	Corporate	Partnership	Personal
255	144	96	15

DrilldownMemberTop(member-set,member-set,numeric,measure,RECURSIVE)

We're recursively expanding the whole Europe, sorting in ascending order its countries and then for each country sorting in ascending order their cities.

Query

DrilldownMemberTop(
    { [Geography].[Geo].[Europe] },
    { [Geography].[Geo].Members },
    1024,
    [Measures].[Amount],
    RECURSIVE
)

Result

{ 
  [Geography].[Geo].[Continent].[Europe], 
  [Geography].[Geo].[Country].[Switzerland], 
  [Geography].[Geo].[City].[Geneva], 
  [Geography].[Geo].[City].[Zurich], 
  [Geography].[Geo].[City].[Lausanne], 
  [Geography].[Geo].[Country].[France], 
  [Geography].[Geo].[City].[Paris], 
  [Geography].[Geo].[Country].[Spain], 
  [Geography].[Geo].[City].[Barcelona], 
  [Geography].[Geo].[City].[Madrid], 
  [Geography].[Geo].[City].[Valencia]
}

Assertion : MDX Equals

Union(
    [Geography].[Geo].[Europe],
    -- for each country
    Generate( Order( [Geography].[Geo].[Europe].Children, [Measures].[Amount], BDESC),
              -- the list of cities
              Union( [Geography].[Geo].currentMember, Order( [Geography].[Geo].currentMember.Children, [Measures].[Amount], BDESC ))
    )
)

DrilldownMemberTop(tuple-set,member-set,numeric,measure)

Drilldown is not limited to member sets.

We're drilling down (for the year 2010) the icCube product to select the three licenses (sorted in ascending order) with the most sales.

Query

SELECT
    DrilldownMemberTop(
        { ( [Time].[Calendar].[2010], [Product].[Prod].[icCube] ) },
        { [Product].[Prod].[icCube] },
        3,
        [Measures].[Amount]
    ) ON 0
FROM
    [Sales]
WHERE
    [Geography].[Geo].[Europe]

Result

2010	2010	2010	2010
icCube	Corporate	Partnership	Personal
255	144	96	15

Assertion : MDX Equals

SELECT
    Union(
        { ( [Time].[Calendar].[2010], [Product].[Prod].[icCube] ) },
        Head( Order( Crossjoin( [Time].[Calendar].[2010], [Product].[Prod].[icCube].Children ), [Measures].[Amount], BDESC), 3)
    ) ON 0
FROM
    [Sales]
WHERE
    [Geography].[Geo].[Europe]

Special Examples (errors, null, ...)

DrilldownMemberTop({},member-set,numeric), DrilldownMemberTop(NULL,member-set,numeric)

DrilldownMemberTop(member-set,{},numeric), DrilldownMemberTop(member-set,NULL,numeric)

No drilldown is performed : the first set is returned.

Query

{ [Geography].[Geo].[Europe],[Geography].[Geo].[America] }

Result

{ 
  [Geography].[Geo].[Continent].[Europe], 
  [Geography].[Geo].[Continent].[America]
}

Assertion : MDX Equals

DrilldownMemberTop( { [Geography].[Geo].[Europe], [Geography].[Geo].[America] }, {}, 1 )

Assertion : MDX Equals

DrilldownMemberTop( { [Geography].[Geo].[Europe], [Geography].[Geo].[America]}, NULL, 1 )

XMLA Properties

Find here the value of the XMLA properties when not specified otherwise.

DrilldownMemberTop (MDX)

Parameters

Return

Description

See Also

Examples

Special Examples (errors, null, ...)

XMLA Properties