Children of the Children in MDX

Hi, all
I'm quite new to MDX.
What I'd like to know is weather it is possible to select all the children of the children of a given node in one-line query. Say we have [Driver].[All Drivers] dimension level. And I want to do something like "[Driver].[All Drivers].Children.Children" instead of obtaining the results with the use of "CrossJoin/Union" . E. g.:

{
Crossjoin({[Driver].[All Drivers].[564654 - Andrew]},
[ActivityPerDriverDailyEvents].[All Events].[Tue - 03/01/2006].Children)), Crossjoin({[Driver].[All Drivers].[564654 - Andrew]},
[ActivityPerDriverDailyEvents].[All Events].[Wed - 04/01/2006].Children)), Crossjoin({[Driver].[All Drivers].[564654 - Andrew]},
[ActivityPerDriverDailyEvents].[All Events].[Thu - 05/01/2006].Children)), Crossjoin({[Driver].[All Drivers].[564654 - Andrew]},
[ActivityPerDriverDailyEvents].[All Events].[Fri - 06/01/2006].Children))
}

Is there any other way of obtaining such a results without hardcoding the dates? Thank you.

Regards,
Roman

Roman,

I believe the function that you are looking for is "Generate".

Generate(<<Set1>>, <<Set2>>[,ALL])

Generate will take the members of the first set supplied to the function and apply them to the second set definition. It is almost like a for each loop in terms of the way it works. In the examples I have below using adventure works, Query 1 will return the US and the half years for 2003. Query 2 will return the US and the children of the half years (quarters) for 2003.

HTH,

- Steve

Query 1:

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,

{{[Customer].[Customer Geography].[Country].&[United States]} *

{[Date].[Calendar].[Calendar Year].&[2003].CHILDREN}} ON ROWS

FROM [Direct Sales]

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

Query 2:

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,

{{[Customer].[Customer Geography].[Country].&[United States]} *

Generate({[Date].[Calendar].[Calendar Year].&[2003].CHILDREN},

{[Date].[Calendar].CurrentMember.Children})} ON ROWS

FROM [Direct Sales]

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE

|||Hi, Steve
In the dialect of MDX (Mondrian) I'm dealing with it would be impossible to use the Generate() method in such a context. So I used Descendants() method instead. And that piece of MDX code from previous post has turned to:

NonEmptyCrossJoin(
{[Driver].[All Drivers].[564654 - Andrew]},
{Descendants([ActivityPerDriverDailyEvents].[All Events])}
)

Thanks for your help!
Roman