Getting Members of a hierarchy tree, but only going down so many levels?

Take the hierarchy [Customer].[Customer Geography] from the AW cube.

Country
State
City
Postal Code
Customer

Is there any nifty way with a function to say that you want all members, but only want to go down 3 levels - to city?

Country
State
City

Currently I'm doing DrillDownLevel 3 times to get "All", Countries, States, and Cities.

select {[Measures].[Customer Count]} on 0, NON EMPTY DrillDownLevel({DrillDownLevel({DrillDownLevel({[Customer].[Customer Geography].[All]})},

[Customer].[Customer Geography].[Country])}, [Customer].[Customer Geography].[State-Province]) on 1

FROM [Adventure Works]

The biggest reason I'm looking for something else is that it looks very convoluted, and also, if the hierarchy changes, the code would need to change.

I suppose you could do .All without a drilldownlevel and use a filter on the ordinal.level, but this might be a performance hit, as it would need to through each and every item to check its level?

Try the Descendants() function, it has quite a few different options. You can drill down a specific number of levels, or to a named level and choose to include or exclude members above and below the onese specified.

eg

select {[Measures].[Customer Count]} on 0, NON EMPTY Descendants({[Customer].[Customer Geography].[All]})},3,SELF_AND_BEFORE) on 1

FROM [Adventure Works]

|||

Perfect!

After a few syntax changes, it runs, and doing a union shows that the results are the same.

select {[Measures].[Customer Count]} on 0,
NON EMPTY Descendants({[Customer].[Customer Geography].[All]}, 3, SELF_AND_BEFORE) on 1
FROM [Adventure Works]

select {[Measures].[Customer Count]} on 0,
NON EMPTY UNION(Descendants({[Customer].[Customer Geography].[All]}, 3, SELF_AND_BEFORE),
DrillDownLevel({DrillDownLevel({DrillDownLevel({[Customer].[Customer Geography].[All]})},
[Customer].[Customer Geography].[Country])}, [Customer].[Customer Geography].[State-Province]))
on 1
FROM [Adventure Works]