Desktop Compliance...Unleashed

• By Steve Irvin
• Apr 01, 2003

It's reporting time again. You find yourself spending countless hours gathering and sifting through data from all parts of the facility. This time, as you fill out those forms and reports, you swear that you are going to fix up those spreadsheets, buy some software or hire a consultant to take care of it all. So, you put yourself up to the task of improving your reporting methods. First, you decide that your spreadsheets are too antiquated, so you look at the vast array of software packages. Then, you find that software packages do some of the things that you need, but not all, or they carry a large price tag. Finally, because of budget cuts, the software option might be gone, along with the consultant option. You become frustrated, make a few changes to your antiquated spreadsheets, and push it off until next time.

Sound familiar? Or did you already spend a bundle on off-the-shelf software that doesn't work well with your processes? There is a better way and chances are that it is sitting right on your desktop.

It's time to discover the rest of your office suite package. Most users are pretty familiar with word processing and spreadsheets. However, the average user does not come close to recognizing the functionality of how these programs can work together as a whole or in conjunction with a database. These applications contain a treasury of tools that can dramatically reduce reporting costs and work effort.

By combining the functionality of the suite applications and by using their vast features, you can actually automate the reporting you do on a regular basis with relatively little time or money versus a purchased off-the-shelf software program. Not to discount off-the-shelf software, since there are several good programs available, but I want to provide some reasons for using the tools you currently have.

In many cases, these off-the-shelf reporting software programs are much more robust than is needed to do the job but the consumer gets caught up in the buying for the sake of buying, the result is often like using a sledgehammer to drive a needle. These programs are often filled with many expensive features that are just not needed. A lot of the off-the-shelf software is Web-based with its own servers and cannot be customized to your needs. Additionally, you are left to populate the programs yourself...manually.

Web-based programs with specially dedicated data servers are clearly more geared for applications having scores of simultaneous users and huge amounts of data. The other purpose of a Web-application is to allow a central office to control and serve up large amounts of information to multiple facilities in a common interface. This is nice, but is it necessary? Not really, at least not for most environmental requirements. The cost of many Web-based environmental applications is well into the six figures for the primary purpose of having the common interface. Much of this same functionality can be achieved through using the existing office suite tools and placing them on your Intranet or network. Use of read-only files placed on the Intranet or network can be used in the same manner as a Web-based application at very little cost. These files can be inexpensively programmed to exchange data with a centralized database in much the same way as the Web-based application. Critics of this method would say that the office suite databases are not as robust as the larger databases used in Web-applications. This is true, but it would take huge amounts of data over several years to overwhelm one of these systems. That amount of data is not generally part of your average environmental usage. Furthermore, it is wise to archive and backup data on a regular basis to minimize any potential losses. This is true for any system.

Purchased programs are also pretty strict as to their format. Data entry screens, process flow and reports usually cannot be changed. The purchaser usually find themselves changing the entire way they do business just to use the software. Additionally, extraneous fields, some required and some not, are scattered all over the forms causing confusion amongst users and inconsistency in data.

Data population, alone, is a monumental task in a new system. Most companies go through the process of weeding out software companies and installing the applications without a thought of how the information (lists and lookup tables) will be populated. Management all too often shrugs it off as something that can be done in your spare time. The fact is, it may take a team of people several months to identify and compile data and then populate the new program. This alone can adds tens of thousand of dollars to the price tag.

We've looked at the various costs of off-the-shelf software. Now let's look at the efficient alternative, using the tools you currently own, the standard office suite. These suites come with three main applications: a word processor, a spreadsheet program and a database program. Additionally, an easy-to-use built-in computer language is part of the suite. This provides extra functionality and automation.

These standard office suites were built to share information easily between applications. Information entered into spreadsheets and word processing programs is easily transferred to databases for storage and reporting. The reverse is true as well. Users can pull information from databases using spreadsheet or word processing applications for creating forms and reports. This is very efficient since you can share data between applications and other departments (if networked) and no double entry of data is necessary. I have seen these tools created for DMR generation, emissions inventories, waste tracking, regulatory compliance, incident reporting and more. These tools are ideally suited for this type of information gathering, storing and reporting and can cost only a fraction of off-the-shelf packages.

