Divide by zero in SSRS variance calculation

Question

Post reply

Divide by zero in SSRS variance calculation

Sachin 80451

SSCommitted

Points: 1642
More actions
March 28, 2012 at 4:12 am

#253857

I keep getting the following error: "rsRuntimeErrorInExpression The value expression for the textbox contains an error: Attempted to divide by zero.".
I've tried this:
=IIF(ROUND(((Fields!TG___2012_YTD.Value - Fields!TG___2011_YTD.Value)/Fields!TG___2011_YTD.Value)*100,1) & " % " IS NOTHING,"Null",ROUND(((Fields!TG___2012_YTD.Value - Fields!TG___2011_YTD.Value)/Fields!TG___2011_YTD.Value)*100,1) & " % ")
And this:
=IIF((Fields!TG___2012_YTD.Value) = 0,0,
((Fields!TG___2012_YTD.Value) - (Fields!TG___2011_YTD.Value)/Fields!TG___2011_YTD.Value) *100)
Can anyone see what I'm missing?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113514 More actions · Answer 1

=IIF((Fields!TG___2012_YTD.Value) = 0,0,

((Fields!TG___2012_YTD.Value) - (Fields!TG___2011_YTD.Value)/Fields!TG___2011_YTD.Value) *100)

the problem on the above is the second 0 as your still passing in 0 to divide by, change it to 1 so that it divides by 1 to get itself

Sachin 80451 SSCommitted Points: 1642 More actions · Answer 2

Same Problem?

for e.g.

My Field!TG__2012_YTD.Value is '0' and

Field!TG__2011_YTD.Value is 646?

Ant-Green SSC Guru Points: 113514 More actions · Answer 3

as your dividing by ytd2011 thats the one you need to check is 0, not ytd2012, missed that at first look

Sachin 80451 SSCommitted Points: 1642 More actions · Answer 4

It now gives me the answer 1. When the answer should be 100. (from the calculation)?

Ant-Green SSC Guru Points: 113514 More actions · Answer 5

what is your expression with the change to ytd2011 and what value fro ytd2012 is being used 0 still?

Sachin 80451 SSCommitted Points: 1642 More actions · Answer 6

I have managed to get the following formula working in Excel where 2011 = 0 and 2012 = 9454 and it does not give me a divide by zero error until I put this in SSRS!

=IIF(Fields!TG___2011_YTD.Value = Fields!TG___2012_YTD.Value, 0,IIF(Fields!TG___2011_YTD.Value <= 0,IIF(Fields!TG___2012_YTD.Value > 0, "8", 0),

(Fields!TG___2012_YTD.Value - Fields!TG___2011_YTD.Value)/(Fields!TG___2011_YTD.Value)))

????

Dave Klug SSCertifiable Points: 5811 More actions · Answer 7

I think the issue you're having is that SSRS evaluates the entire expression prior to doing the comparsions, so you'll always be dividing by zero. I wrote custome code to get around it and just call that rather the using the IIF statement.

DaneChollette SSC Journeyman Points: 77 More actions · Answer 8

I've been having this same issue and tried following all the examples given and could not get it working.

My method of Fixing this was I used the general calculation, = IIF (Fields!Field1.value = 0, 0, (Fields!Field2.value / Fields!1.value)...... I would still get the error.

I then went to the Text Box Property where I have my calculation and set the visibility to show or hide based on expression and set it to: = IIF (Fields!Fields1.value = 0, True, False)

It may not be the fanciest way but it works for me.

Possinator Hall of Fame Points: 3452 More actions · Answer 9

Add custom code to rdl (report properties --> code pane):

Public Function DivideBy(ByVal Numerator, ByVal Denominator)

If Denominator = 0 Then

DivideBy = 0

Else : DivideBy = Numerator / Denominator

End If

End Function

Add to expression as:

=Code.DivideBy(Fields![Numerator].Value, Fields![Denominator].Value)

Where you replace [Numerator] and [Denominator] with the field values you want to divide

[font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Divide by zero in SSRS variance calculation

Cookies on SQLServerCentral