MDX hierarchy problem

Hello,

My point is I wonder how to retrieve a leaf member from a date hierarchy.

I would like to retrieve the leaf of [Hierarchy Calendar] at the CurrentMember, which is a Date (as 2007/01/07).
[Hierarchy Calendar] is composed of : Year, Semester, Quarter, Month, Week of year, Date.

I can't retrieve the date of the hierarchy using [Date].[Hierarchy Calendar].[Date].CurrentMember, and I absolutly need to use a hierarchy, not the dimension : [DATE].[Date]

My final MDX must look like that:
IIF(IsEmpty(([Date].[Hierarchy Calendar].CurrentMember,[Measures].[NAV PER SHARE AMOUNT])),

NULL,

IIF(Not IsEmpty([Measures].[NAV PER SHARE AMOUNT]),[Measures].[PRODUCT FIRST NAV DATE] ,([Measures].[FIRST NAV DATE],[Date].[Hierarchy Calendar].[Date].CurrentMember.PrevMember)

)

)

Any help would be appreciated.

Thx,

Damien

Here are two queries that might help point you in the right direction.

Good luck,
Bryan

Code Snippet

withmember [Measures].[x] as

SETTOSTR(DESCENDANTS([Date].[Calendar].CurrentMember,,LEAVES))

select

x on 0,

[Date].[Calendar].[Month].[October 2003] on 1

from [Adventure Works]

;

select

{} on 0,

EXISTS([Date].[Date].[Date].Members,[Date].[Calendar].[Month].[October 2003]) on 1

from [Adventure Works]

;

|||

I know the DESCENDANTS function but I think that's not the solution ...

Maybe another solution ?

|||

How about that EXISTS function (in the second query)?

B.

|||

Thanks for your help.

For information, I resolved my problem with this, it's not very optimized but works under my scheduling constraints

Thanks again.

.FIRSTCHILD:[DATE].[Hierarchy Calendar].CURRENTMEMBER,NOTISEMPTY([Measures].[NAV PER SHARE AMOUNT])),EXCLUDEEMPTY) =0,

([MEASURES].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent.PrevMember),

([DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent,[Measures].[PRODUCT FIRST NAV DATE])

)

)

),

IIF(ISEMPTY(([Measures].[NAV PER SHARE AMOUNT])),

IIF([DATE].[Hierarchy Calendar].CurrentMember.LevelIS [DATE].[Hierarchy Calendar].[Year] ORCOUNT(filter([DATE].[Hierarchy Calendar].CurrentMember.Parent.FirstChild:[DATE].[Hierarchy Calendar].CurrentMember,notisempty([Measures].[NAV PER SHARE AMOUNT])))>0,

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.PrevMember),

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.PrevMember)

),

[Measures].[PRODUCT FIRST NAV DATE]

)

)

)