Getting rid of NaN, Infinity and #error in your Reporting Services report

Because of Reporting Services inability to handle some totals when pulling data from Analysis Services,
I ended up writing a lot of expressions. Instead of having to put a bunch of IIF statements in the expression,
we came up with a code block that handles the following conditions…
 
Division:
If the numerator or the denominator is equal to 0 or is null
 
Subtraction
if the numerator or the denominator is a non numeric character. (we did this because the client wanted " – " if the value was 0
 
Addition
same conditions as Subtraction
 
multiply by 100 (also called Index)
We had some calculations that the client wanted to see as an index. So this code was added to do a "safe" division and then multiply by 100
 
 
Public Function SafeDivide(Numerator as String, Denominator as String) as String
Try         
If Numerator = “” or Denominator = “” then
Return “-“
End if
If Numerator = “-“ or Denominator = “-“ then
Return “-“
End If
If CDbl(Numerator) =0 or CDbl(Denominator) = 0 then
Return “-“
End if
Return String.format((Numerator / Denominator ).ToString("0.0"))
Catch
Return "-"
End Try
End Function
Public Function SafeDivideIndex(Numerator as String, Denominator as String) as String
Try         
If Numerator = “” or Denominator = “” then
Return “-“
End if
If Numerator = “-“ or Denominator = “-“ then
Return “-“
End If
If CDbl(Numerator) =0 or CDbl(Denominator) = 0 then
Return “-“
End if
Return String.format((Numerator * 100/ Denominator ).ToString("0.0"))
Catch
Return "-"
End Try
End Function
Public Function SafeSubtract(Numerator as String, Denominator as String) as String
If Numerator = "" or Denominator = "" then
Return "-"
End If
If Numerator = “-“ or Denominator = “-“ then
Return “-“
End If
Try
Return String.format((Numerator – Denominator ).ToString("0.000"))
Catch
Return "-"
End Try
End Function
Public Function SafeAdd(Numerator as String, Denominator as String) as String
If Numerator = "" or Denominator = "" then
Return "-"
End If
If Numerator = “-“ or Denominator = “-“ then
Return “-“
End If
Try
Return String.format((Numerator + Denominator ).ToString("0.000"))
Catch
Return "-"
End Try
End Function
This code can be added to your SSRS reports by right clicking outside of the matrix/data regions and selecting Report Properties–>Code and paste the code in there. Within an expression, you can call the code like this
 
Code.SafeDivide(MyNumerator,MyDenominator)
 
This sure made things much simpler.
 
Special thanks to Elia for doing the majority of the work on this code.
 
Advertisements

3 thoughts on “Getting rid of NaN, Infinity and #error in your Reporting Services report

  1. Thanks; your code works well when there is real error, i.e division by Zero. But when everything is OK, i.e both Numerator and denominator are valid numbers, i get this error error BC30455 “Arguments not specified for parameter ‘Denominator’ of Public Function SafeDivide”

  2. This solution works perfectly!! Thank you!

    Only one remark: SSRS is throwing an error stating that there is an error on line 40 of the custom code: [BC30037] Character is not valid.

    I only used the first (safedivide) function and just left the rest out so I didn’t debug it further.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s