Division by 0 is not allowed in Reporting Services/SQL Server. With this simple formula you can prevent division by 0, so the #Error message no longer appears in your report.
= IIF(SUM(Fields!OmzetVorigJaar.Value) + 0 = 0, Nothing,SUM(Fields!MargeVorigJaar.Value) / IIF(SUM(Fields!OmzetVorigJaar.Value)+0=0,1,( SUM(Fields!OmzetVorigJaar.Value) ) ))This formula calculates last year’s margin percentage (margevorigjaar/ omzetvorigjaar).
First you check whether the number you want to divide by is 0 ( number + 0 = 0). If it is, you fill the textbox with Nothing and the rest of the calculation stops. Nothing is the same as NULL in SQL, only with a different name. You can also put an empty string in the textbox: “”. In my experience this can cause problems when you want to use the result of this cell in another cell (ReportItems). The input is then an empty string, and you cannot build formulas with that.
If the number is not 0, the division runs.
Another trick is to add a piece of code to the report:
Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 OR first = 0
Return 0
Else
Return first / second
End If
End Function
Then you use this in your expression:
=Code.Divide(SUM(X), SUM(Y) )
Frequently asked questions
How do I prevent division by 0 and the #Error message in SSRS?
First check whether the denominator is 0 with IIF(SUM(field)+0=0, Nothing, ...). If it is, the textbox shows Nothing and the calculation stops. Otherwise you do the division.
What is Nothing in a Reporting Services expression?
Nothing is the same as NULL in SQL, only with a different name. You can also put an empty string in the textbox. That can cause problems when you use the result of that cell in another cell.
How do I create a Divide function in an SSRS report?
Add a Public Function Divide to the report code that returns 0 when first or second is 0. Otherwise it returns first / second. You call it with =Code.Divide(SUM(X), SUM(Y)).
Why does SSRS show #Error when dividing by zero?
Division by 0 is not allowed in Reporting Services and SQL Server. The division fails, and that is why #Error appears in the report. A check on the denominator prevents it.
How do I calculate last year's margin percentage in SSRS without an error?
Divide MargeVorigJaar by OmzetVorigJaar, but first check whether the sum of OmzetVorigJaar is 0. In that case the expression shows Nothing. Otherwise it divides SUM(MargeVorigJaar) by SUM(OmzetVorigJaar).
Comments
Related blogs
'Show All' in a parameter box
Passing a multi-value parameter to a drillthrough report
Problems when exporting to Excel