Display totals by unit and hour, better method?

I have an existing vision window that shows the number of events per unit, per hour. The unit is parsed form the tag name. It works with gateway tag change script that increments the individual total tags, and another gateway tag change script that resets them at midnight. I also have the OPC tags logging with historian. I spent a bit of time working with a dataset to do this, but i think i would need to loop through it and insert zeros for instances where an event has not occurred yet. Does anyone know a better way of doing this?

So where is your current data being stored? and how are those values on screen being populated?

Its stored in two places, in historian tables that i use for reporting, and in a bunch of memory tags that go like Unit1_00_Total, Unit2_01_Total and so on.

Ok, so the gateway script sets the values of all of these tags every hour? and again at midnight it resets them all to 0

It updates on each tag change event, its basically a live status page for the supervisor.

But I presume that the only tags that update themselves are those that represent the current hour?
And following on from that, the table therefore presents the counts of things produced by each unit, per hour for the current day?

I don’t see any issues with using tags for this and I think a dataset would only complicate things. I assume you’re using a template for each unit row?

Yes, but the table is logging any change to the opc tags, i have to do some filtering to only get the ones i want for totals.
This is the tag change script, the 28 tags are the opc boolean tags that indicate something happened.

I actually built this quite some time ago, and did not use templates, but that is a great idea. It totally works, but thought there may be a smarter way. Thank you.

*Also fair ponit on the data set, i just went back and looked at the queries for the reports… not going there again.

Just FYI, instead of using .getValue(), .getTagPath() etc, you can just use the more Pythonic methods: .value, .tagPath.itemName etc.

It looks like all of your tags are in a flat structure with every units’ tags all in the one folder.
I would make a few changes here:

  • create a couple of UDTs:
  1. Unit
  2. Unit Hourly Total

Unit Hourly Total has tags:

  • Total [int]
  • Count Trigger [boolean]
    • Add your tag change event script to this tag and remove the global event script

Unit has at least these tags (and any other relevant tags):

  • Totals/Hour 00 [Unit Hourly Total]
  • Totals/Hour 01 [Unit Hourly Total]
  • Totals/Hour … [Unit Hourly Total]
  • Totals/Hour 23 [Unit Hourly Total]

This will help to standardise your tags and will also simplify your tag change event script as you can use relative referencing inside of it instead of hardcoding them (e.g. system.tag.readBlocking('[.]Total'))

1 Like

I was looking into creating a UDT for it, but i was going down the data set route. All good advice, i will try it out. Thanks again.

One thing you didn’t mention is how your database table is being written to? (for reporting)

With tag historian, I disabled partitioning.

You could also consider "up"serting the values within the tag change script as well. That would give you a much cleaner way of producing the data again for the report, unless of course you do want to see counts over smaller increments in time and not just hourly.

That’s actually the original way i was doing it, but i wanted to try out historian. I also wasn’t sure if the tag change script running a bunch of queries would have a performance issue.

That’s exactly what the historian does (well, via the store and forward system, which you could also use with system.db.runSFUpdateQuery) :slight_smile: sometimes many many times/second.

The historian is made for constantly changing data that you want to present on a timeline. It’s not great for structured tables like the one you’ve got. Obviously you can still use it for that, but the queries become unwieldy and expensive

Good to know, and also means if i have to do this again i dont need that module.

I would use tag history. Use count aggregate over one hour interval. Let ignition do the hard stuff for you.

1 Like

Thanks, I don’t have a lot of experience with using historian but i will try this.

https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory