How to fix #Error when dividing by 0 in SSRS

Question

So I am aware this has been answered before,

here:

SSRS 2008 - Dealing with division by zero scenarios

and here:

https://sqldusty.com/2011/08/01/ssrs-expression-iif-statement-divide-by-zero-error/

and I'm sure these work for what I need. However, I'm struggling where to put the different parts of the Syntax as

A) I'm not very good at this but I am trying my best!

and

B) The code is awkwardly long

This is my code which produced #Error

=SUM(Fields!OperationalCharge.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalCharge.Value, "WaterBillingData") - Fields!OperationalChargePreviousMonth.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData"))

/ SUM(Fields!OperationalChargePreviousMonth.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData")) * 100

And this is what I have tried:

=Iif(SUM(Fields!OperationalCharge.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalCharge.Value, "WaterBillingData") - Fields!OperationalChargePreviousMonth.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData")) = 0, 0,
SUM(Fields!OperationalCharge.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalCharge.Value, "WaterBillingData") - Fields!OperationalChargePreviousMonth.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData")) / SUM(Fields!OperationalChargePreviousMonth.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData")) * 100 ) = 0, 1, 
Iif(SUM(Fields!OperationalCharge.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalCharge.Value, "WaterBillingData") - Fields!OperationalChargePreviousMonth.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData"))

However, this then gives me the error:

The Value expression for the Textrun 'Col.name.Paragraphs[0].TextRuns[0]' Contains an error: [BC30205] End of statement expected

I'm half thinking I've actually got this right, however as I've changed it, it doesn't like something in the update from other things I've read while trying to resolve this.

Ideally I want any 0/0 to show N/a or -. I'm aware the above code I tried doesn't do this, but when I tried to how this as well, I also came across the same error

Any help would be gratefully received!

Cheers,

Wil

score 1 · Accepted Answer · answered Jul 29 '19 at 12:37

Your issue may be in this IIF statement.

Iif(SUM(Fields!OperationalCharge.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalCharge.Value, "WaterBillingData") - Fields!OperationalChargePreviousMonth.Value + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData"))

It appears you have the Boolean test but not the True or False parts of the statement. It states IIF theSUM. Not what it equals and nothing for the True or False parts.

This was it, apologies I should have commented sooner! Thanks a lot for your help here, it saved me even more head scratching — Wil-Liam, Sep 24 '19 at 09:39

score 1 · Answer 2 · answered Jul 29 '19 at 14:15

First thing I'll mention before I even looked at the expression - you need to be trying to calculate some of this stuff at the query level. If you can join these tables instead of using all these lookups, you will save yourself loads of time and effort dealing with this sort of issue. If that is not a possible solution, there is definitely something missing in the expression.

The first conditional SUM looks okay from what I can tell, as does the numerator from the false condition. The denominator is where your issues arise. It looks to me like you simply misplaced the IIF. On top of that, you have a misplaced parenthesis. You'll need to move the IIF to right after the division sign, remove the parenthesis from after * 100 and move it to the end of the expression. Basically, the end of your expression should look like the following.

/ IIF(SUM(Fields!OperationalChargePreviousMonth.Value
            + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData")) * 100 = 0,
            1, 
            SUM(Fields!OperationalCharge.Value 
                + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalCharge.Value, "WaterBillingData") 
                - Fields!OperationalChargePreviousMonth.Value 
                + Lookup(Fields!InvoiceDate.Value,Fields!InvoiceDate.Value, Fields!OperationalChargePreviousMonth.Value, "WaterBillingData")))

FYI, a program like Notepad++ can be very useful in diagnosing these sorts of issues. I used it to format the expression and use it's useful syntax highlighting to match parethesis to its terminating partner.

Thank you very much for this After reading what you said I'm going to try and re-write this at query level as well to speed the whole process up — Wil-Liam, Sep 24 '19 at 09:42

How to fix #Error when dividing by 0 in SSRS

2 Answers2