Infinity problem

Hi,

I have a table with some database fields and some calculated values. Sometimes it happens that I divide by 0 or null. As a result I get 'Infinity' in my textbox, is it possible to get rid of this 'message'?

greetz

Im not sure what the return value of that message is .... but if its a string containing the word "Infinity" you could try something like this:

Your field that sometimes returns infinity is: CalculatedField
IIf(CalculatedField = "Infinity", "Write your expression when true", CalculatedField)

That expression is used for a new calculated field and that field you can use in a textbox

|||

That could idd be a solution, but isn't there any way to use formatting. I don't like changing the value of my textbox.

greetz

|||

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code). Call that custom code function inside of performing the division directly in the expression.

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

-- Robert

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

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!

Indexing on calculated fields

I would like to index on a calculated field. I need to index records by
w and want to store the W Number. Below is a test schema that
should work but the index will not create.
drop table calcdate;
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,[basedate],0)
);
create index calcdate_idx ON calcdate(calcsun);
I get the error " 37000(1933)[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot create index because the key column 'calcsun' is
non-deterministic or imprecise."
The page
http://msdn.microsoft.com/library/d...>
_08_95v7.asp
says that DATEDIFF is deterministic so I cannot see why the above index
will not create.
All help much appreciated.
GJHello, GJ
I was somehow surprised of this behaviour, too. It turns out that
DATEDIFF is indeed deterministic, but of it's parameters was not: 0 as
a datetime is non-deterministic! The page you quoted says that CONVERT
is deterministic with a datetime only when the style parameter is
specified (and it's not 0, 9, 100 or 109). Therefore, this works (and I
think that you will get the same results):
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,basedate,convert(datetime,'1
9000101',112))
);
create index calcdate_idx ON calcdate(calcsun);
Razvan