c# report returns #error

Question

I get #Error in reportviewer when I use the following expression:

    =IIf(Fields!PaymentNumber.Value>0,
         IPmt(Fields!Annual_Interest_Rate.Value/1200,
              Fields!Payment.Value,
              Fields!No_of_Monthly_Payments.Value,
              Fields!Total_Amount_Financed.Value),0)

When I remove the IPmt() function and use some static value instead, it works. Is there a way to find out the exact error ?

Reporting Services uses the VB Runtime Library. Are you using SSRS? — Sir Crispalot, Feb 15 '12 at 23:21
no, i'm not using SSRS, just a simple microsoft reporting services — Kres, Feb 15 '12 at 23:24

Chris Latta · Accepted Answer · 2012-02-16T04:20:16.427

Based on the equation used to calculate an annuity I'd say you are passing either 0 for the number of periods per year or 0 for the interest rate and thus getting a divide by zero error.

You are checking that PaymentNumber is greater than zero, but PaymentNumber is not used in your IPmt calculation so maybe you are using the wrong field in the calculation?

Anyway, regardless of that, you need to be aware IIF is not a statement, it is a function that gets passed two parameters. Therefore those two parameters are evaluated prior to the function call and thus BOTH sides of the "if statement" get evaluated. This can be confusing because you will still get divide by zero errors when you are checking your divisor is non-zero. Just remember it is a function, not a statement, and so there is no short-circuit evaluation to prevent the erroneous part from being executed. However, there are workarounds, mostly involving doing two IIF calls to remove the zero value from being used in the division calculation:

=IIF(Fields!DivideBy.Value <> 0, Fields!Something.Value / IIF(Fields!DivideBy.Value <> 0, Fields!DivideBy.Value, 1), 0)

actually I fixed the values in database, so now I cannot get divide by zero error. And I don't. Because If the PaymentNumber > 0 I get the calculated IPmt, but if it equals to 0 I get #Error.. — Kres, Feb 16 '12 at 18:46

score 0 · Answer 2 · answered Feb 15 '12 at 23:31

It's not particularly easy to debug #Error in Reporting Services.

In my experience, the best way is to break it down into its component parts and see if any of those are the problem. You've already narrowed it down to the IPmt function which is expecting to be passed four Double parameters.

Have you checked that the four parameters you're passing in all have values and are of the correct type?

score 0 · Answer 3 · edited May 23 '17 at 12:20

0

'IIF' in SSRS does not have short-circuit evaluation. Make sure that when you call this function, even when PaymentNumber is 0 or less, that you have valid values for IPmt().

Several workarounds are possible:

edited May 23 '17 at 12:20

Community

1
1

answered Feb 16 '12 at 00:04

Jamie F

23,189
5
61
77

c# report returns #error

3 Answers3