Hi,

The Isssue here is that there is a calculated member in a cube,which
determines the price for a given day. The Cube has data price stored
for a daterange rather than a single date i.e Start Date to End Date.
The Start Date is joined to Time Dimension at date level.
So basically, the idea here is to write a calculated member which will
check the Price for a given date, if it is Empty then it go back to the
previous date and so on, till it finds the first not Empty price value.

Now what happens is that when the gap between start date and end date
is more than 20 days it throws a recursive error.

Below given are the details of the MDX written


IIF([Time].[Financial].CurrentMember.("Business Date Id") =
[Time].[Financial].[All
Time].FirstChild.FirstChild.FirstChild.FirstChild.First Child.Properties("Business
Date Id"),
IIF(NOT ISEMPTY([Measures].[Selling Price Major]),
[Measures].[Selling Price Major],
0),
IIF(NOT ISEMPTY([Measures].[Selling Price Major]),
[Measures].[Selling Price Major],
[Time].[Financial].PrevMember)
)


We've applied SP4 to our analysis server too. Any help in this regard
will be highly appreciated.

Rahul.

What if you use the "is" comparison, like:

[Time].[Financial].[All
Time].FirstChild.FirstChild.FirstChild.FirstChild.First Child,
CoalesceEmpty([Measures].[Selling Price Major]), 0),
IIF(NOT ISEMPTY([Measures].[Selling Price Major]),
[Measures].[Selling Price Major],
[Time].[Financial].PrevMember))

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I think the issue might be that in your "previous period" logic you are
not specifying which measure to use. It therefore uses the current
member in the measures dimension, which is the calculated measure itself
and results in "infinite" recursion.

What I think you need to do is to replace:

[Time].[Financial].PrevMember

with:

([Time].[Financial].PrevMember,[Measures].[Selling Price Major])

which will tell the formula to return the [Selling Price Major] measure
from the previous period, instead of trying to return the value of the
current calculation from the previous period.

eg

IIF([Time].[Financial].CurrentMember.("Business Date Id") =
[Time].[Financial].[All
Time].FirstChild.FirstChild.FirstChild.FirstChild.First Child.Properties
("Business
Date Id"),
IIF(NOT ISEMPTY([Measures].[Selling Price Major]),
[Measures].[Selling Price Major],
0),
IIF(NOT ISEMPTY([Measures].[Selling Price Major]),
[Measures].[Selling Price Major],
([Time].[Financial].PrevMember,[Measures].[Selling Price Major]))
)


Let me know if this works for you.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uUadV2luFHA.3452 (AT) TK2MSFTNGP14 (DOT) phx.gbl>,
deepak_puri (AT) progressive (DOT) com says...

Sorry - scrap my last response. I did not realise that you were actually
trying to implement a recursive function.

Mosha did mention in his blog here
http://sqljunkies.com/WebLog/mosha/a...1/23/6811.aspx
that prior to SP3 the recursive stack used to run out at about 30 calls,
but after SP3 this was significantly increased. So I am not sure why you
would have issues if you have SP4 loaded.


In article <MPG.1d94eb6e848b1e28989699 (AT) news (DOT) microsoft.com>, Darren
Gosbell <dgosbell_at_yahoo_dot_com> says...