SSRS Expression #Error - Calculate variable shows error when dividing by zero

Question

I am currently using a formula to calculate a variable, which looks like:

=(Fields!MontantInitialRegime.Value/Fields!MontantInitial.Value)*Fields!SoldeFacture.Value

It works well, except for the cases where both numerator and denominator equal zero.

I've done some research and I've tried to solve it by using the formula below, which doesn't work.

=IIF(Fields!MontantInitialRegime.Value = 0, Fields!SoldeFacture.Value, Fields!MontantInitialRegime.Value /
IIF(Fields!MontantInitial.Value = 0, Fields!SoldeJour.Value, (Fields!MontantInitialRegime.Value/Fields!MontantInitial.Value)*Fields!SoldeFacture.Value))

Please note that if the result of the division is an error, I'd like to show Fields!SoldeFacture.Value.

Here's an example of the current output:

Disjointed

Answer 1

Ok, if I understand you correctly, I think you need to Fields!SoldeFacture.Value when a division error would happen.

The division error is happening only when the denominator is 0, 0 / 5 = 0 but 5 / 0 = error!

So, with your existing formula as you say, you're nearly there:

=(Fields!MontantInitialRegime.Value/Fields!MontantInitial.Value)*Fields!SoldeFacture.Value

All we need to do is add the Immediate IF condition around it to check if the denominator is 0 or not:

=IIF(Fields!MontantInitial.Value = 0, Fields!SoldeFacture.Value, (Fields!MontantInitialRegime.Value/Fields!MontantInitial.Value)*Fields!SoldeFacture.Value)

By way of explanation, the following:

=IIF(Fields!MontantInitialRegime.Value = 0, Fields!SoldeFacture.Value, Fields!MontantInitialRegime.Value / IIF(Fields!MontantInitial.Value = 0, Fields!SoldeJour.Value, (Fields!MontantInitialRegime.Value/Fields!MontantInitial.Value)*Fields!SoldeFacture.Value))

is basically saying if Fields!MontantInitialRegime.Value = 0 then use the value in Fields!SoldeFacture.Value and divide by Fields!SoldeJour.Value if Fields!MontantInitial.Value = 0 or do the initial calc ((Fields!MontantInitialRegime.Value/Fields!MontantInitial.Value)*Fields!SoldeFacture.Value)) with 0's in the table you will always end up with a divide by zero here.

Hope this helps.

Lee.

Acolyte

Thank you very much for the answer, Lee! The syntax is correct and accepted, but I continue to see the error. If SoldeFacture is negative, could it possibly be a problem? In fact, I made a little mistake, because, SoldeJour IS SoldeFacture, I only forgot to change its name. – Disjointed

I mean, what I inserted now was: =IIF(Fields!MontantInitialRegime.Value = 0, Fields!SoldeJour.Value, Fields!MontantInitialRegime.Value/IIF(Fields!MontantInitial.Value = 0, Fields!SoldeJour.Value, (Fields!MontantInitialRegime.Value/Fields!MontantInitial.Value)*Fields!SoldeJour.Value)) – Disjointed

Answer 2

This should work:

If Fields!MontantInitial.Value = 0 (ERROR) then show Fields!SoldeFacture.Value as requested, else go ahead an do your calculation.

=IIf(Fields!MontantInitial.Value = 0, Fields!SoldeFacture.Value, Fields!MontantInitialRegime.Value/Fields!MontantInitial.Value)*Fields!SoldeFacture.Value)

Nummular

GrandRalph, thank you for your answer and your time! Unfortunately I continue to see the error. – Disjointed

Answer 3

I finally found the answer to my problem: =IIF((Fields!MontantInitial.Value <= 0 OR IsNothing(Fields!MontantInitial.Value)), Fields!SoldeJour.Value, (Fields!MontantInitialRegime.Value/IIF(Fields!MontantInitial.Value <= 0 OR IsNothing(Fields!MontantInitial.Value), 1, Fields!MontantInitial.Value))*Fields!SoldeJour.Value)

I put another IIF as suggested and replaced the variable which would correspond to 1 for 1 itself, as suggested in the answer below and it works well!

SSRS 2008 - Dealing with division by zero scenarios

Thank you for your collaboration! :)

Disjointed