Save tag value once per week for annual production chart

Ignition
,
1

Hi,

At a power plant I have a tag from the PLC called MWH_LAST_WEEK. At 12:00:00AM on Mondays this value changes. When it changes I want to write it to a new SQL table I will create in the ignition database called WEEKLY_MWH. The WEEKLY_MWH table will have 3 columns, YEAR (INT), WEEK (INT), and MWH, (unsigned 32 bit integer). I will create an onchange script on the MWH_LAST_WEEK tag so that when it changes it will write the year, an integer from 0-51 for the week of the year, and the new value of MWH_LAST_WEEK to the WEEKLY_MWH table.

Then I will create a chart bound to an SQL query which will extract the data from the table so that I get one series per year, with the X axis being the week in the year and the y axis being the MWH per week. I want the X axis labels to be the months in the year.

I have not done any SQL from within ignition yet. Is this the right way to do it?

thanks for any pointers

2

I would use a transaction group and have it trigger on the tag changing.

3

doesn’t look like my customer has purchased the transaction groups. Either way that seems to be a better but functionally equivalent way of getting the data in to the database than the on tag change script.

Any examples of how to extract the data from the table for charting? What would the query look like that returns the dataset of one series per year for the easy chart?

4

The query for the dataset would look something like:

SELECT SUM(MWH)
FROM WEEKLY_MWH
GROUP BY YEAR
5

Thanks Jordan. It has been about a decade since I’ve really done any SQL. I’ve been reading on the forum and it seems like I have to use ‘Chart’ instead of ‘EasyChart’. Is this still true?

6

If you’re going to use an aggregate like that, then yes, the standard chart should be your weapon of choice. :slight_smile:

7

since the columns in the table were WEEK OF YEAR, YEAR, and MWH but the chart I wanted to display was WEEK OF YEAR on the X axis and MWH on the Y axis, with one series per year what was required was something like an excel pivot chart. I didn’t figure out how to make it work with GROUP BY, since I am not sure that I want to aggregate any of the MWH values since I have set up a gateway script to write to this table once per week. Maybe there was some way to get this functionality with the historical data but I don’t know it either.

I used the postgres crosstab function for this, which is included as part of the postgres tablefunc extension.

install extension (only has to be done once per database)

psql -d IgnitionDB
CREATE EXTENSION tablefunc;

confirm it installed properly by this query or \dx postgres command

select count(*) from information_schema.routines where routine_name like 'crosstab%';

should see 6 rows returned. then the pivot table (crosstab) query I put in the chart SQL query binding is

SELECT * FROM crosstab
   (
      'SELECT week, year, mwh 
       FROM mwh_weekly 
      ORDER BY 1,2'
   )
   AS CT 
   (
       "Week" smallint,
       "2018" bigint,
       "2019" bigint,
       "2020" bigint,
       "2021" bigint,
       "2022" bigint,
       "2023" bigint,
       "2024" bigint
   )

image.png1028×593 18.2 KB

Ideally it would dynamically create the query to include the last 10 years but not any years before 2018, but I’ll leave it to someone else to update the query in 10 years to show 2028 to 2038.

Next challenge is to figure out how to make this work when there are multiple generators instead of just 1.