Weekly report generating

[gouch]

Hello,

I have to log flowmeter values, display weekly totaliser values on a wonderware scada node and generate a weekly report.
I am using both a plc5 and a controllogix to count up the totaliser values.
I have access to a rssql database to log the values to.

What is the best approach to take with regard to triggering the saving of data, the frequency of saving it and the generating the reports.

I am worried that if I trigger the save once a week, the PLC may not reliably be able to count every seven days accuratly over time. It would be good if all devices were synced with the same date/time setting but not sure how practical that is.

The PLC can trigger a save every day easily using its internal date time values. Maybe I should log data daily, use the report to pull together the weekly values.

The reports are generated in visual studio.

Any guidance on the best approach would be appreciated.

 

[Saffa]

Maybe I should log data daily, use the report to pull together the weekly values.

I would go a step further, and log every hour if you're writing to a SQL database. if you've got hourly values, it's pretty easy to write a SQL query to return the 24 hourly totals and then add them up. Same for weekly, monthly etc.

Another option that works for me:

One of our client's has a couple of base stations, each with a local PLC and a number of remote telemetry units - 15 to 30 of them. Since we get data from two different sources (PLC and RTU software) via good ol' DDE still, and not all the RTUs get the midnight total reset command at the same time, we simply use Intouch's HistData to export a CSV file of all the flow total tags for a period starting at 11:45pm and ending at 00:15am the next morning, i.e. 30 minutes. Our reporting package (also a custom VB application) then parses these CSV files and simply takes the largest value which is the daily total before it was reset.

It works pretty well for us for the water reticulation we're monitoring (provided the RTU isn't in comms fail, but thats another problem!) but your mileage may vary.

 

[gouch]

When you say write every hour, would you zero the values every hour or just keep writing the live total every hour and just zero at the end of the day based on the PLC internal time.

If I write the total value every hour and reset at the end of the day, use sql to query the table for the date and get the highest value for the day like you said +-15 mins, I was thinking +- 1 hour for daylight savings that are not updated in the PLC.

Should the date time column in the sql table be written to by device and then use this time when compiling the report information. That way the data wiil be correct with the PLC's internal time even if its actually out with the PC running the reporting software?

 

[Saffa]

Sorry, I think my post may have been a little confusing with two methods!

If you're going to write every hour, then yes I'd zero the totalizer each time and store the PLC's time.

This method would not look at the maximum value, but simply (via a SQL query) produce 24 values for the day which you'd need to add up.

You could probably do what you said too and that'd also likely work.

I would maybe have a play with both, shouldn't be too hard to implement both systems as a test and see what happens.

Make sure you have a seperate totalizer that is only reset at Midnight that Intouch looks at, otherwise your trend will be very difficult to interpret. (assume you're also going to be logging in Intouch for easy trend viewing etc)

I would then make sure that you've got some mechanism for updating the PLC's clock via the HMI or (not sure if you could do this with the PLC5) via a network time server.

 

[richardwal]

In this situation we have set up our totalizers so that they act like an odometer in a car. They continually count up to some pre-determined number and then reset to zero. When querying the data via SQL we then grab the starting and ending numbers and calculate the difference. If our starting number is greater than our ending number we then add our pre-determined roll-over to the ending number and then perform the calculation. Depending on how often the value is stored in the database, this will allow to query just about any range...hourly, per-shift, daily, etc....

Our rollover value tends to allow for many days of production before rolling over. With this the PLC clock becomes irrelevant. The SQL server clock and resulting time stamps are used to drive our reporting. We had too many instances where a slow/fast plc clock or incorrect adjustment of PLC time caused reporting issues.

 

[Saffa]

That sounds like a good solution too.

I think there will be a bunch of ways to do this, I'd personally just try one and see how it works with your particular circumstances.

 

[BillS]

Hi,

If you log data in SQL database, XLReporter can connect to SQL database using a native driver, poll both raw data and calculated data (average, minimum, maximum) into your report automatically (scheduled) and interactively.

Evaluation version is available from http://www.TheReportCompany.com/product_download.htm.

You design reports right in Excel, so can use any formulas and formatting you already know. Can output to Excel, html, email, pdf. Can view your reports on mobile as well. It is quite reasonably priced. Hope you check it out.