I am working a project for a water treatment plant. I have a need to generate a report that populates analog data every 15 minutes for their permit requirements. Has anyone done this or could someone provide a guide on how to create this report?
you should probably use a transaction group or a gateway timer script to push the analog data that you want into a table. from there it will be easy to generate the report. maybe some more details on your requirements will give us more of an idea to work with.
Our normal reporting software package is XLReporter. I can link, via an OPC driver, to an analog tag in the controller and set the spreadsheet row to update every 15 minutes with the data. I figure there is a way in SQL to accomplish the same type of thing, but I am admittedly NOT a SQL guru. It wouldn’t have to be real time data. This report could be generated daily from historical data points and still accomplish the same goal. The spreadsheet looks something like:
TimeStamp Turbidity1 Turbidity2 Turbidity3
00:15 1.0035 10.265 5.285
00:30 1.1275 11.175 6.250
And so on. At the bottom of each row, would be a cell with min max and average.
I may be able to link to a current report if that would help.
yeah so you could attack this quite a few different ways.
I would probably create a new table and store the data there. create a column for each value(unless you have some ridiculous amount of values that you want to store, then you may look at doing things differently).
Then what i would probably do is create a minute tag, that uses an expression that pulls only the minute out of the current system time. from there I would put that minute tag into a gateway tag change script.
my script would look something like this
minute = system.tag.getTagValue("minute")
if minute in [15,30,45,0]:
print 'start of writing to turbidityreport table'
datetime = system.tag.getTagValue("currenttime")
turbidity1 = system.tag.getTagValue("turbidity1")
turbidity2 = system.tag.getTagValue("turbidity2")
turbidity3 = system.tag.getTagValue("turbidity3")
system.db.runPrepUpdate("INSERT INTO turbidityreport (datetime, turbidity1, turbidity2, turbidity3) VALUES (?,?,?,?)", [datetime, turbidity1, turbidity2, turbidity3])
print 'end of writing to turbidityreport table'
print 'not yet'
As far as displaying the data, I am assuming you want to stick this in the report module? I believe the report module has built in functions that will sum/avg the columns of data for you.
If you are using SQLtag History to collect this data, you can use the SQLtag history binding to achieve your goals. When using this make sure that the time is starting on the hour and the Sample Size is set to “interval” at 15 min.
If your data is not SQLtags history, then you can use a WHERE clause like this:
WHERE EXTRACT(MINUTE FROM t_stamp) IN (0, 15, 30, 45)
This extract function only works in MySQL, but all databases have similar function types. Also if there are multiple records for each minute then you will need to filter them somehow.
Hope this helps