CODEWITHSUNDEEP
vb.nett-sqlreporting-services
Serdia
Serdia

Reputation: 4428

how to avoid #Error and NaN in SSRS

I have this expression in my report in order to calculate ratios:

=
SUM(Fields!PaidLosses.Value) / 
Lookup(Fields!YearNum.Value & Fields!MonthNum.Value, Fields!YearStartRisk.Value & Fields!MonthNum.Value, Fields!EarnedPremium.Value, "EarnedAllCoverages")

When I am choosing a parameter with not enough data I got #Error and NaN. The result of NaN is the division of "-" on "-" values. And the result of #Error is the division of "-" on 0 values. enter image description here

Upvotes: 0

Views: 462

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Try:

=SUM(Fields!PaidLosses.Value) / 
IIF(
ISNOTHING(
  Lookup(Fields!YearNum.Value & Fields!MonthNum.Value,
  Fields!YearStartRisk.Value & Fields!MonthNum.Value,
  Fields!EarnedPremium.Value, "EarnedAllCoverages")
) OR 
Lookup(Fields!YearNum.Value & Fields!MonthNum.Value,
Fields!YearStartRisk.Value & Fields!MonthNum.Value,
Fields!EarnedPremium.Value, "EarnedAllCoverages") = 0,1,
Lookup(Fields!YearNum.Value & Fields!MonthNum.Value,
Fields!YearStartRisk.Value & Fields!MonthNum.Value,
Fields!EarnedPremium.Value, "EarnedAllCoverages")
)

This is caused because you cannot divide any number by zero or nothing (SSRS nulls)

Also you can use this custom code provided in this great answer to safely perform divisions.

Add the below code in the custom code textarea in Report menu / Report Properties... / Code tab

Public Function SafeDivide(ByVal Numerator As Decimal, ByVal Denominator As Decimal) As Decimal
    If Denominator = 0 Then
        Return 0
    End If
    Return (Numerator / Denominator)
End Function

Then in your tablix just call the function with the numerator and denominator as arguments:

=Code.SafeDivide(
  SUM(Fields!PaidLosses.Value),
  Lookup(
    Fields!YearNum.Value & Fields!MonthNum.Value,
    Fields!YearStartRisk.Value & Fields!MonthNum.Value,
    Fields!EarnedPremium.Value, "EarnedAllCoverages"
    )
  )

Let me know if this helps.

Upvotes: 1

Related Questions