Ignition Reporting database design

Ignition
1

Hi all,

I’ve set up several transactions in transaction groups to log data to a table. The transactions log data from several different data points from several different sites every hour, on the hour. Essentially, I have a table that has the columns - Timestamp, SiteName, Value, and Description (the type of value it is. Temperature, volume, etc.). All the different values from all the different sites are all lumped in there together.

On one of the tables in the report, I would like to look something like this (just a mockup I did in excel):

image.png548×630 6.16 KB

What’s the best way to go about achieving the above table in my report?
I need to account for null values, as well if there are multiple entries for one time period on accident. Would nested queries be what I need? Or maybe having many different SQL queries for just one item, and then joining them together with a python script. If you’ve solved this problem before, feel free to contact me directly. Thanks!

2

This is a combination of grouping and pivoting/crosstab. Pretty much every database can do this, but syntax varies greatly. /-:

3

I’m using MySql.

I’m considering making a python script that creates the dates and times in a list/, and then grabs the data for each site value dynamically (get unique sitenames with a seperate sql query at the start). Then have it iterate through each sitename, asking for the values. When it adds the values to the first dataset of times, if there’s no value to add it will give it a null value.