Automatic Report Generation Using Excel and Your SCADA System

A water utility SCADA system contains a wealth of historical data — data which is often needed for the completion of monthly EPA reports. Historically, these reports are prepared by hand with pen-and-paper — or by manually typing values into a computer form document. However, this article details a vastly-improved, semi-automated method of report generation in which the SCADA system exports data into an Excel-based EPA report using a custom function library.

First, the custom function library is built in the form of an ‘XLL’ file. An XLL is a special type of DLL (Dynamic Link Library) that is built to extend the capabilities of Microsoft Excel. Just as Excel offers a multitude of helpful math functions, such as SUM(), AVERAGE(),PRODUCT() — our XLL library implements a set of import functions that provide a direct data pipeline between the SCADA historical database and the spreadsheet. The XLL is built ideally using the C programming language and Microsoft’s Visual Studio compiler. After the source code is written and compiled, the resultant XLL file is copied into the Add-In directory of the SCADA computer, located at: C:\Users\<UserName>\AppData\Roaming\Microsoft\AddIns\. Additionally, a required Microsoft-provided library file ‘XLCALC32.DLL’ is also copied into the same directory.

The XLL is activated within Excel through the Menu: FILE>OPTIONS>”ADD-INS”>”MANAGE EXCEL ADD-INS”. Note that activation is only required once.

To streamline the reporting process, our custom-built XLL was designed with a function library capable of fetching time-stamped data points from the historical database of the SCADA system. Examples could include meter readings, chlorine residuals, chemical scale readings, etc.. Individual cells within the spreadsheet are then loaded with function calls to the XLL that import the desired data into the desired fields of the EPA report.

Two screenshots from two separate water utilities are shown below. Note that the cosmetic formatting is somewhat different, reflecting the preferences of both the water utility and the oversight agency. It is also noteworthy that these spreadsheets are easily customized by the utility as its needs evolve.