MDX: Getting the first child at a lower level

I'm trying to get the first day in a specific week or month or year. I've tried with HEAD, but the following measure returns #Error for every row.

Isn't HEAD returning a member ?

What am I doing wrong ? I have tried TopCount too.

member [Measures].[LY] as

(head(descendants([Calendar].[Fiscal].currentmember,[Calendar].[Fiscal].[Day]),1), [Measures].[Sales])

... rows ...

where ([Calendar].[Fiscal].[Month].[2007-02-26])

You are using hierarchy [Calendar].[Fiscal] in Axis 0 and in WHERE clause. You can use same hierarchy just in one place.

So you should do

member [Measures].[LY] as

(head(descendants([Calendar].[Fiscal].[Month].[2007-02-26],[Calendar].[Fiscal].[Day]),1), [Measures].[Sales])

... rows ...

And: Head returns set, not member.

Vidas Matelis

|||

Thanks Vidas,

How do I get the first member of the set ?

|||

Alan,

I pointed about member versus set as head function can return more than 1 member if specified, so it returns set. This will work without you actually worrying if this is set or tuple or member and that is nice feature of SSAS 2005.

Function .Item(0) gets first tuple from set or first member from tuple.

Vidas Matelis

|||That did it ! Many thanks.