How to Avoid Divide by Zero in Custom Code Function Calls in SQL Server Reporting Services Reports
If you are a developer or if you are dealing with scripting or programming even not professionally, I believe you probably know what kind of an error you will get if you divide a value by zero (0).
I know also and I try to avoid divide by zero conditions in reports that I have been developing using the Microsoft SQL Server 2005 / 2008 Reporting Services (SSRS).
I always try to check if the divisor is 0 or not. So I have managed to avoid division by zero conditions successfully with the help of "IIF" conditional function.
But recently I have realized that if you are using Custom Code and if you are going to call a custom code function if the divisor is not 0 then you should take a step further otherwise report engine again will throw error.
I will try to explain the situation which causes division by zero error with a sample Reporting Services report and a few textbox placed on the layout and edit some expressions for each textbox to demonstrate the problem case.
GREEN TEXTBOX EXPRESSION WITHOUT CUSTOM CODE FUNCTION CALL
OK, the below code is checking the divisor by the help of an IIF control and if the divisor equals to zero (0), then display an empty string on the result field.
That is nice but there is not a Custom Code function call in this expression.
= IIF(
ReportItems("textbox2").Value = 0,
"",
ReportItems("textbox1").Value * 100 / ReportItems("textbox2").Value
)
RED TEXTBOX EXPRESSION
Here is a function build in the Custom Code section of the SQL Server Reporting Services report.
Although there is an IIF conditional statement and checks whether the divisor is zero, in the sample you will see that this control is not enough to prevent the textbox expression throw an error.
I was hoping the report engine not to evaluate the last part (FALSE part) of the IIF statement, but I realized that although the expression evaluates to TRUE the report engine checks also the FALSE part with the values in each part of the IIF condition.
= IIF(
ReportItems("textbox2").Value = 0,
"",
Code.SampleCustomCodeFunction(
ReportItems("textbox1").Value * 100 / ReportItems("textbox2").Value
)
)
BLACK TEXTBOX EXPRESSION
You can see that the below expression code is same as the above one except the FALSE condition statements of the IIF call.
So this expression is our test case to see whether the report server engine error is coming from the Custom Code function call.
= IIF(
ReportItems("textbox2").Value = 0,
"",
"N/A"
)
GREEN TEXTBOX EXPRESSION
And here is what should I do as a Reporting Services report developer to avoid divide by 0 or division by zero cases.
You see, I also added a second IIF condition in the parameter section of the Custom Code function call.
Here the parameter value is controlled to see that it is different than 0 and then passes an approtiate value in case of the parameter is zero.
= IIF (
ReportItems("textbox2").Value = 0,
"",
Code.SampleCustomCodeFunction(
IIF(
ReportItems("textbox2").Value = 0,
0,
ReportItems("textbox1").Value * 100 / ReportItems("textbox2").Value
)
)
)
So as a summary, to prevent divide by zero errors be carefull with custom functions.
If you are calling custom code functions add an additional IIF if required.