avoiding "NaN" , "infinity" in reports

When I use calculation in generating reports, I get "NaN" and "Infinity" values, I wanted to avoid this and display "0" if the result of calculation is null or infinity..

I used the below calculation

=(Fields!approved_tier_cnt.Value)/(Fields!approved_cnt.Value)

Please let me know how to avoid "NaN" and "infinity", while displaying in reports

Regards

Durai

Hello

What I do is tackle those Nan's before they get into RS. On your SQL Server you could make a function something like this:

CREATE FUNCTION formula (
@.Numerator REAL,
@.Denominator REAL,
@.decimals INT = 2,
@.length INT = 10
)
RETURNS VARCHAR(50)
AS BEGIN IF (
@.Denominator IS NULL )
OR (@.Numerator IS NULL )
RETURN '' IF
(@.Denominator <> 0) BEGIN
RETURN str((@.Numerator ) / @.Denominator,@.length - 1, @.decimals)
END
RETURN ''
END

et voila...
put the results in a view

Worf

|||

I would use some explicit type casting and conditional logic.

Firstly to avoid NaN I would make sure the fields are numeric by using one of the standard VB conversion functions e.g. CInt(), CDbl() etc. like this:

=CDbl(Fields!approved_tier_cnt.Value)/CDbl(Fields!approved_cnt.Value)

Then use the Iif() function to check for nulls or check that the divisor is not 0 to avoid infinity

=Iif( Fields!approved_tier_cnt.Value AND
Fields!approved_cnt.Value AND
CDbl(Fields!approved_cnt.Value) != 0
, CDbl(Fields!approved_tier_cnt.Value) / CDbl(Fields!approved_cnt.Value)
, 0
)