SSRS Expression Divide by Zero Error

reporting-services ssrs-tablix ssrs-expression
d90 picture d90 · Oct 4, 2013 · Viewed 52.2k times · Source

I have a tablix box that has a division expression. When dividing by zero or nulls I get #Error displayed in my report. I tried to create an IIF statement and tested with static values. This verified my syntax was correct but I still see the error on my reports.

Report Preview

=IIF(Sum(Fields!CY_Dollars.Value)=0, 0, (Sum(Fields!CY_Dollars.Value) - Sum(Fields!PY_Dollars.Value))/(Sum(Fields!PY_Dollars.Value)))

So I'm taking Current year dollars, subtracting Previous year dollars, and dividing that total by previous year dollars to get the percentage change. Is there a trick to this that I'm not getting?!

Answer

Nathan Griffiths picture Nathan Griffiths · Oct 7, 2013

You can add a function to your report code that handles the divide by zero condition, this makes it a bit easier to implement in multiple cells, e.g.

Public Function Divider (ByVal Dividend As Double, ByVal Divisor As Double)
If IsNothing(Divisor) Or Divisor = 0
  Return 0
Else
  Return Dividend/Divisor
End If
End Function

You can then call this in a cell like so:

=Code.Divider(Fields!FieldA.Value, Fields!FieldB.Value)

Related questions

Tablix: Repeat header rows on each page not working - Report Builder 3.0

I have a tablix with lots of rows that span over multiple pages. I have set the Tablix property Repeat header rows on each page but this does not work. I read somewhere that this is a known bug in …

Read More
SSRS How to repeat the table header on each page in print preview

I have an SSRS report, for some reason the header is not repeating on all of the pages in the print preview mode. Below is the image of the table: I have as well checked the Repeat Header rows on …

Read More
How to check if SSRS Textbox is empty

How do you check if a textbox is empty in SSRS 2008? I've tried this code and it doesn't work. IIF(ReportItems!txtCountVolunter.Value = "", false, true)

Read More