Blue pill or Red pill - Reporting, Historizing, and Summarizing Data

I feel like I am going down the wrong rabbit hole because I seem to be going to great lengths to generate daily and monthly reports when it comes to historizing totalized values. There are 2 general ways of generating daily totals. Let the PLC add it up for you or let Ignition do it. But I see limitations in both.
Take for example a flow meter. This device provides instantaneous flow throughout the day in gallons/minute (generally a jagged graph that increases and decreases throughout the day). The PLC can calculate the total flow by getting the current value once a second, divide by 60, and add to an accumulator. Graphing this value results in a ramping (always increasing) function that resets at midnight (using the PLC’s clock…more on this in a bit). Then, just before you reset the value, you can also copy that last value into another variable to represent the total flow for “yesterday”. graphing the “yesterday” value provides flat, constant lines that change value only once per day.

So now comes the fun part. If you want to create a monthly report to show daily totals (1 row per day), you could use the “.queryTagHistory” scripting function and return the “Maximum” aggregate value of the ramping totalizing tag and specify the intervalHours parameter to 24 for the date range. Or you could use the “LastValue” aggregation mode of the “yesterday” tag. But you’ll be 1 day behind and have to adjust your start/end dates accordingly. Or you could use “.queryTagCalculation” and return the “Sum” and “Count” of the flow tag and perform your own integration of the data to get the total per day…hoping that there were no hiccups in the scan class polling and saving to the database for exactly the 86400 samples needed in the day (or 1440 if you plan on only sampling once a minute)…otherwise you utilize the “Count” value and get a best estimate (Riemann sum comes to mind)

Even so, if the PLC clock is not exactly synchronized with Ignition, returning the “Maximum” or “LastValue” will eventually fail you because you may not capture the true maximum flow if the PLC resets after Ignition’s midnight. And your next day value might be incorrect if your flow is less than the previous day’s true maximum.
The other issues that arise are not being able to get a month-to-date total without adding both the summation of all “yesterday” values plus today’s current totalizer value.

So, my question for you all is. How do you do it? How do you create daily, monthly, month-to-date or even year-to-date info from the historian? Do you create new tags for the sole purpose of historizing totals? Do you let Ignition do the totalizing instead of the PLC?

Extra Credit: What about non-totalizing processes like maximum Turbidity (NTU) or daily average transmembrane pressure (TMP) of a filter but ONLY when there is flow through the membrane or a valve is open? I’m having to query the tag historian for both tags in small intervals (5-min chunks) and check if the valve or flow is on, then perform average of only those data points. Alternatively, I am also creating a second tag whose value is “null” if not running so that the historian doesn’t record zeros or tries to average non-valid data.

1 Like

I make the PLC run an odometer-style totalizer in a 32-bit integer. Never resets, just rolls over. For analog processes, I use a float in the PLC to hold the moment-to-moment integral fraction. Any time it reaches or exceeds 1.0, I add the integer part to the odometer and retain just the fraction in the float. No RTC required in the PLC, just the real time interval to scale the integrator for the float accumulator.

Instead of resetting the accumulator daily, just snapshot the odometer at that time. In the PLC if an accurate RTC is available. Write back from Ignition otherwise. Operator display is then the delta from the odometer to the snapshot.

Use a transaction group to record all such totalizers together at your desired minimum analysis interval (one minute, perhaps). With that recording, the total for any time period that is within the rollover limit is simply the delta between the last value before the period and the last value in the period. (Or first value within to first value after–just be consistent.) Outside the rollover limit, compute for each largest standard period within the rollover limit and sum those.


Phil, how do you handle the “roll overs”? I would imagine some “if” statement to check if previous number is larger than new number (or vice versa) then add the remaining portion up to the maximum value of the 32-bit number. Also, is the transaction group scalable to cover hundreds of tags? In other words, we use UDTs so when we have a new flow meter, just add to tag browser and voila! is historized. If using transaction groups, we’d have to remember to update that as well with the new totalizer tags. Am I understanding this correctly?
I’m liking this approach, but it would mean having to go back and update hundreds of objects in the PLC (Allen-Bradley Plant PAX objects)…doesn’t sound like its going to happen overnight.

Most PLCs, including AB, will silently rollover when adding to an integer when at or near the max value. In older PLCs, you might need to set a config parameter to allow it.

Most databases don’t have a problem with many columns. You might want to break it up by plant area or similar. And yes, you’d have to manually do this. If it must be automated, consider using a gateway timer script instead of a transaction group.

Hi @drojas. I’m with @pturmel on this one.

