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
)

Averages with multi-select

I am trying to write an MDX calculation that calculates the average policy premium across multiple policies. It works fine when selecting one carrier. However, the avg function is basically based on the count and sum functions (at least that is what I read). When you select one carrier the average is based on the following:

Policy A: 2000

Policy B: 1000

Policy C: 3000

Average: 2000

Lets say I multi-select another carrier and the second carrier has the exact same amounts for the policies

the total policy amounts would be :

Policy A: 4000

Policy B: 2000

Policy C: 6000

Average: 2000

The summation (12000) should be divided by the number of policies * the number of carriers (6).

However, I wind up getting 4000 as my average. Any thoughts?

Here is the MDX I am using:

Total Policy Premiums:

sum(existing [POLICY].[POLICY #],([POLICY].[VEHICLE #].&[1],[measures].[TOTAL POLICY PREM]))

Avg:

avg(existing [POLICY].[Policy].children,[Measures].[Total Policy Premiums])

Any insight would be greatly appreciated.

You could do this for example:

AVG ( Existing CrossJoin( [Carrier].[Carrier].members, [Policy].[Policy #].members ),

[Measures].[TOTAL POLICY PREM] )

Average can be computed in multiple ways, so you need to think through on what exactly you are computing. In the MDX above, combinations of carrier and policy that do not exist or are null will not be included in the computation.