I’d like to stick that sign on Microsoft SQL Server Reporting Services.
I built a table to show each Internal Sales Team Manager what their units Revenue and Profit was, year to date, by month, or customer, or vendor. The last column of the table was a percentage profit margin, aka “Profit” divided by “Revenue”.
The gotcha with this is that if something is given away for free, profit over revenue throws a divide by zero error. So simple me thinks:
=iif(revenue>0, profit/revenue, 0)
Which, roughly translated, tells the software to calculate the percentage profit if revenue is greater than zero, otherwise just stick zero in as the answer. So, I rerun the view, and get #error in all the same places and the same 13 examples of attempted divide by zeroes in as before.
Brain thinks - oh, there must be some minuscule revenue numbers in the thousandths of pence in there, so change the formula to:
so that the denominator is at least one, so the chance of throwing a divide by zero error is extremely remote. The giveaway would need to be mind bogglingly huge to get anything close to a divide by zero exception. Re-run the view. Result:
Same 13 divide by zero #error exceptions.
WTF? Look at where the exceptions are being thrown, and the revenue is zero, so the division shouldn’t even be being attempted. So off to Google with “SQL Services Reporting iif divide by zero” I go.
The answer came from a Microsoft engineer who admits, nominally for performance reasons, both paths of the iif get executed at the same time as a performance shortcut, so that whichever half needs to give it’s result, it’s already populated and ready to use. So, the literal effect of
works like this:
Calculate 0 on the one side.
Calculate Profit/Revenue on the other.
If Revenue > 0, pick the second option, else pick the first.
If either side throws an exception (like divide by zero), blat the answer, substitute “#Error” instead.
Solution is to construct two nested iif statements in such a way that the optimiser does’t execute the division before the comparison with zero is made. With that, I’m sure wearing underpants on your head has the same sort of perverse logic somewhere. This is simply atrociously bad software engineering.