By using the office suite programs you are able to gather information from around the facility using special built-in database communications and linking, place it into your program, arrange or calculate results and then generate the reports that you need. Best of all, you can automate this process by using the recording features that are built into the system. Depending on your computer knowledge and available time, you can also perform additional programming to automatically perform most every task you now do manually.

Let's work through a real-life example, water discharge reporting (DMRs). Many facilities have a vast array of data that must be compiled and arranged to generate these recurrent reports. The word recurrent is key here. It should be no surprise that these reports are due on a regular basis. Yet, you repeatedly find yourself frantically tracking down data at the last moment from all over the facility. There's an easier way to do this. Track down and document the origin of the data to the point at which it becomes electronic data. Find out how to pull that data directly from the source for your own use so you?re not getting a third hand spreadsheet or report. If there is a lot of manually entered data that is given to you in a printout, find out where it came from. If it's a printout, it had to be electronic at some point. Track it down and pull as much of that data electronically to reduce manual entry. Also, if your lab is still giving you paper printouts, call them and ask for the data to be provided in an electronic format. For the remaining manual entries, create an electronically linked form for operators to directly submit into your database or spreadsheet. Once you have the sources of data located, document everything. Documentation is key to a successful system.

From the DMR example, the importance of finding the original source of electronic data was explained. This also will help in automation. If you plan on automating the reporting system, the data source must be reliable. Therefore after the data sources are confirmed and documented, automation can begin. Automation can take place by using basic spreadsheet cell links, hyperlinks, recorded macros or basic computer coding. Linking applications is the easiest method and is pretty commonly known. Computer coding is more difficult but can be done using built-in recording tools called macros. A macro allows a user to record the actions that he/she performs. This can include formatting sheets, linking spreadsheets, etc. The user simply hits a record button and begins doing the tasks that they would normally do to perform the periodic reporting. The computer records these actions and stores them in a macro. The user can then perform these actions automatically in the future by calling on the macro that was recorded.

Many options and resources are available for help in implementing this type of office suite solution. There are several books, courses and CDs available that teach how to harness the power of these office applications. (Stay away from books for dummies. No self-respecting person should ever buy something for himself/herself that is meant for a dummy.) Also, many environmental consultants and professional developers routinely perform these services.

If you're going to build the tool in-house, much of the work can be done with macros or simple coding. Obviously it will take some time to create your reporting tool, just remember to take your time and document everything. Plan on working intermittently over a few months, and use this time as kind of a productive escape from the daily grind. Always write down what you have done and keep a few reference books and reference Web-sites handy.

As mentioned, another option is to have the tool developed professionally. I would recommend utilizing a company that has both programming experience and an environmental background. With the environmental background, it is easier to communicate your needs and less expensive to achieve the functionality you wish to achieve. Also, bells and whistles are nice, but they do add cost to the final product. Get your reporting software functional first, then add the fancy stuff with the money you saved from implementing a good low-cost solution.

As far as the cost of a professionally developed solution, some companies will give lump prices while others will work on an hourly basis. Estimate how long it would take you to develop this tool. Then assign a cost per hour to that number and evaluate whether it is worth your time to do it in-house or to have someone else do it for you. Also, evaluate any approaching deadlines. It may be worth having someone else do it if you know they will finish in time for you to use it in the next reporting period, thus saving you that much more time and increasing your return on investment. If you're still wondering about the worth of using a professional developer, choose a smaller task that you perform on a regular basis. Get them to perform some automation on that spreadsheet so you can evaluate the development cost versus your recurring time commitment. This way, you've explored this option without much out of pocket expense and you've got a great new tool to use in your arsenal.

This article originally appeared in the 04/01/2003 issue of Environmental Protection.