Infinity Issue in the Expression SSRS

Question

Hi Friends,

I have written one expression but it is showing Infinity for one of the row.

=iif(sum(Fields!Net_Amount.Value) is nothing,0,
(sum(iif(right(Fields!Fiscal_Year.Value,4)=2012,Fields!Net_Amount.Value,nothing))-
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))/
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))

but 2011 not existing for that customer.

Can anyone tell me where I am doing mistake ... Thanks for your help

Answer 1

Division by zero is not possible, it's a mathematical nonsense.

Your code should check if the divisor is 0.

If divisor <>0 then compute your calculation

If divisor = 0 then show an error message, leave that field blank or show a value that fits your business rules (could even be a 0 in some cases)

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

Thanks for your information .

I tried in this way but still it is not working.

where I am doing mistake ? Thanks for your help....

=iif(sum(Fields!Net_Amount.Value) is nothing,0,
(sum(iif(right(Fields!Fiscal_Year.Value,4)=2012,Fields!Net_Amount.Value,nothing))-
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))/
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,1)))

Answer 3

What is the error message this time? Still "infinity"?

If that's the case, you're still dividing by Zero.

Print the divisor expression on a textbox (or debug it)

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 4

I was getting this time #Error

This error

Answer 5

So, this time you'll probably computing an operation where one of the operators is Nothing.

Nothing / 2 = Error

28 / Nothing = Error

(Nothing -1) / 7.65 = Error

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 6

Now I am checking part by part now.

Initially I took this part

=sum((iif(right(Fields!Fiscal_Year.Value,4)=2012,Fields!Net_Amount.Value,nothing)))

It is working fine. It is giving only  2012 value thats fine. But when I am placing Zero in place of nothing

I was getting error why is that

=sum((iif(right(Fields!Fiscal_Year.Value,4)=2012,Fields!Net_Amount.Value,0)))

This is the Error Message:

Build complete -- 0 errors, 0 warnings
[rsAggregateOfMixedDataTypes] The Value expression for the textrun ‘Textbox28.Paragraphs[0].TextRuns[0]’ uses an aggregate function on data of varying data types.  Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.
Preview complete -- 0 errors, 1 warnings

Answer 7

Your code:

=iif(sum(Fields!Net_Amount.Value) is nothing,0,
(sum(iif(right(Fields!Fiscal_Year.Value,4)=2012,Fields!Net_Amount.Value,nothing))-
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))/
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))

New Code:

=iif(sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)) is nothing,0,
(sum(iif(right(Fields!Fiscal_Year.Value,4)=2012,Fields!Net_Amount.Value,nothing))-
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))/
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))

Answer 8

Hi there

Please put these expression whatever suits you

One will display nothing when there is no value for FY2011 and one will display 0 if there is no value for FY2011.

I am putting screenshot for you

Many thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

-- for displaying nothing
=iif(sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)) is nothing,nothing,
(sum(iif(right(Fields!Fiscal_Year.Value,4)=2012,Fields!Net_Amount.Value,nothing))-
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))/
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))
--for displaying 0
=iif(sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)) is nothing,0,
(sum(iif(right(Fields!Fiscal_Year.Value,4)=2012,Fields!Net_Amount.Value,nothing))-
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))/
sum(iif(right(Fields!Fiscal_Year.Value,4)=2011,Fields!Net_Amount.Value,nothing)))