Report with Scripted Nested Data

Ignition
2

So, I figured this out on my own with a lot of help from inspect.py, which showed me this:

Java Type:   <type 'com.inductiveautomation.reporting.common.api.QueryResults'>
  M: <type 'void'> addNestedQueryResults(<type 'java.lang.String'>,<type '[Lcom.inductiveautomation.reporting.common.api.QueryResults;'>)
  M: getCoreResults()
    returns <type 'com.inductiveautomation.ignition.common.Dataset'>
  M: <type 'java.util.TreeMap'> getNestedQueryResults()
  M: <type 'java.lang.Object'> get(<type 'int'>)
  M: get(<type 'int'>)
    returns <type 'com.inductiveautomation.reporting.common.api.QueryResults$Row'>
  M: <type 'int'> size()
  M: <type 'java.lang.Object'> lookup(<type 'int'>,<type 'java.lang.String'>)

I tossed the reporting module’s jars into Eclipse so I could look at the constructors and their arguments and found two (long classnames omitted):

QueryResults(Dataset dataset)
QueryResults(Dataset dataset, QueryResults parent, int parentRow)

The first obviously just wraps a dataset. The second looks like a way to associate a dataset to each row of a parent query. Which can then be given a Data Key with the .addNestedQueryResults() method on the parent. I came up with this first attempt:

	# Obtain the QueryResults class for our own use
	from com.inductiveautomation.reporting.common.api import QueryResults
	# Build and reprocess parent data
	pyds = system.db.runPrepQuery(.....)
	heads = list(pyds.underlyingDataset.columnNames)+['extra column name']
	newRows = []
	for row in pyds:
		newRow = [x for x in row]+['extra column data']
	parentds = system.dataset.toDataSet(heads, newRows)
	parentqr = QueryResults(parentds)
	# Build child datasets
	childList = []
	for r in range(parentds.rowCount):
		# Use parentds.getValueAt(r, 'some ref column') to retrieve foreign key(s)
		childpyds = system.db.runPrepQuery(.....)
		childList.append(QueryResults(system.dataset.toDataSet(childpyds), parentqr, r))
	# Attach list of children to parent
	parentqr.addNestedQueryResults('ChildKey', childList)
	# Add result to the report data map
	data['ParentKey'] = parentqr

Which promptly blew up trying to import QueryResults. Hmmph. You can’t directly import classes from a non-core library. So I cheated: I created a Query Key ‘ZZ’ from a simply query that returned no rows and made sure it was above the script in the list of data sources. Then I changed the top of the script to be:

	# Obtain the QueryResults class for our own use
	QueryResults = data['ZZ'].getClass()
	# Build and reprocess parent data
......

Et voilà! Scripted data with nested scripted data.

{Edited to new format…}

9 Likes