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.

 

 

 

If you have a SCADA System that was built by Navionics Research, then your system is already compatible and ready to be upgraded with this plugin.

If you are an owner/end-user of a SCADA System that was built by another vendor, then it is possible that our plugin can be adapted to your system.

If you are a SCADA integrator in search of a custom Excel plugin that binds to the historical database of your specific system, we are glad to offer our efficient software consulting services.

In all cases, we are glad to help… Give us a call or visit our contact page.

Telemetry, SCADA, & Controls Newsletter

Was this article helpful?  Would you be interested in receiving updates such as these in our occasional email-delivered newsletter?  If so, here’s our sign-up page:

Subscribe to Navionics Research’s “Telemetry, SCADA, & Controls Newsletter”