SSRS Divide by zero
How to handle a divide by zero is a problem every developer runs into from time to time. I used to run into this issue quite frequently, and I started to get tired of typing the same code over and over again. I wanted something a little cleaner that would handle a few different issues I encountered when dividing one column by another in SSRS.
I started with a search and I found a small block of code that I could embed in my report templates that would handle divide by zero quite easily. However, once in a while one of my columns would not return a number. Instead it would return something like N/A or Unknown. This caused the solution I had found to fail. I spent some more time searching and I couldn’t really find anything that would work easily in SSRS. So, I changed my search parameters to see if I could modify the embedded code I was already using. I didn’t find any SSRS specific answers but when I refined my search to .Net code that would handle this situation I got a bit closer. I made some modifications that I thought would work, but they didn’t. It turned out that SSRS does not include all the .Net namespaces. I had to explicitly declare the entire namespace path to the function I was using. What I ended up with is:
Public Function DivideBy(ByVal Exp1, ByVal Exp2) AS If Microsoft.VisualBasic.IsNumeric(Exp1) And Microsoft.VisualBasic.IsNumeric(Exp2) Then If Exp2 = 0 Then DivideBy = 0 Else: DivideBy = Exp1/Exp2 End If Else DivideBy = "N/A" End If End Function
This will first evaluate if the inputs are numbers at all. If both inputs are numbers, then it will evaluate if either of them are zero. If one of the inputs is zero, then it will return a zero. If neither of the inputs is zero, but they are both numeric, then it will return the result of the division. However if either input is not a number, then it will return an N/A. This gave the result I was looking for without any additional coding or error handling in the report itself.
Embedding .Net code in SSRS.
The first thing you need to do is to embed the code above into your report template. You do use a report template, don’t you? With your report, or template loaded onto the design surface, click the Report menu and choose Report Properties.
Once you have the properties dialog open, choose the Code tab. Next paste the code above into the Custom Code window of the tab and click OK.
Now the code is embedded in your report. Next you have to know how to use the code.
Using embedded divide by zero code
In order to use this code, open up an expression for the text box where you want the division to take place and use this format for the code in your expression:
It is really that easy. The first input value is the field you will use for your numerator, and the second is the field you will use as your denominator. If you embed this code in your report template, it will always be there for you to use. The only thing you will have to remember is how to call the code. Once you have done that a few times, it will become second nature.
I hope you find this information useful Thanks for reading.