I have the need to create a report where the user selects some number of days (variable), then the report will group first by tag, then by day and show min, max, times for each, and avg of the tags for each day.
I had created a transaction group with the tags thinking that would be easier since i wouldn’t have to worry about going across monthly data tables. But with each tag being its own column i am not sure how to group by column? Am now wondering if would be better off using the regulary history data tables (configured for monthly) and grouping by tag ID?
Would appreciate any advice that may point me in the right direction.
Thanks,
Scott
You can simply use a UNION query to pull the data out of your ‘wide’ table. Assuming you have a table called myTagHistory with columns for mytag1 and mytag2 values, you could use the following query -
SELECT t_stamp, 'MyTag1' as tag, mytag1
FROM myTagHistory
UNION
SELECT t_stamp, 'MyTag2' as tag, mytag2
FROM myTagHistory
ORDER BY t_stamp
Personally I prefer long tables but then you’ll probably have to do a lot of pivot queries, so if you’re not into doing pivot queries then stick with what you got and use the UNION example.
Thanks Pat,
That was very helpful. Now i can see that gets me the dataset needed and I can now group on the ‘tag’ column.
Any thoughts now on how to break the data out by day show the min, max avg for each day?
Also, it sounds like you prefer to work with the regular history tables(long tables). Can you elaborate on that a bit for me. Is it difficult to query across multiple tables? to cross reference to the tagID, etc.
I am going to be up against that on another report in the near future so may be best to just go that route now.
Thanks a lot for your help.
Scott
There’s a good training video on the website that goes over the Report Module. Your aggregate functions (min, max, total, avg) are located in the Keys panel http://www.inductiveautomation.com/support/usermanuals/ignition/index.html?reporting_concepts_substitutionkeys.htm
As far as whether to use long or wide tables, go with what’s easiest for you to use for your requirements. We are often asked to display data in a wide format, customer wants a report to show 2 different flows in a table, so using a wide table is quick and easy to do. To pull this report from a long table requires a pivot query, it’s a more complicated query to create. Here’s a simple example -
[code]
Customer’s Report
±-------±---------±---------+
| Time | Flow1 | Flow2 |
±-------±---------±---------+
| 07:00 | 502 | 546 |
| 07:01 | 508 | 541 |
±-------±---------±---------+
WideFlowTable WideFlowTable Query -
±-------±---------±---------+ SELECT * FROM WideFlowTable
| Time | Flow1 | Flow2 |
±-------±---------±---------+
| 07:00 | 502 | 546 |
| 07:01 | 508 | 541 |
±-------±---------±---------+
LongFlowTable LongFlowTable Query -
±-------±---------±---------+ SELECT Time,
| Time | Tag | value | MAX( if (tag = ‘Flow1’, value, null)) as ‘Flow1’,
±-------±---------±---------+ MAX( if (tag = ‘Flow2’, value, null)) as ‘Flow2’
| 07:00 | Flow1 | 502 | FROM LongFlowTable
| 07:00 | Flow2 | 546 | GROUP BY Time
| 07:01 | Flow1 | 508 |
| 07:01 | Flow2 | 541 |
±-------±---------±---------+[/code]
So obviously in this example using a wide table is quick and simple. Problems with using a wide table arise when the customer has a lot of flows, this creates a very wide table. If the customer decides to add or remove a flow, you need to modify the table and now the database is storing irrelevant data. What if you want to store additional data related to the flow value, like say when a pump was running? What if some of the flow values were entered by an operator, you’d probably want to record who entered it and when. What if a value was altered, you’d probably want to note that information.
When I first started using FactoryPMI & FactorySQL I used wide tables, when I started running into limitations with using wide tables I learned about pivot queries and switched to using long tables. Valid arguments for either format can be made, what you use should boil down to what you’re comfortable with and your requirements.