0

I have a simple % change formula I want to implement into a report... the formula being (a-b)/b.

Issue is sometimes the value for b coming into the data set is blank/null... and furthermore SSRS can't ever seem to accurately realize this through the iif() statement I'm using. Instead whenever the report renders, for any line item where data for b in the formula is missing I get the dreaded "ERROR" showing up in that cell. The formula in the cell I'm using is below. What's the issue here? Why can't SSRS see that sum(b) = nothing or isnothing(sum(b)) evaluates to true? It's like it bypasses that part of the formula and goes ahead and does the math, divides by 0 and throws and error?

=iif( 
    isnothing(sum(Fields!BR_Quantity.Value))=true or sum(Fields!BR_Quantity.Value)=0 or sum(Fields!BR_Quantity.Value)=nothing,
    nothing,
    (sum(Fields!Full_Case_Quantity.Value)-sum(Fields!BR_Quantity.Value)) / sum(Fields!BR_Quantity.Value)
)
Dale K
  • 25,246
  • 15
  • 42
  • 71
MattyKluch
  • 11
  • 1
  • 10
  • Possible duplicate of [SSRS 2008 - Dealing with division by zero scenarios](https://stackoverflow.com/questions/5471817/ssrs-2008-dealing-with-division-by-zero-scenarios) – Dale K Feb 21 '19 at 00:22

1 Answers1

0

The IIF function evaluates both the TRUE and FALSE parts of the expression.

Use a separate IIF on both the numerator and denominator:

=IIF(ISNOTHING(SUM(Fields!BR_Quantity.Value)) OR SUM(Fields!BR_Quantity.Value) = 0, 0, SUM(Fields!Full_Case_Quantity.Value) - SUM(Fields!BR_Quantity.Value)
/
 IIF(ISNOTHING(SUM(Fields!BR_Quantity.Value)) OR SUM(Fields!BR_Quantity.Value) = 0, 1, SUM(Fields!BR_Quantity.Value) )

This way when your denominator is 0 or NULL, the resulting expression is 0 / 1 .

Kinda lame but Microsoft won't fix it.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Ok, I will try this method tomorrow. I totally thought it was lame that the iif() function doesn't work like in t-SQL – MattyKluch Feb 21 '19 at 02:11
  • Whats strange about it is that it works without errors if numbers are integers. Also using Cdbl function like =Iif(Fields!b.Value=0, 0 , Cdbl(Fields!a.Value)/Cdbl(Fields!b.Value)) seems to remove the error from the expression – niktrs Feb 21 '19 at 09:02
  • The best approach in my opinion is by using custom code like in this answer https://stackoverflow.com/questions/19189132/ssrs-expression-divide-by-zero-error . Simple vb code and makes your expressions more readable – niktrs Feb 21 '19 at 17:29