I have a report with a field whose value was the expression:
Fields!TotalPrice.Value/Fields!TotalSlots.Value
Although sometimes TotalSlots was blank and thus I was getting a divide by zero runtime error. So I changed the expression to this:
=IIF(Fields!TotalSlots.Value > 0, Fields!TotalPrice.Value/Fields!TotalSlots.Value,"unknown")
but I'm still getting a divide by zero error. How do I work around this zero divisor issue.
You can use FORMAT function to format numbers, e.g. =Format(Fields! MyField. Value,"0.00").
The SSRS expression language is basically VB. According to the MSDN page on Nothing: When checking whether a reference (or nullable value type) variable is null, do not use = Nothing or <> Nothing. Always use Is Nothing or IsNot Nothing.
How to check if a parameter is NULL in SSRS? The IsNothing() function returns True if the Paramter value is NULL , otherwise it will return FALSE.
The VB IIF evaluates all arguments, so it will throw an error if any argument throws an error:
Your formula can be written as:
=IIF(Fields!TotalSlots.Value > 0,
Fields!TotalPrice.Value /
IIF(Fields!TotalSlots.Value > 0,
Fields!TotalSlots.Value,
1 ),
"unknown")
Then even when TotalSlots is zero, the formula still won't encounter a division problem.
Jamie F's answer is correct. As a tip, you can add a function to your report code to make the division a bit easier to implement in multiple cells, e.g.
Public Function Divider (ByVal Dividend As Double, ByVal Divisor As Double)
If IsNothing(Divisor) Or Divisor = 0
Return 0
Else
Return Dividend/Divisor
End If
End Function
You can then call this in a cell like so:
=Code.Divider(Fields!FieldA.Value, Fields!FieldB.Value)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With