Find last non empty child in CASE statement?

Question

I am aggregating a measure using LastChild.

So, when it comes to serving a YTD total for an incomplete year, I get NULL.

What I want to return is the last non empty child of the member requested. The hierarchy in question has 3 levels, year, quarter and month.

The query I am using is below.

Does anyone have any suggestions?

WITH

 

MEMBER [Measures].[VLDE] AS

 

CASE [Measures].[FactValue]

 

 

WHEN NULL THEN

 

-- Get the hierarchy level requested (1 = year, 2 = QTR)

 

CASE [Time].[Hierarchy].CurrentMember.Level.Ordinal

-- I want to get the last NON EMPTY child here, not just the last child (which could be empty)

 

WHEN 1 THEN ([Time].[Hierarchy].CurrentMember.LastChild, [Measures].[FactValue])

 

 

END

 

ELSE [Measures].[FactValue]

 

 

END

SELECT

 

 

{[Measures].[VLDE]}

 

 

ON AXIS(0)

,{[dCountry].[CountryName].

 

Members}

 

 

ON AXIS(1)

FROM

 

[LocalCube]

WHERE

 

([Time].[Hierarchy].[Year].&[2010])

Answer 1

".. What I want to return is the last non empty child of the member requested .." - so would another "LastNonEmpty" measure on the same field serve your purpose more simply?

---

- Deepak

Answer 2

Yes, much more simply. I wouldn't have thought of that. Thanks DeePak.