A utilization report is essentially a view of hours worked vs hours paid – simple, right? You can determine appropriate staffing levels by understanding how much work they’re performing and how much work needs to be performed.

It turned out “the utilization report” at my company actually meant six distinct reports. Here’s how the system worked:

  • At the beginning of every month, a user would submit an IT ticket as a reminder to “run” the report on the 15th of the next month
  • Somewhere near the 15th of the month, a specific developer would navigate to a directory on his machine and double-click an Access database
  • The developer would then locate each of the six reports in the Access database and run them all in turn, exporting them as RTF files when they finished
  • Each report was extremely resource-intensive and completely took over the IT guy’s computer, rendering him useless for hours – and even longer if the Access database crashed
  • When all reports had been exported, they were attached to an email and sent to the user from Step 1

This process was repeated every month and caused much frustration for the user (because they couldn’t get the report immediately) and the developer (because it consumed his computer and he couldn’t get anything done). After hearing the developer curse and complain about the utilization report a few times, I set to work on the Access database. The process was changed to:

  • On the 15th of every month, Windows Task Scheduler opens the Access database, triggering a VBA subroutine
  • Each report is exported to RTF
  • All exported reports are programmatically attached to an email message and sent to the user

The new process is entirely automated and takes about three minutes to complete. It turns out Access was running all of the report queries (a bunch of UNION ALLs in a number of views) twice – once to display the report to the developer, and again to export the data to RTF. In addition, the formatting process required to display the report inside Access took an enormous amount of time, whereas the formatting of the RTF to be displayed in an external viewer was far quicker.