Division by zero error in SSRS

Question

I'm trying to debug an error in an SSRS report created by another developer. As far as I can tell, it's a division by zero problem. All the report is doing here is calculating a percentage at the end. The stored procedure converts all null values to zero before returning results, so I can't see what the problem is here. Any ideas?

=
Sum(IIf(Fields!ConstructionTradeDesc.Value = "Journeyman", 
    Fields!BlackF.Value + Fields!HispanicF.Value + Fields!AsianPacificF.Value  + Fields!NativeAmericanF.Value + Fields!OtherF.Value, 
Nothing)) 
/ 
Sum(IIf(Fields!ConstructionTradeDesc.Value = "Journeyman" 
    and 
Fields!BlackM.Value + Fields!BlackF.Value + Fields!HispanicM.Value + Fields!HispanicF.Value + Fields!AsianPacificM.Value + Fields!AsianPacificF.Value + 
Fields!NativeAmericanM.Value + Fields!NativeAmericanF.Value + Fields!OtherM.Value + Fields!OtherF.Value > 0, 

Fields!BlackM.Value + Fields!BlackF.Value + Fields!HispanicM.Value + Fields!HispanicF.Value + Fields!AsianPacificM.Value + Fields!AsianPacificF.Value + 
Fields!NativeAmericanM.Value + Fields!NativeAmericanF.Value + Fields!OtherM.Value + Fields!OtherF.Value, 
1)) 
* 100

Stacia Stacia · Accepted Answer · 2012-07-03T17:08:43

SSRS does not automatically handle divide by zero errors for you, but it looks like you are attempting to address that in the code which would be the correct approach. Are you seeing any errors at all display - such as in the Error List window (which you can open from the View menu)?

The problem could be that you need to place your IIF function on the outside of your expression rather than the inside. So something like this:

=
Iif(Fields!ConstructionTradeDesc.Value = "Journeyman", 
    sum(Fields!BlackF.Value + Fields!HispanicF.Value + Fields!AsianPacificF.Value  + Fields!NativeAmericanF.Value + Fields!OtherF.Value), 
Nothing)
/ 
Iif(Fields!ConstructionTradeDesc.Value = "Journeyman" 
    and 
sum(Fields!BlackM.Value + Fields!BlackF.Value + Fields!HispanicM.Value + Fields!HispanicF.Value + Fields!AsianPacificM.Value + Fields!AsianPacificF.Value + 
Fields!NativeAmericanM.Value + Fields!NativeAmericanF.Value + Fields!OtherM.Value + Fields!OtherF.Value) > 0, 

sum(Fields!BlackM.Value + Fields!BlackF.Value + Fields!HispanicM.Value + Fields!HispanicF.Value + Fields!AsianPacificM.Value + Fields!AsianPacificF.Value + 
Fields!NativeAmericanM.Value + Fields!NativeAmericanF.Value + Fields!OtherM.Value + Fields!OtherF.Value), 
1)
* 100

Division by zero error in SSRS

1 Answers