Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Divide by zero tweak

  • 8 November 2010
  • Author: DanielBowlin
  • Number of views: 9834
  • 0 Comments

I am sure just about everyone here has run into divide by zero issues.  I have a standard way of handling divide by zero problems that I have found on this, and other sites.  Here is code I found somewhere on a blog or forum, and began using:

Public Function DivideBy(ByVal Exp1, ByVal Exp2)
 If Exp2 = 0 Then
 DivideBy = 0
 Else: DivideBy = Exp1/Exp2
 End If
End Function

This code is very easy to call from the expression builder, and provides a very consistent way to handle divide by zero errors.  I recently was working on a report where I was using a reference to report textbox as the denominator.  That textbox however, had some additional data in the expression that returned a N/A or something similar when the expression had some undesired results due to some bad data.  This was fairly easy to deal with right in the expression, but I thought I might have to deal with this again and set out to modify my function.  I ran into a slight problem in that the code window is not aware of all .Net name spaces.  So I ended up having to use the full name space to get the function working.  So here is my simple tweak that will handle both a divide by zero and a non numeric component in the equation.

Public Function DivideBy(ByVal Exp1, ByVal Exp2)
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

I hope you find this useful.

Print
Categories: Analysis Services
Tags:
Rate this article:
5.0

DanielBowlinDanielBowlin

Other posts by DanielBowlin

Please login or register to post comments.