CASE STUDIES
data transformation

Area of assistance: Data and Report Automation Solution

Technology involved: Microsoft Access 2000, Excel and Visual Basic

Problem: The client needed to reduce the time and effort spent by a full-time accountant developing utility usage reports. The sales staff required these reports to follow up with their large multi-site clients. Whenever a set of reports were needed, the accountant spent up to eight hours producing the reports, and the sales representative had to wait anywhere between 2 to 5 days to receive them.

Analysis: The source, in Excel, originally came from a Mainframe application. The flattened record format that contained client information and power usage was difficult and time-consuming to work with and had to be manually created in order to get the specific format needed to produce the report details, charts, and graphs. An accountant was required to move the data into several Excel spreadsheets via a manual cut and paste process. Because the process was manual and spread out over several spreadsheets, thirty percent (30%) of the process included the accountant verifying the data. In turn, the reports and graphs being generated had to be generated one at a time. There was also a need to create an archiving process for future reference in the event of a modification of the Excel sources.

Solution: The client, having Microsoft Access 2000 and other Microsoft Office products available on the desktop, was a perfect candidate for full-blown office automation utilizing Visual Basic and existing Microsoft COM technology.

Goal: Reduce the time and effort required to generate the reports and eliminate the need for the accountant to produce the reports.

Strategy: The data from the original source, IBM mainframe, was extracted, formatted and placed into user tables within a separate Access database according to date. Because of space concerns and archiving needs the database was placed in a ZIP file. The client could then, on demand, unzip a single database and automatically generate the reports and charts according to the client and store ID. The colors, fonts, layout, headers and footers of the reports and charts are dynamically created using a cascading style sheet and Visual Basic for the necessary cosmetic touch.

Conclusion: The solution provides a reduction of anywhere from 5 to 7.5 hours of labor and a process anyone can run. This new process now only requires five to fifteen minutes of administrator time to generate a complete client report and chart, which, in turn, can be e-mailed, faxed, and printed for sales representatives or customers upon request.

If you would like to know more about this solution or the client for whom we provided it, please contact us.