Erin Leale

asked on

# SSRS "If - then - else" formula #error

Hello!

I'm new to SSRS - using - Report Builder 3 with Epicor. I have a formula that should prevent a divide by 0 error.

=iif(Fields!Calculated_Inv_Margin.Value = 0, 0,

sum(Fields!Calculated_Inv_Margin.Value)/sum(Fields!Calculated_Revenue.Value))

It does the percent calc great - but anything with a 0 gets an #Error. So I took out the divide calc and just had the revenue field show - that works. Why doesn't the the calc above just give me a 0?

Any help would be great! Thank you!

I'm new to SSRS - using - Report Builder 3 with Epicor. I have a formula that should prevent a divide by 0 error.

=iif(Fields!Calculated_Inv_Margin.Value = 0, 0,

sum(Fields!Calculated_Inv_Margin.Value)/sum(Fields!Calculated_Revenue.Value))

It does the percent calc great - but anything with a 0 gets an #Error. So I took out the divide calc and just had the revenue field show - that works. Why doesn't the the calc above just give me a 0?

Any help would be great! Thank you!

(value / "divide by") has "divide by" zero error (

(numerator / denominator) has divide by zero error

You need to

=iif(

sum(Fields!Calculated_Inv_Margin.Value)/

note: you also need to test the summed value, just like you have used in the calculation

*or more formally*)(numerator / denominator) has divide by zero error

You need to

__test if the denominator is zero__(your existing formula tests the numerator)=iif(

**sum(****Fields!Calculated_Revenue.Value)**= 0, 0,sum(Fields!Calculated_Inv_Margin.Value)/

**sum(Fields!Calculated_Revenue.Value)**)note: you also need to test the summed value, just like you have used in the calculation

ASKER

So I tried the following:

=iif(sum(Fields!Calculated_Revenue.Value) <> 0, sum(Fields!Calculated_Inv_Margin.Value)/sum(Fields!Calculated_Revenue.Value),0)

This Produces the same result:

I tested the value by using the following:

=iif (Sum(Fields!Calculated_Revenue.Value) = 0, 0, 1)

This returned the result I expected:

I don't understand it - it seems like it is re-evaluating the formula after "then" in the if statement.

I also tried this:

=iif (Sum(Fields!Calculated_Inv_Margin.Value) = 0, 0,

sum(Fields!Calculated_Inv_Margin.Value)/sum(Fields!Calculated_Revenue.Value))

I'm going to try and store a formula with the 0 result to see if I can make it work that way.

Can you do a case statement in SSRS formula editor?

Thanks!

=iif(sum(Fields!Calculated_Revenue.Value) <> 0, sum(Fields!Calculated_Inv_Margin.Value)/sum(Fields!Calculated_Revenue.Value),0)

This Produces the same result:

I tested the value by using the following:

=iif (Sum(Fields!Calculated_Revenue.Value) = 0, 0, 1)

This returned the result I expected:

I don't understand it - it seems like it is re-evaluating the formula after "then" in the if statement.

I also tried this:

=iif (Sum(Fields!Calculated_Inv_Margin.Value) = 0, 0,

sum(Fields!Calculated_Inv_Margin.Value)/sum(Fields!Calculated_Revenue.Value))

I'm going to try and store a formula with the 0 result to see if I can make it work that way.

Can you do a case statement in SSRS formula editor?

Thanks!

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
-- Test data

DECLARE @t TABLE (val1 Int, val2 Int);

INSERT INTO @t VALUES (10,0),(20,0);

SELECT * FROM @t;

SELECT Sum(val1)/Sum(val2) FROM @t;

Msg 8134, Level 16, State 1, Line 6

Divide by zero error encountered.

;WITH cte (p1, p2) AS (

SELECT Sum(val1) AS p1, Sum(val2) AS p2 FROM @t

)

SELECT CASE WHEN p2=0 THEN 0.0 ELSE p1/p2 END AS pp FROM cte