Software 101 – Validate your reports.

As my Mama says, ” If you don’t have time to do it right the first time, when will you have time to do it over?!”

Earlier this week, I worked with a colleague. I followed his instructions.  He asked for data pulled from SQL tables, into a spreadsheet for him to analyze.  Simple, export and done.

We had already revised much and added lots of columns to provide more details, when he asked that I give him the “cost per mile” to plug into his projections.  When I did, he questioned the results, as it showed losses and questioned the rate per mile since it was the last change made.  He also had me add information to his exported spreadsheet file and questioned my sql script, as it returned a different result than he expected (according to his spreadsheet formulas).  After a while of fending off accusations of being incorrect, I got the feeling he was trying to compare apples to oranges, so I asked a few questions of my own. Firstly, “What is your control data? How many loads should it report and what is the total revenue?”   Those questions were followed by silence.

It reminded me of another instance when a client hired an SQl “expert” to create a very complicated profit per truck report with drill downs and subsidiary reports.  I must say that report came out beautiful, although totally useless because the data it returns is incorrect.  The revenue is good in the sub-report, but not the main report.  Driver pay shows all transactions included (not just those applicable to driver expenses).  So after deducting the advances against a load, it looks like some drivers are paid almost nothing! (Driver advances are not considered company expenses, rather loans, so they end up as “driver pay”). Debugging such a complex report after the fact is almost impossible as he was calling into play global variables written by other authors and received minimal instructions from his employer or these other authors.

Any time you are creating new reports and crunching large amounts of data, you must plan your work first!  Clearly know what you are looking for and  use a small sampling to verify the results.  Also, if you need to make minor changes, confirm the results are valid, then continue to tweak the report and add the rest of the calculations and data.

A good measuring stick is to verify what you can against current reports you trust.

Such as load counts, total revenue, etc.   Also, be sure you are comparing apples to apples.  If the basis of the report for loads is on completion date for example, don’t get it mixed in with bill dates.  Be sure you are looking at actual completion dates and not ETA’s or order dates.  Many systems today record so many details from so many sources that you will need to confirm the data you are working with is applicable to your project.

Another recommendation is to allow for exceptions.  What if you are using odometer readings to calculate miles and the tractor is not reporting?  The GPS locations reflect movement, but the odometer reading never changes so the total miles are zero.  As we learned in elementary school, you cannot divide by zero.  Minor things like this can askew the results big time.



Leave a Reply