Nested Historical Query in Report works, issues with Graphically Presenting Data

Ignition
1

Hello,

So I worked from the following post to get my Nested Child queries working. Report with Scripted Nested Data

Doing a few named queries using above idea. I am also doing a Historical Query, returning one row. I believe the Underlying Historical Dataset in the Scripting Data source has data(see Wrapper.log below).

I am struggling with how to generically reference a Data source in a Report Designer Table Component, by a Column Index, not an explicit Column Name.

If you follow the Script - Data source below I wind with a Parent- “SelectedRows” with a Child “K123Historical”
If put in a Default Value for ‘TagAliases’ and ‘TagPens’ in the Data Source, I can graphically drag and drop to build table. When I go and pass in new ‘TagPens’ and ‘TagAliases’ as Report Parameters, the report is still using the Original ones from Default Value for Data Source. I need to not “Drag and Drop” Explicit Column Names, but somehow refer to a Column Index of the underlying Data Source. This might not be possible?

from system.report import QueryResults

wholeSelectedRows = data['WholeSelectedRows']

parentqr = QueryResults(wholeSelectedRows)

print "!from Reporting Data Source"
aliases= data['TagAliases']
aliasesPyDS = system.dataset.toPyDataSet(aliases)
pySeq = ["t_stamp"]
for row in aliasesPyDS:
	for value in row:
		pySeq.append(value)
print "pySeq",type(pySeq), pySeq
		
tagPens = data['TagPens']

tagPensPyDS = system.dataset.toPyDataSet(tagPens)
tagPensPySeq = []
for row in tagPensPyDS:
	for value in row:
		tagPensPySeq.append('[default]'+value)
print "tagPens",type(tagPensPySeq), tagPensPySeq


childListK123Tstamp = []
for r in range(wholeSelectedRows.rowCount):
	K123_Test_ID = wholeSelectedRows.getValueAt(r,"K123_Test_ID")
	childDSK123Tstamp = system.db.runNamedQuery("K123XL100","SelectK123TestIDtstamp", {"Test_ID":K123_Test_ID})
	date = childDSK123Tstamp.getValueAt(0,0)
	newHistoricalData = system.tag.queryTagHistory(paths=tagPensPySeq, startDate=date, endDate=date, returnSize =1,columnNames= pySeq,aggregationMode = "LastValue")
	histPyDS = system.dataset.toPyDataSet(newHistoricalData)
	print histPyDS
	print histPyDS.getColumnNames()
	for row in histPyDS:
		for value in row:
			print value
	childListK123Tstamp.append(QueryResults(newHistoricalData, parentqr,r))
parentqr.addNestedQueryResults('K123Historical', childListK123Tstamp)	

data['SelectedRows'] = parentqr

Output from Wrapper.log


INFO   | jvm 4    | 2018/09/21 12:34:52 | !from Reporting Data Source
INFO   | jvm 4    | 2018/09/21 12:34:52 | pySeq <type 'list'> ['t_stamp', u'Punch 1', u'Punch 2', u'Punch 3', u'Punch 4', u'Actual Pre Force']
INFO   | jvm 4    | 2018/09/21 12:34:52 | tagPens <type 'list'> [u'[default]K123_XL100/Punch 1', u'[default]K123_XL100/Punch 2', u'[default]K123_XL100/Punch 3', u'[default]K123_XL100/Punch 4', u'[default]K123_XL100/Actual Pre Force']
INFO   | jvm 4    | 2018/09/21 12:34:52 | <PyDataset rows:1 cols:6>
INFO   | jvm 4    | 2018/09/21 12:34:52 | [t_stamp, Punch 1, Punch 2, Punch 3, Punch 4, Actual Pre Force]
INFO   | jvm 4    | 2018/09/21 12:34:52 | 2018-09-21 11:23:51.585
INFO   | jvm 4    | 2018/09/21 12:34:52 | 5.3575091362
INFO   | jvm 4    | 2018/09/21 12:34:52 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:52 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:52 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:52 | 0.0
INFO   | jvm 4    | 2018/09/21 12:34:52 | <PyDataset rows:1 cols:6>
INFO   | jvm 4    | 2018/09/21 12:34:52 | [t_stamp, Punch 1, Punch 2, Punch 3, Punch 4, Actual Pre Force]
INFO   | jvm 4    | 2018/09/21 12:34:52 | 2018-09-21 11:24:02.191
INFO   | jvm 4    | 2018/09/21 12:34:52 | 5.3575091362
INFO   | jvm 4    | 2018/09/21 12:34:52 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:52 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:52 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:52 | 0.0
INFO   | jvm 4    | 2018/09/21 12:34:53 | <PyDataset rows:1 cols:6>	
INFO   | jvm 4    | 2018/09/21 12:34:53 | [t_stamp, Punch 1, Punch 2, Punch 3, Punch 4, Actual Pre Force]
INFO   | jvm 4    | 2018/09/21 12:34:53 | 2018-09-21 11:24:24.319
INFO   | jvm 4    | 2018/09/21 12:34:53 | 5.3575091362
INFO   | jvm 4    | 2018/09/21 12:34:53 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:53 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:53 | -0.00999999977648
INFO   | jvm 4    | 2018/09/21 12:34:53 | 0.0
2

I think I figured this out. Or I probably read this somewhere on forum and subconsciously remembered it.

So I modified the script above for the Historical Query columnNames pySeq above to be generic. For however many Pens in my TagPen Aliases Dataset, then generate a Generic pySeq Col0, Col1, etc.

Then I could use a test/dummy default value dataset to graphically build my report using the Col0, Col1, names. At first I only defined 6 Physical Columns for Dynamic Tag Pens.
To label the Column Headings dynamically I had to rely on Array Index Column notation from manual @dataSource[0].columnName@. In my case @TagAliases[0].aliases@ ( Tag Aliases is Report Parameter, type Dataset, my test/default Value was a single column Dataset with Column labeled “aliases”)
https://docs.inductiveautomation.com/display/DOC79/Data+Keys

When I pass my Client Tag Reporting Parameters of Tag Pens and Tag Aliases, the report appears to keep up with my preexisting Template Repeater that has same functionality.

Passing less than 6 Tag Pens/ Aliases displays <N/A>'s(livable), passing too many Tag Pens/ Aliases does not appear to cause any issues with Report is displayed in Designer.

1 Like