RDLC expression resulting in #error

Question

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.

Answer 1

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.)

Answer 2

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.