# Report with Scripted Nested Data

**URL:** <https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801>\
**Category:** Ignition\
**Created:** [June 10, 2016, 4:39pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801 "2016-06-10T16:39:31Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 13, 2016, 3:36pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/2 "2016-06-13T15:36:17Z")

</div>

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

```auto
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):

```auto
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:

```auto
	# 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:

```auto
	# 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…}

---

_[View the full topic](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801)._