Do your totalising in the PLC, then use a transaction group to push the hourly or daily total to a database table. It would be prudent here to do some buffering/StoreAndForward array in the PLC in case your transaction group fails. I would generate the timestamp, value, totaliser ID/Name from the PLC when the totaliser is reset and not depend on when the transaction group actually executed. Ensure PLC clock is in sync with Ignition, there are many ways to do that.

Your database table would then have recorded totals of all your totalisers. One row per sample along with PLC timestamp, value, totaliser ID/Name. Essentially your own Historian. Then, with your report, you can write all kinds of queries with WHERE clauses on timestamp and totaliser ID/Name. And storing samples hourly would give you more options when you come to do your report.

The other thing about totalising in the PLC is you can gate/condition your totalising with whatever the process is doing. E.g. using the same flowmeter you can have total A through valve A, and total B through valve B. You can also ignore those annoying negative or cut-off values from your 4…20mA.

By buffering records in the PLC with a timestamp and totaliser ID/Name. You wouldn’t need to modify the transaction group every time you add a totaliser :slightly_smiling_face: The transaction group will just need to check if there is a new record in your PLC buffer, copy it, handshake back and then the PLC can shuffle your buffer.

Hope this makes sense and helps.

1 Like

Thanks ziad. This is interesting. I’ve been trying to keep everything within the built-in historian, but creating a separate table for storing totalized values seems more straight forward. And creating just a few tags on the PLC that will load various totals in an array does mean less maintenance on the Ignition transaction group or timer script.

I agree that the PLC should be the one doing the filtering of data such as establishing a cutoff flow (anything less than X amount is essentially no flow) or associating with the open/close state of a valve.

Just to be clear: I recommend never resetting totalizers. Or other product/cycle counters. Let them roll over. Odometer-style. Emulate resetting, where needed, through snapshots and arithmetic.

This is a good idea. Particularly when the buffer contains never-reset odometer values with the timestamp.

I’m curious. Why? If Store, Buffer, Reset is all done in the same line, we should be safe…

Spot on @drojas. Any purpose built storing of events or production records I’d use transaction groups as they’re event driven. But obviously, you can’t trend them with Easy Chart easily. So it depends what you’re after.

Because there’s always the possibility of one or more such records being lost on the way to the DB. With odometers, a missed record simply hurts the granularity of time periods that can be examined – the total computed between two records is still correct. You just can’t go more than one rollover period without a record.

Fair enough. That’s why I’d buffer in the PLC. Different ways to skin a cat I guess :slight_smile:

And buffers overrun. Especially if you are recording at short intervals for fine granularity. Some cat-skinning methods yield higher-quality fur than other methods.



1 Like

I store hourly and daily totalized data using the same method (Transaction groups). I have created dynamic reports using date range then using the sum query to get month to date and monthly totals. This also allows for determining the maximum, minimum and average values for the date range.

Despite the arguments for/against, I wish to integrate/totalize in Ignition.

It seems (to me) that the Tag History/Tag Calculation “aggregate” functions natively query the data necessary to integrate/totalize a selected range of data.

But considering “aggregate” functions are an OPC HDA standard, there must be an obvious reason why “Integrate” and “Totalize” are not standard aggregates.

Before I go down the custom tag history aggregate rabbit hole, can anyone advise why integration/totalization aren’t already a thing? (assuming Trapezoidal integration is the preferred method)

Given: accuracy/precision are relative to scan time/data availability/validity

Integration of rates to yield totals is a task fraught with peril. Mathematically speaking. Accuracy and Precision are at odds with each other for this task, and it gets worse the further from the raw signal you get. Totalizing good enough for billing purposes invariably involves a dedicated ratemeter that handles the totalizing internally, delivering a pulse per unit volume or similar digital interface. Integration of slow rate samples is wildly inaccurate. PLCs with fast scan times can accurately totalize an analog signal, but it generally requires careful bounds of a float value that artificially overflows into an integer (as described above).

Totalizing from a history of slow samples not a “thing” because its results can’t be trusted.

Appreciate the input. Is that representative of IA’s position/rationale for not offering such a feature? if so I will remove my feature/idea suggestion

In my limited experience, historical rate-based totalizers are quite useful for long-term process analysis and/or to cross-check/reconcile the static weights that are used for billing, especially in processes with appreciable loss rates. Further, they’re used for KPI tracking in sub-processes.

I believe OSI pi offers Totalization tags.

I found (via google) at least one (perhaps obscure) historian with a time-integral totalizer aggregate function (p9)

I am not an IA employee.

In a purely statistical sense, yes, integration of rate records from long-term history may be useful as a sanity check of totals from other measurement sources. For such a purpose, I would query the raw data from Ignition’s historian, and script a time-weighted integral over the entire period.