Thursday, June 9, 2011

Skinning the Cat with XLReporter

At what point does control functionality requirements become just too complex to implement in the native control environment? It’s a tough call, because there are so many factors that go into the decision. Converting some massive calculation into the DCS control environment just might not make sense.

Other considerations though, especially for regulated industries like biotech and nuclear, center on the ability to validate all of the control functionality in a like manner. Having one piece of code, written in a different programming language, and existing on a different platform (typically Microsoft), may not make sense.

A few years ago, I converted some pretty complex, iterative calculations into function block and SFC logic in DeltaV. I spent many months working to optimize the code to run in the controller with the rest of the control strategy.

Earlier this year, we needed to come up with some dew point calculations as part of a project. While they were eventually easy enough to code in native DeltaV format, the source for the equations came from Excel spreadsheets.

So recently two things caught my eye on a potentially better way to “skin the cat”. First, I saw a question pop up on the DeltaV LinkedIn site about Hydro Carbon Dew Point (HCDP) calculations and how to implement them with the DCS. I googled HCDP and found some very complex equations taking place in Excel to solve these calculations.

The second thing was a product update presentation I sat through from Peter Kaprielian of Sytech for their XLReporter package. As you might gather from its name, XLReporter makes heavy use of Excel for data gathering and reporting. XLReporter is jam-packed with features, and while I can’t do justice to them all here, the one that got my attention was the ability to write data, via OPC, to other systems.

That’s when the light bulb went off – could I do HCDP calculations in Excel, feeding GC data from DeltaV to the spreadsheet and getting the results pushed back down to my controller?

Now I kind of wimped out, because I don’t know the first thing about HCDP, but I do know something about dry bulb, wet bulb and humidity calculations due to our recent project work. So I set out to implement a wet bulb temperature calculation implemented in a XLReporter spreadsheet. The idea was to read air temperature and humidity values from DeltaV, process them in my XLReporter spreadsheet, then write the result back into a parameter in my controller.

Additional internet surfing netted me a dew point spreadsheet I “borrowed” for my test. I cut and pasted the spreadsheet into the Template worksheet of an XLReporter Excel workbook. Then I added real time data links to temperature and humidity parameters from a couple of different modules in my controller:



I then tied off cells E29 and E31 with the values of C18 and C17. The final step was to take the result in J34 and write it back to a parameter in one of my modules. When you run a Report Update, the Results worksheet looks like this:



And my test module in DeltaV looked like this:


I set up a schedule within XLReporter so the calculations are processed once a minute. XLReporter is a bargain at twice the price, so if you have a particularly complex or otherwise computationally intensive application, give this technique a try.

If you'd like to know more about how I pulled this off, post a comment or shoot me an email.