Report Datasets

Ignition
1

Hello

I’m storing a level using the History property of the tag. I would like to develop a report that returns the avg, min and max grouped by hour for a given begin and end time. Seems like there are a few ways to accomplish this and I was wondering what is best practice.

What is the best option to bind the report dataset to? Tag History, DB Browse, SQL Query?
Where would the grouping/aggregation take place? In the query loading the dataset or in the report itself?

Currently my archiving strategy is using the automatic table design and default settings where every month a new table is created with the tagid, floatvalue, t_stamp, etc columns. I would prefer to get the data without having to write any SQL, but open to whatever is recommended.

Thanks for any advice

2

You can’t bring back the min, max, and average in the same SQLTags history binding. What I normally do is bind the dataset of the report to a tag history binding bringing back the natural sample size for a period of time. That way you have the raw data. The report (as well as expression bindings) can perform functions on the dataset to get the min, max, and average.

3

Thanks for the reply. Any suggestions on how to group the min, max, avg of the raw data by hour in the report?

4

That is a little tricky. I wasn’t thinking about that. I was thinking about a single min/max/avg of a tag for a certain period of time.

In this case you will probably have to use scripting to generate a new dataset that contains an easy grouping column. You want to be able to group by the hour in the report to calculate the min/max/avg. I will add a feature request in our system to make this easier since it shouldn’t be so complicated.

In the meantime, you can do it through scripting. First bring back the raw data to a dataset. We can add a propertyChange script on the component where the raw dataset property is with the following script:[code]if event.propertyName == “rawDS”:
oldDS = event.newValue
rawData = system.dataset.toPyDataSet(oldDS)
header = []
for column in oldDS.getColumnNames():
header.append(column)
header.append(“hour”)
newData = []

for row in rawData:
	rowData = []
	for col in row:
		rowData.append(col)
	rowData.append(system.db.dateFormat(row[0], "yyyy-MM-dd HH:00:00"))
	newData.append(rowData)

event.source.calcDS = system.dataset.toDataSet(header, newData)[/code]You can then use the new calcDS in your report and you can group by the new column "hour". When you do grouping in the report you can perform min/max/avg calculations. Hope this helps and let me know if you get stuck.