DrilldownLevel (MDX)

We drill down a level for the [Spain] and [France] members. In result we have hierarchized members [Spain] and [France] with their children.

Query

DrilldownLevel({[Geography].[Geo].[Spain],[Geography].[Geo].[France]})

Result

{ 
  [Geography].[Geo].[Country].[Spain], 
  [Geography].[Geo].[City].[Barcelona], 
  [Geography].[Geo].[City].[Madrid], 
  [Geography].[Geo].[City].[Valencia], 
  [Geography].[Geo].[Country].[France], 
  [Geography].[Geo].[City].[Paris]
}

Assertion : MDX Equals

{
[Geography].[Geo].[Spain],
[Geography].[Geo].[Spain].children,
[Geography].[Geo].[France],
[Geography].[Geo].[France].children
}

We drill down a level for the [Spain] and [Europe] members. Since we specify [Country] as the level for the drill down we will get hierarchized members [Spain] with its children and [Europe] only (continent level).

Query

DrilldownLevel( { [Geography].[Geo].[Europe], [Geography].[Geo].[Spain] }, [Geography].[Geo].[Country] )

Result

{ 
  [Geography].[Geo].[Continent].[Europe], 
  [Geography].[Geo].[Country].[Spain], 
  [Geography].[Geo].[City].[Barcelona], 
  [Geography].[Geo].[City].[Madrid], 
  [Geography].[Geo].[City].[Valencia]
}

Assertion : MDX Equals

{
[Geography].[Geo].[Europe],
[Geography].[Geo].[Spain],
[Geography].[Geo].[Spain].children
}

There is no member at specified level (i.e., [City]) in the set; so no drilldown is performed and the set itself is returned.

Query

DrilldownLevel( { [Geography].[Geo].[Europe], [Geography].[Geo].[Spain] }, [Geography].[Geo].[City] )

Result

{ 
  [Geography].[Geo].[Continent].[Europe], 
  [Geography].[Geo].[Country].[Spain]
}

Assertion : MDX Equals

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

We creates a set of tuples from [Continent]s and [Years]. Specifying 1 as index, we are drilling down for the levels of the hierarchy with the index 1 which is [Time]; quarters are returned.

Query

DrilldownLevel( { ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Year].[2010] ) }, , 1)

Result

{
  ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Year].[2010] ), 
  ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Quarter].[Q1 2010] ), 
  ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Quarter].[Q2 2010] ), 
  ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Quarter].[Q3 2010] ), 
  ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Quarter].[Q4 2010] )
}

Assertion : MDX Equals

Union(
    { ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Year].[2010] ) },
    CrossJoin( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Year].[2010].Children )
)

The index expression does not reference any valid hierarchy; so no drilldown is performed and the set itself is returned.

Query

DrilldownLevel( { ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Year].[2010] ) }, , 10)

Result

{
  ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Year].[2010] )
}

Assertion : MDX Equals

{ ( [Geography].[Geo].[Continent].[America], [Time].[Calendar].[Year].[2010] ) }

There is no member from hierarchy of the specified level expression.

Query

SELECT DrilldownLevel( { [Geography].[Geo].[Europe], [Geography].[Geo].[Spain] }, [Time].[Calendar].[Year] ) ON 0 FROM [Sales]

Result

Error Code    : OLAP_DRILLDOWN_INVALID_HIERARCHY
Error Message : DrilldownLevel() : the hierarchy '[Time].[Calendar]' is not available in the set

The index does not reference any hierarchy.

Query

DrilldownLevel( [Geography].[Geo].[Continent].members, , -1 )

Result

Error Code    : OLAP_OUT_OF_RANGE
Error Message : DrilldownLevel() : the hierarchy/member index value '-1' is out of range (expected range:'>= 0')

We pass null/empty set and expect an empty set as result.

Query

{}

Result

{}

Assertion : MDX Equals

DrilldownLevel({})

Assertion : MDX Equals

DrilldownLevel(NULL)