Dashboard Reporting

Earlier this year, upon entering a meeting room, I overheard a local, Kansas City-based C-level executive say something along the lines of, “I don’t give a damn how the data goes in as long as it comes out the other end making sense so we can make some decisions!”. I was walking into the meeting room after this meeting had just ended to join another meeting with that same executive to discuss software project improvement topics. The software development team he had been meeting with left the meeting noticeably concerned with the executive’s comments.

As our meeting began, his mood carried over into our discussion as he expressed his dissatisfaction with the team focusing on (as he saw it) unnecessary UI and architecture changes and how those were going to delay the reporting requirements for the current release—which from his perspective was the entire business reason for the project. I asked a few questions and my meeting topic immediately changed to discussing possible ways to make the management reports the priority once again. We left the meeting room together and went to visit the technical lead and the project manager. Since this post is more about a dashboard reporting solution, I won’t discuss the details of that discussion but the end result was defining a way to get the reporting needs met in time for the first release date. The solution? Microsoft Office.

Okay, okay, I know there are a variety of outstanding reporting tools out there (I’ve used a lot of them) and that Microsoft isn’t the only game in town when it comes to office applications but this is what I had to work with and hey, it works. I also got to be hands on with delivering a solution which is something I enjoy as much as process improvement work. For the next few days I took the customer’s pain and turned it into a major sigh of relief with a custom Excel Dashboard solution. The basic approach was for employees around the country to periodically download the latest data file along with the Excel workbook in a compressed package and run it on their laptops disconnected. One of the worksheets in the workbook contained different criteria the user could set. The next worksheet contained an actual ‘dashboard’ containing a number of charts and metrics that management wanted to track and communicate, the third worksheet contained a raw pivot table that for those inclined to use it, would allow them to do further analysis and the final worksheet contained information about the data and a few tips for using the application. The workbook itself was linked to the data file that accompanied it in the compressed, downloaded file so all queries were local against this slice of corporate data. It was fast, easy to use, informative and allowed the overall project to complete on time and hit its release goals.

It wasn’t quite as elegant as the development team had envisioned for a reporting solution but user feedback was such that it suited their purposes. There were confidentiality concerns over the information being downloaded and distributed in this manner but before this reports were being emailed as PDFs containing the same information and any reporting tool was going to need to allow at least sufficient access to the same information. There was the cost concern over a “throw-away” solution such as this but the reality was the cost for developing it was easily less than the costs associated with delaying the project to build the “perfect” reporting solution. So even if it was disposed of (and my bet is it won’t be—at least not for a long while), the organization would still be money ahead. At any rate, it made the team stop and think about ways to get features into production sooner without seeing disposable work as a wasted investment in all cases so I guess there was a bit of process improvement thrown in at no extra charge. I’ll plan to write a few more posts about Microsoft Office automation from time to time. There’s plenty of great content on the web in this area but hopefully I’ll share a tip or two that will be useful to someone out there.