SSRS Expression #Error - Calculate variable shows

问题:

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:

回答1:

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)

回答2:

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.

回答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! :)