Hi



I think this problem must be trivial but I can't quite get it solved.



I have a sales cube (in SSAS 2005) where I have at YTD calculation on QTY,
like this:





Sum(PeriodsToDate([Financial Date].[YQMD].[Year],[Financial
Date].[YQMD].Currentmember),[Measures].[Qty])



And it works just fine. When the user slices on the Time dimension
([Financial Date]) lets say for October 2006 the result is all the items
that are sold in October 2006 and what is sold of theese items YTD. The
problem is when an item is NOT sold in October 2006 I still want to show
what is sold YTD, but the YTD value is missing since it is not sold in
October 2006.



How to solve this?



Thanks in advance.



-Henrik.

Maybe the problem is not in the calculation expression itself (which
looks fine), but in its NON_EMPTY_BEHAVIOR. If you set that to:
[Measures].[Qty], you should clear it, because the YTD is not
necessarily empty (as you observed) whenever that time member is.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi

Thank you, it solves the problem,

But it raises a new one, performance. The query gets very slow , from 5
seconds to 2 minutes when I uncheck the non-empty behavior.

Is there any way to solve this?

Thanks

-Henrik.



"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> skrev i en meddelelse
news:u1l3w15IHHA.5000 (AT) TK2MSFTNGP03 (DOT) phx.gbl...

Could you give an idea of the specific query, the dimensions involved
and the density/sparsity of data?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***