Infinity Issue in the Expression SSRS

# Infinity Issue in the Expression SSRS

• Monday, April 30, 2012 1:14 PM

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

### All Replies

• Monday, April 30, 2012 2:24 PM

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

• Monday, April 30, 2012 2:31 PM

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

• Monday, April 30, 2012 2:35 PM

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

• Monday, April 30, 2012 2:59 PM

I was getting this time #Error

This error

• Monday, April 30, 2012 3:03 PM

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

• Monday, April 30, 2012 3:35 PM

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```

• Edited by Monday, April 30, 2012 3:36 PM Extra info
•
• Tuesday, May 01, 2012 3:18 AM

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

• Proposed As Answer by Tuesday, May 01, 2012 5:16 AM
• Marked As Answer by Tuesday, May 01, 2012 9:46 AM
•
• Tuesday, May 01, 2012 4:58 AM
Moderator

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

```-- for displaying nothing