So in creating an SSRS report recently which contained percent calculations in the totals I came across a divide by zero error I could not get around. All the checks for null/0 in the world wouldn't get rid using the expression editor.
Here is the formula for the detail level where it works fine:
Margin = (Price-Cost)/Price
and checking for zero values
Margin = Iif((Price-Post)=0 or Price=0),0, (Price-Cost)/Price
But for totals, it throws an error
Margin = (Sum(Price) - Sum(Cost))/Sum(Price)
Margin = Iif(((Sum(Price)-Sum(Cost))=0 or Price=0)),0, (sum(Price)-sum(Cost))/sum(Price)
SHOULD be straight forward,,,,, but not. It appears to be too complex in some way for the expression to be calculated and just returns errors.
So, and I'm borrowing here, I'm going to repost something simple but brilliant that was posted by William Mendoza on his blog site:
In the Menu; got to Report > Report Properties > Code and paste the code bellow
Public Function Quotient(ByVal numerator As Decimal, denominator As Decimal) As Decimal
If denominator = 0 Then
Return 0
Else
Return numerator / denominator
End If
End Function
To call the function go to the the Textbox expresion and type:
=Code.Quotient(SUM(fields!FieldName.Value),SUM(Fields!FieldName2.Value))
in this case I am putting the formula at the Group level so I am using sum. Otherwise it would be:
=Code.Quotient(fields!FieldName.Value,Fields!FieldName2.Value)
Thanks William for your post and help
