Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Be original, if it means being a little eccentric, so be it
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

11
August

How Do I Solve the Divide by Zero Issue in SQL Server Reporting Services?

A client of mine emailed a report, designed in SQL Server Reporting Services 2005, that when compiled produced a "Divide by Zero" error message. 

When looking at the report,  the formula generating the error message was in the format:

=IIF ({Tablename.Fieldname2} = 0 , 0, {Tablename.Fieldname1}/{Tablename.Fieldname2})

The IIF function has the same arguments as the Microsoft IF function, and is used in the same way to test the value of the denomination.

Unfortunately in SQL Services Reporting Services, the IIF function evaluates all the arguments before it executes, therefore, the formula above may still generate a divide by error message


To get around this issue, the above formula has to be modified to:

=IIF ( {Tablename.Fieldname2} = 0 ,0,
{Tablename.Fieldname1}/ IIF( {Tablename.Fieldname2} = 0 , 1, {Tablename.Fieldname2}))

MAKE A COMMENT

Name *

Email Address *

Comment *