20

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?!

Pedram
  • 6,256
  • 10
  • 65
  • 87
d90
  • 767
  • 2
  • 10
  • 28
  • You mentioned that there are nulls in your data? Try nesting each field call in a NULL replacement function. For example, I would the format IIF(Fields!PY_Dollars.Value = Nothing, 0, Fields!PY_Dollars.Value = Nothing) for each Fields!PY_Dollars.Value in the current formula. Better yet, handle the nulls in the source query instead. – Eric Hauenstein Oct 04 '13 at 19:59
  • The condition in your `IIF` statement is not checking for zero denominator (you are checking for zero in *CY*, not *PY* value). Instead, try: `=IIF(Sum(Fields!PY_Dollars.Value)=0, 0, (Sum(Fields!CY_Dollars.Value) - Sum(Fields!PY_Dollars.Value))/(Sum(Fields!PY_Dollars.Value))) ` – rpyzh Feb 14 '14 at 03:23

4 Answers4

57

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)
Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
  • can we use Code.Divider() custom function in SQL or MDX within dataset ? Silly but I want something like this to avoid multiline IIF expressions in my huge MDX :( – Aditya May 29 '14 at 13:45
  • 1
    This function can only be used in the report design surface, not in a SQL or MDX query. There are a number of approaches for handling divide-by-zero calculations in MDX, e.g. http://www.bidn.com/blogs/dustinryan/ssis/465/mdx-handle-dividing-by-null-or-0-gracefully, but if you have SQL Server 2012 or later you can use the new Divide() function which does a similar job to the above code. – Nathan Griffiths Jun 03 '14 at 00:41
  • Divide function works like what I want. Thanks alot @Nathan. +1 to comment :) – Aditya Jun 04 '14 at 09:48
  • 1
    dont you have to define a return type? Is it not necessary? – Emil Jan 14 '15 at 09:03
  • 5
    I wish I could upvote this like 1000 times. Thanks man, perfect solution. – LittleTreeX Mar 10 '15 at 21:49
  • Mi Hero!! This worked out excellent! I didn't really know how to add a function to a report before... so I went but it is sooo easy! 1. In Design view, right-click the design surface outside the border of the report and click Report Properties. 2. Click Code. 3. In Custom code, type the code https://msdn.microsoft.com/en-us/library/ms156028.aspx – Gus Mar 27 '15 at 19:27
  • ´help me in my problem =Fields!Saldo.Value*100/Fields!CostoNeto.Value´ – rodrigo.rodriguez Nov 29 '17 at 19:39
  • Great. But you want to hit the Dividend as well with the checks. ie: either the Dividend or Divisor are null or zero then return zero – Gary Thomann Sep 20 '18 at 05:09
  • Why no Float type in ssrs? Getting "There is an error on line n of custom code: [BC30002] Type 'Float' is not defined." – Gary Thomann Sep 20 '18 at 05:33
  • @GaryThomann handling the dividend isn't necessary as zero or null divided by anything just returns zero, not an error. – Nathan Griffiths Sep 20 '18 at 23:18
  • Greetings from the future! It's 2021 and publishing SSRS reports to PowerBI via the API throws an error when you have shared code. I am right now unwinding a bunch of `DivideBy` calls to get this to work. – Nick.Mc May 07 '21 at 03:38
  • Hi Nick, is that by design or due to a bug in the API? – Nathan Griffiths May 07 '21 at 08:31
  • It seems like a bug because these reports can be published manually from the SSRS editor and run fine inside Power BI. – Nick.Mc May 08 '21 at 04:30
  • 1
    So... if anyone has to refactor their shared divide code out, this is an inline method that actually works https://sqldusty.com/2011/08/01/ssrs-expression-iif-statement-divide-by-zero-error/ . Basically `IIF(denominator=0,0,Numerator) / IIF(Denominator=0,1,Denominator)` – Nick.Mc May 12 '21 at 04:24
15

I suspect you're running into the issue where SSRS doesn't actually short circuit an IIF statement; even though you're checking for 0, you're still going to hit a divide by zero error.

Try something like:

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

Using two IIf statements means you avoid the error by using the equation 0/1 when Sum(Fields!PY_Dollars.Value) = 0, thus just returning 0.

Also note that the above expression is checking Sum(Fields!PY_Dollars.Value) = 0, but yours is checking Sum(Fields!CY_Dollars.Value) = 0 - the denominator needs the zero check here.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • I understand the logic and it should work but SSRS doens't calculate it correctly. I still get the error with this. – d90 Oct 08 '13 at 17:19
  • 2
    +1. Here is a post on using double iif statements http://ntsblog.homedev.com.au/index.php/2010/03/12/sql-server-reporting-services-ssrs-iif-statement-divide-by-zero-error/ – Bobby Oct 08 '14 at 08:13
9

To avoid the overhead of maintaining code, the solution below feels tidiest to me. It avoids the division by zero in the denominator by adding the smallest possible Double value to it (Double.Epsilon, which is 4.94e-324). This value's way too small to affect the value of the fraction for anything people would use SSRS for. It also avoids nesting IIF functions.

=IIF(Sum(Fields!Denominator.Value)>0, 
     Sum(Fields!Numerator.Value)/(Sum(Fields!Denominator.Value)+Double.Epsilon),
     nothing)
ambrosen
  • 101
  • 2
  • 5
  • This is the simplest solution. In fact, by adding the Double.Epsilon to the denominator, you don't even need the iif statement anymore. – Nicko Aug 22 '23 at 19:11
0

Why not just express the values as DOUBLE? This avoids the multiple IIF functions? It should have better performance too.

=IIF(Sum(CDbl(Fields!CY_Dollars.Value))=0, 0, (Sum(CDbl(Fields!CY_Dollars.Value)) - 
    Sum(CDbl(Fields!PY_Dollars.Value)))/(Sum(CDbl(Fields!PY_Dollars.Value))))
jmoerdyk
  • 5,544
  • 7
  • 38
  • 49