Archive for the 'Reporting Services' Category

09
Oct

Divide by Zero Error in BIDS

I have a budget report in BIDS 2005 where I show three dollar amounts: Required Amount, Funded Baseline, and Shortfall. Then there’s a percentage showing Shortfall/Required. It’s pretty standard stuff. The problem is that I have one budget item that requested no money: their Required Amount is zero. Their shortfall is zero, and so their shortfall % should be zero as well. Here’s what I have:

That formula produces this:

The relevant line is at the bottom, where I get a divide by zero error. This threw me for a loop, so I changed the expression in Shortfall % to this:

This produced the expected results:

Solution:

James Hunter came in with the solution to this problem. Required_Amt was still being evaluated although not used. So he suggested embedding another if statement like so: