Menu
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.
=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?!
374
SSRS does not short circuit IIF arguments. Therefore, using a single IIF function to screen for division by zero will have no effect and give an #ERROR value. Instead, a pair of nested IIF statements can be used. The outer IIF controls the value returned in the case of division by zero, 0 in the example above.
Using IIF Function in SSRS We are going to add a new field to the report data set to determine if the Order Year is the Max or Current Year. As shown below, the dataset properties window is opened, and the Fields tab is selected. After clicking Add, at the bottom of the list a new field is added.
You can use FORMAT function to format numbers, e.g. =Format(Fields! MyField. Value,"0.00").
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
