Early in my career, I used to write and debug device drivers, which was a mix of reading code in octal/hex, looking at source listings, pouring over crash dumps and trying to work out which code paths executed in parallel. Each potentially in conflict with each other if you weren’t extra careful. Doing that for a time gets you used to being able to pattern match the strangest of things. Like being able to tell what website someone is looking at from far across the room, or reading papers on a desk upside down, or being able to scroll through a spreadsheet looking for obvious anomalies at pretty impressive speeds.

The other thing it gives you is “no fear” whenever confronted by something new, or on the bleeding edge. You get a confidence that whatever may get thrown at you, that you can work your way around it. That said, I place great value in stuff that’s well designed, and that has sensible defaults. That speeds your work, as you’re not having to go back and spell out in minute detail what every smallest piece of the action needs to do.

I’ve been really blessed with Analytics products like Tableau Desktop Professional, and indeed more recently with Google Spreadsheets and Google Fusion Tables. These are the sort of tools I use routinely when running any business, so that I can simply, in a data-based way, adjudge what is and isn’t working business-wise.

The common characteristic of these tools are that they all guess what you need to show most of the time, and don’t delay you by having to go through every piece of text, every line, every smallest detail with individual calls for font, font size, colo(u)r and the need to cut the graph display of a line once the last data point is rolled out – and not, as one product does, just throw all future months stuck on a flat line once the plot goes into future months with no data yet present.

There have been several times when i’ve wanted to stick that “Does Stupid Things” sign on Microsoft SQL Server Reporting Services.

I diligently prototyped (as part of a Business improvement project) a load of daily updated graphs/reports for a team of managers using Tableau Desktop Professional. However, I was told that the company had elected to standardise on a Microsoft Reporting product, sitting above a SQL Services based Datamart. In the absence of the company wanting to invest in Tableau Server, I was asked to repurpose the Tableau work into Microsoft SQL Services Reporting Services (aka “SSRS”). So I duly read two books, had a very patient and familiar programmer show the basics and to set me up with Visual Studio, get the appropriate Active Directory Access Permissions, and away I went. I delivered everything before I found no line Management role to go back to, but spent some inordinate time between the two dealing with a few “nuances”.

Consider this. I built a table to show each 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, (nominally negative) profit over revenue throws a divide by zero error. So simple to code around, methinks:

=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:

=iif(revenue>1,profit/revenue, 0)

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 statement 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:

=iif(revenue>0, profit/revenue,0)

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.