Analyzing Cubes using Office Client Components - Professional SQL Server Analysis Services 2005 with MDX - page 786

Let's start with the innermost function and work our way out. Whenever you
drag and drop a specific hierarchy to the Pivot table, the member in the top-
most level of that hierarchy is identified and used in the query. In the previous
example, the All member in the Customer Geography hierarchy, All Cus-
tomers, is used. The DrillDownLevel MDX function is a function that can take
multiple parameters. The Syntax of the DrillDownLevel function is as follows:
DrillDownLevel (<Set>,{<Level>,<Index>}])
The first argument to the DrillDownLevel function is a Set. The second and
third arguments are optional. The DrillDownLevel function returns the mem-
bers of the specified Set (provided as the first argument) that are one level
lower than the level of the members specified in the set. If the optional Level
parameter is specified, the function returns members one level below the spe-
cified level. If the set contains tuples, the index is used to reference the di-
mension for which the drill down has to be applied. In the query we are cur-
rently examining, the members at the Country level of the hierarchy Customer
Geography is returned. You could have retrieved the members by specifying
the following MDX expression, but the DrilldownLevel function provides you
with more options and is useful while drilling down to multiple levels, which is
why Excel uses this function. We talk about drilling down to multiple levels
later in this section.
{[Customer].[Customer Geography].[All Customers],
[Customer].[Customer
Geography].[All Customers].children}
The result of the DrillDownLevel function is a set of members. By default dur-
ing MDX evaluation the calculated members typically do not get included in
the set. You would have to explicitly include them. While browsing the Pivot
table Excel generates calculated members based on the user selections in
the Pivot table. To make sure appropriate calculated members do get in-
cluded in the result, we use the MDX function AddCalculatedMembers. Fol-
lowing is a simple illustration using the Measures dimension for you to under-
stand the behavior of AddCalculatedMembers (you have learned that Meas-
ures is a special dimension within the cube).
select measures.[Sales Amount] on 0
from [Adventure Works] //Calculated
measures not returned