T-SQL Tuesday – The Reporting Table


My T-SQL Tuesday post for April is based on Aaron Nelson’s subject of reporting. If you’re unsure of what T-SQL Tuesday is, you can read about it’s origins here.

The Reporting Table

Years ago, actually well over a decade in my past, I was working for a small company and building an order entry/CRM/inventory/etc. system for them. We had upgraded their old DOS/Foxpro system to VFP/SQL Server 6.0, and we we in the process of adding some new enhancements. Most of these were actually more sophisticated reports that we designed to help them handle larger volumes of business needs.

One of the reports I was in charge of building was based on a spreadsheet that one of the supply people had built. It used a look back at orders for the past few months and then the inventory we currently had and give the person a guess about what quantity of the various products to order.

So I ported it, encapsulated the same logic that was in the XLS, and deployed it. A day later I was coming back from lunch when the supply guy, and the owner of the company stopped me to say that the report didn’t work and that I needed to fix it ASAP.

I put my tie back on and got to work, digging in with the supply guy. The first thing he showed me was two copies of the report from that morning. They had different data, but the same parameters (I was very glad I’d printed the parameters on each report). I was puzzled at first, but then noticed that the reports had been run a couple hours apart. Since the report was based on sales up to the current day, and the current inventory levels, it’s entirely possible that if the report was run at two times, the data might be different.

“That’s not right.”

That’s what I heard, and I started to show the guy how I could structure queries as of the time of each report in Query Analyzer and get the data he saw. However he didn’t want to hear it and I realized quickly that I needed another solution.

So I inquired how they used the report in more detail. He told me that they’d run the report and then share the data between a few people to discuss what to order. I told him he could “photo copy” the report (we printed things back then), and pass out copies, but his head started shaking before I got much past “copy machine” and so I stopped.

Back to the drawing board, and with a vacation scheduled, I needed solution quickly. My investigations led me to understand that the reason this was never a problem before was that the supply guy manually got data from other reports and assembled it into a spreadsheet by hand. That took him most of a day, and he’d email the report to everyone that afternoon and the next day as they went through their meeting, they would adjust factors on the XLS to decide how much to order.

I’d built in factors as report parameters, but now this was embedded inthe application and so you’d have 2-3 people playing with parameters on reports with their laptops, and comparing numbers. A static printed report wouldn’t work.

What I came up with was a report table. I built a table that duplicated all the columns of the report. So for a report that had productID, Jan Sales, Feb Sales, Mar Sales, Last Order, Current Inventory, etc., I had this table.

CREATE TABLE OrderingReport (

productID int,

JanSales numeric(10, 4),

FebSales numeric(10, 4),

MarSales numeric(10, 4),

LastOrder numeric( 10, 4),

CurrInv numeric( 10, 4)


I then scheduled a job that would “populate this table” every night around 2am, a time that was unlikely to impact anyone’s work schedule at this company. I reworked the report to query this table, using filters if they limited it by product, and return the same information all day.

I deployed it and the supply group was thrilled. Then could easily run a report, and it would compare to the data that a co-worker had. They could run reports, change factors, re-run them, change back parameters, and still end up with consistent reporting for the day.

Of course, I added one last enhancement to the application before I left. I had to give them a “reload” menu item to reload the entire table is some significant sale occurred, but this satisfied their need for consistent reporting in 1995.