For as long as The City (as I'll call it) has supplied water to its residents, it has had one big headache called "The Annual Water Survey." Like residents of all large metropolises, The City's residents want to make sure the water they drink has only a miniscule amount of the "bad stuff," such as heavy metals and pathogens, and just the right amount of the "good stuff" -- chlorine, fluoride, etc. The water survey -- a 100-plus-page report that details test after test after test -- was their vote of confidence.

Compiling the survey had always been a long and tedious process. At first, field technicians would take samples from across The City, add drops of various indicator chemicals and record the results in their logbooks. From there, lab technicians would transcribe the numbers and use special slide rules to create tables of meaningful results. Typists would then compile the various tables into a giant binder and send it off for duplication.

Technology helped speed things up a little. Electronic testers replaced drip kits, spreadsheets replaced slide rules and word processors replaced typewriters. But for the most part, the process remained the same. All of that changed, however, when The City -- with its grand plan to automate the water survey -- wanted to make everyone's lives easier.

After 18 long months of development, the highly paid consultants hired by The City presented their solution. Field technicians would be equipped with ruggedized, Internet-enabled PDAs that would instantly upload test results to the .NET-based central server. From there, lab technicians would use an ASP.NET-based app to verify and analyze the data. Reports -- including the monstrous, 100-plus-page water survey -- could easily be generated from the lab data. It was a big investment, but The City knew it would pay off.

Not Quite Field-Tested

Shortly after going live, field technicians across The City noticed a bit of an issue. Because water testing often occurred in low-coverage areas, the PDAs would routinely toggle between offline and online modes. This resulted in a several-seconds-long synch process, which would often fail and require a reboot when the PDA lost its connection. The technicians just resorted to doing the tests, writing the results in a notepad and entering them on the PDA later.

Either way, the field technicians weren't too thrilled about the PDAs. Their old process of entering data into Excel spreadsheets and e-mailing the spreadsheets in was much faster than struggling with a little stylus to tap out numbers. To make the techs "lives easier, the consultants built several Excel spreadsheets -- each with a heavy dose of VBA -- that would serialize data into XML and send it through a Web service to the central server.

Not Lab-Tested, Either

As the lab technicians began their analyses of the new data they, too, noticed a bit of an issue. Although the Web-based app mimicked a cell-based spreadsheet, it lacked the robustness of Excel that the techs had grown accustomed too. There was no auto-fill, quick-sum, conditional formatting or any of the other must-haves.

The consultants quickly responded by building Excel export functions, but that proved to be deficient. The techs couldn't interlink spreadsheets or easily keep their local, heavily modified spreadsheets in synch with data on the server. The consultants finally settled on a solution that the lab technicians could use: They built several Excel spreadsheets, each with a heavy dose of VBA, that would de-serialize data from the central server, display it and then upload any changes through the same mechanism.

As for the Reports ...

Once the lab technicians compiled enough real data to create a survey, the writers were ready to begin generating reports. However, that system also proved to be a bit cumbersome. Although the Web-based reporting module provided them with significant control over how the PDFs would look, it lacked some of the finer details of their former tool, Microsoft Word.

The biggest annoyance was that they were no longer able to instantly preview layout changes, such as changing font size to make a table fit on a page. Try as they might, the consultants simply could not figure out a way to allow them to easily tweak the layout. Instead, they presented a solution that the writers could use: They built several Word documents -- each with a heavy dose of VBA -- that would automatically download and de-serialize data from the central server, and then serialize and upload the saved report.

The Final System

After three years of tweaking and hundreds of thousands of dollars in consulting fees, The City ended up with a system that everyone could live with. Field technicians used Excel to enter data, lab technicians used Excel to process data and writers used Word to create reports. Granted, it was exactly what they had before, but at least now they had a .NET server to bridge it all together.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!