Monday, March 26, 2012

Infinitive recursion for my AS2000 calculated member

Hi,

Can someone please help me on this. I get following error when I browse my virtual AS2000 cube:

<Infinite recursion detected during execution of calculated member Sum({Descendants....>

I have implemented a 'dummy' utility dimension with one calculated member: (I have used a parentchild dimension because that's only way I know how to get the formula a from source view):

--

view [dbo].[vdimUtilityCalculation] as

select
'CalculationID' = 1,
'ParentID' = 1,
'CalculationName' = 'Currency',
'Formula' = 'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))',
'MemberOption' = 'SOLVE_ORDER=''-1'''
--

Any idea?

Thanks, Christer

You need to change coordinate in the utility dimension in order to prevent infinite recursion. I.e. something like that:

'Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", (UtilityDim.DefaultMember,[Amount Fixr]), (UtilityDim.DefaultMember,[Amount Flor])) * ValidMeasure([Rate]))',

|||

Thanks for your reply.

I added a second 'dummy' member to my utility dimension and pointed to in the formula like this:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])},IIF([Currency].CurrentMember.Properties("Fixed") = "1", ([CalculationUtility].&[2],[Amount Fixr]), ([CalculationUtility].&[2],[Amount Flor])) * ValidMeasure([Rate]))

It works for some accounts (account dimension with calculated members), not accounts with formula like account1/account2, they are not calculated correctly... and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

Not sure how to get this working, is this easier to implement in AS 2005?

Thanks, Christer

|||

and it don't work with Aggregate function (Excel , filter multiselect) and that was the main to use utility dimension with solev_order = -1...

It should work. I suggest you start troubleshooting by creating a calculated member in this utility dimension with SOLVE_ORDER=-1 and then sending MDX query with Aggregate over Period dimension. It isn't easy to help through newsgroup, but if you will take it slow, step by step, you should be able to see that it does work.

|||

Thanks! After some struggle I got it to work!

sql

No comments:

Post a Comment