Jim Mimlitz, NRI<\/p><\/div>A\u00a0water utility\u00a0SCADA system contains a wealth of historical data\u00a0— data which is often needed for the completion of monthly EPA reports.\u00a0 Historically, these reports are prepared by hand with pen-and-paper — or by manually typing values into a\u00a0computer form document.\u00a0 However, this\u00a0article 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\u00a0function library.<\/p>\n
First, the custom function library is built in the form of an ‘XLL’ file.\u00a0 An XLL\u00a0is a\u00a0special type of DLL (Dynamic Link Library) that is built to extend the capabilities of Microsoft Excel.\u00a0\u00a0Just as\u00a0Excel offers a multitude of helpful math functions, such as SUM(), AVERAGE(),PRODUCT() — our XLL library\u00a0implements a set of\u00a0 import functions\u00a0that\u00a0provide\u00a0a direct data pipeline between the SCADA historical database and the spreadsheet.\u00a0 The XLL is built ideally using the C programming language and Microsoft’s Visual Studio compiler.\u00a0 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:\u00a0 C:\\Users\\<UserName>\\AppData\\Roaming\\Microsoft\\AddIns\\.\u00a0 Additionally,\u00a0a required Microsoft-provided library file ‘XLCALC32.DLL’ is also copied into the same directory.<\/p>\n
The XLL is activated within Excel through the Menu:\u00a0 FILE>OPTIONS>”ADD-INS”>”MANAGE EXCEL ADD-INS”.\u00a0 Note that activation is only required once.<\/p>\n
To streamline the reporting process, our custom-built XLL was designed with a function library\u00a0capable of fetching time-stamped data points from the historical database of the SCADA system.\u00a0 Examples could include meter readings, chlorine residuals,\u00a0chemical scale readings, etc..\u00a0 Individual cells within the spreadsheet are then\u00a0loaded with function calls to the XLL that import\u00a0the desired data into the desired fields of the EPA report.<\/p>\n
Two screenshots from two separate water utilities are shown below.\u00a0 Note that the cosmetic formatting is somewhat different, reflecting the preferences of both the water utility and the oversight agency.\u00a0 It is also noteworthy that these\u00a0spreadsheets are easily customized by the\u00a0utility as its needs evolve.<\/p>\n
<\/p>\n
![\"Semi-Automated](\"https:\/\/wireless-telemetry.com\/blog\/wp-content\/uploads\/2014\/01\/Screenshot_001.jpg\")
<\/a>Semi-Automated EPA Report Generation Containing Master Metering Data.<\/p><\/div>\n
<\/p>\n
<\/a>Semi-Automated EPA Report Generation Containing Totalization of Water Treatment and Filtration.<\/p><\/div>\n
<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
In all cases, we are glad to help… Give us a call or visit our contact page<\/a>.<\/p>\n Telemetry, SCADA, & Controls Newsletter<\/strong><\/p>\n Was this article helpful?\u00a0 Would you be interested in receiving updates such as these in our occasional email-delivered newsletter?\u00a0 If so, here’s our sign-up page:<\/p>\n Subscribe to\u00a0Navionics Research’s\u00a0“Telemetry, SCADA, & Controls Newsletter”<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" A\u00a0water utility\u00a0SCADA system contains a wealth of historical data\u00a0— data which is often needed for the completion of monthly EPA reports.\u00a0 Historically, these reports are prepared by hand with pen-and-paper — or by manually typing values into a\u00a0computer form document.\u00a0 However, this\u00a0article details a vastly-improved, semi-automated method of report generation in which the SCADA system …<\/span> Read More →<\/span><\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":28,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,7,8,10,14],"tags":[34,60,61,133,143,171],"class_list":["post-17","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-applications","category-general","category-historical-trending","category-interface","category-report-generation","tag-automatic-report-generation","tag-excel","tag-excel-add-in","tag-scada-system","tag-spreadsheet","tag-xll-file"],"_links":{"self":[{"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/17","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=17"}],"version-history":[{"count":0,"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/17\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=\/"}],"wp:attachment":[{"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=17"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=17"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wireless-telemetry.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=17"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}