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

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

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

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

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.

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

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”

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.