Eliminate #Error within Report Builder 3.0 when dividing by 0

sql ssrs-2008-r2 reportbuilder
user3847080 picture user3847080 · Jul 17, 2014 · Viewed 7.2k times · Source

When the following formula divides by 0, all three of the below are returning #Error

=(Sum(Fields!Q2ActDelta.Value))/(Sum(Fields!Q1Actuals.Value))

=IIf(Sum(Fields!Q1Actuals.Value)=0,"",(Sum(Fields!Q2ActDelta.Value))/(Sum(Fields!Q1Actuals.Value)))

=IIf((Sum(Fields!Q1Actuals.Value))=0 Or (Sum(Fields!Q2ActDelta.Value))=0," ",(Sum(Fields!Q2ActDelta.Value))/(Sum(Fields!Q1Actuals.Value)))

What do I need to do to eliminate #Error? Why isn't report builder following the true condition?

Thanks!!

Answer

Robby Cornelissen picture Robby Cornelissen · Jul 17, 2014

It's caused by the fact that ReportBuilder still tries to evaluate the false path, even if the condition resolves to true. The command below should solve your problem.

 =IIf(Sum(Fields!Q1Actuals.Value)=0,0,Sum(Fields!Q2ActDelta.Value)/IIf(Sum(Fields!Q1Actuals.Value)=0,1,Sum(Fields!Q1Actuals.Value)))

Related questions

How to implement 'Not In' Filter in SSRS?

I have a field in SSRS that i need to filter on. For 3 table I can use a IN filter. But I am in need to use a NOT IN operator. The field contains numeric values. I need to be …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More