Combine result of two Data sources into a table Report

Ignition
1

Hi
I define two data sources in report with the same time range:
1 - History Tag Query with ‘Average’ aggregation mode
2 - History Tag Query with ‘Range’ aggregation mode

How can I use the result in only one table in report? The table component only accept one Data Key.
I want some column show average and some of them show Range for the same interval.

2

You’ll need to use a script data source to combine them into one dataset, then use the key created by the script in your table.

1 Like
3

Could you please show me some examples code

4

Since I have no idea what you have, I’ll assume a few things:

  • Two exiting datasources: averageQuery and rangeQuery
  • Each dataset has two columns: t_stamp and value

There are a couple of different ways to combine datasets.

If the datasets have different time stamps and you want to keep them all. This is great for graphs and charts, but perhaps not so much for tables:

def updateData(data, sample):
	"""
	This function has the opportunity to add additional data into the report's
	data map.

	Arguments:
		data: This is a map, whose keys are report data keys and values should
		      be sequences, maps, scalar values, or datasets to provide information to
		      the report.
		sample: A flag that will be True if the report data being gathered is
		        for a preview of the report. Use to avoid slow queries and calculations
		        to keep previews quick.
	"""
	averageDataset = system.dataset.toPyDataSet(data['averageQuery'])
	rangeDataset   = system.dataset.toPyDataSet(data['rangeQuery'])
	
	headers = ['t_stamp', 'average', 'range']
	dataOut = []
	for row in averageDataset:
		dataOut.append(row[0],row[1], None)
	for row in rangeDataset:
		dataOut.append([row[0], None, row[1]])
		
	combinedDataSet = system.dataset.toDataSet(headers, dataOut)
	data['combinedData'] = system.dataset.sort(combinedDataSet, 0)

Or you just keep the timestamps from one dataset, which makes for a cleaner looking table. (this example uses the timestamps from the average data):

def updateData(data, sample):
	"""
	This function has the opportunity to add additional data into the report's
	data map.

	Arguments:
		data: This is a map, whose keys are report data keys and values should
		      be sequences, maps, scalar values, or datasets to provide information to
		      the report.
		sample: A flag that will be True if the report data being gathered is
		        for a preview of the report. Use to avoid slow queries and calculations
		        to keep previews quick.
	"""
	averageDataset = system.dataset.toPyDataSet(data['averageQuery'])
	rangeDataset   = system.dataset.toPyDataSet(data['rangeQuery'])
	
	headers = ['t_stamp', 'average', 'range']
	dataOut = []
	for avgRow, rangeRow in zip(averageDataset, rangeDataset):
		dataOut.append([avgRow[0],avgRow[1], rangeRow[1]])
	
	data['combinedData'] = system.dataset.toDataSet(headers, dataOut)
2 Likes
5 
dataOut.append(avgRow[0],avgRow[1], rangeRow[1])

i think needs to be 

dataOut.append([avgRow[0],avgRow[1], rangeRow[1]])
6

What if you are trying to combine 2 datasets that do not contain the same amount of rows. For an example, I have two queries where I’m gathering historical data from. Sometimes, depending on the timing, we might have more daily records in one table then the other. How does that work?

7

I had to add .getCoreResults()

averageDataset = system.dataset.toPyDataSet(data['averageQuery'].getCoreResults())

1 Like