4

I have two decimal fields, profit and revenue. They are displayed in a tablix control, each has their own column. In a third column, I want to divide profit by revenue. The result when either of those fields is zero is #error, this I'm guessing is due to the dividing by zero. I came up with the following expression to solve this:

=iif(Cint(Fields!revenue.Value) = 0 orelse cint(Fields!profit.Value) = 0 ,"",FormatPercent(Fields!profit.Value / Fields!revenue.Value,2))

That expression still results in #error. I did some testing and took out the false portion of the expression. The expression looked like this:

=iif(Cint(Fields!revenue.Value) = 0 orelse cint(Fields!profit.Value) = 0 ,"No","Divide")

When running that expression, the original spots that had #error, now show "No". That tells me that expression is working like I would expect, but why does it throw the #error when I add the division in the false condition. It should not be hitting that part of the expression. Any help is appreciated. I also tried a switch statement but the results were the same. It threw the #error anytime I had the division in the expression.

Phil Hannent
  • 12,047
  • 17
  • 71
  • 118
HunterX3
  • 338
  • 1
  • 6
  • 18
  • Just wanted to add, don't forget to look in the Debug output window in Visual Studio. The reason for the error will be shown there. – ShawnOrr Jan 20 '22 at 18:15

2 Answers2

12

Very similar to: Reporting Services expression gives error in some circumstances

IIF evaluates all arguments. If any argument generates an error, then the entire function will throw an error, regardless of which of the three arguments should have been returned.

Try this code:

=iif(Cint(Fields!revenue.Value) = 0,"",FormatPercent(Fields!profit.Value / iif(Cint(Fields!revenue.Value) = 0, Fields!revenue.Value, 1 ),2))

This code has a second iif that keeps any argument from ever dividing by zero. (I edited the code directly into browser: it may need minor tweaks. Also, you really should use cell or placeholder properties to format as a percent, not your expression.)

Community
  • 1
  • 1
Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • Thanks for that. I didn't know about it evaluating both sides of the expression. – HunterX3 Jun 07 '11 at 16:13
  • Wow, I was about to pull my hair out over this same exact scenario. Thanks for the info! – robertmiles3 Nov 17 '11 at 14:21
  • WOW!! It would be nice if M$ would get around to fixing this so we wouldn't spend so much time cracking our heads trying to find a work-around for this. RIDICULOUS!!! – cChacon Jul 21 '16 at 19:20
  • Thanks, I had problem with **negative numbers**, I Solved it using `Math.Abs((Sum(Fields!Amount.Value))` – Shaiju T Aug 30 '16 at 17:14
1

Watch out,

It seem like you can't use unsupported Datatype in a report iif expression.

here is what I had:

=IIf(Fields!MyNotSoComplexObject.Value is nothing, "No object", Fields!MyOtherField.Value )

When "MyNotSoComplexObject" was null everything was working, when it was assign at something, I had #error.

I discovered this by trying to display directly "MyNotSoComplexObject" in the report.

My work arround was to put a bool value in my dataset that verify if "MyNotSoComplexObject" has value.

Guish
  • 4,968
  • 1
  • 37
  • 39