# 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:** 20\
**Page:** 1

<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 10, 2016, 4:39pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/1 "2016-06-10T16:39:31Z")

</div>

The report datasource scripting examples explain how to access the nested data in another key for use constructing a custom dataset. What I don’t see is how to construct nested data that would be suitable for use with a child table in a table group. I have a parent dataset that I’m constructing via script due to annoying limitations of an ancient SQL Server 2000 instance. I need a subquery for each row that could be done with a normal query, but there’s no way to add a regular subquery to a scripted dataset.

Hint please?

---

<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…}

---

<div class="post-metadata">

**Author:** ![carlos.poncedeleon](https://avatars.discourse-cdn.com/v4/letter/c/b77776/32.png) [@carlos.poncedeleon](https://forum.inductiveautomation.com/u/carlos.poncedeleon)\
**Post date:** [August 2, 2016, 9:01pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/3 "2016-08-02T21:01:10Z")

</div>

Very helpful. Thank you!

---

<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:** [August 2, 2016, 11:07pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/4 "2016-08-02T23:07:05Z")

</div>

You’re welcome! I wondered at the time if anyone even read it. I was kinda’ hoping for Kathy Applebaum to short circuit my investigation.  
I can be a bit impatient sometimes. 🙂

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [August 3, 2016, 1:23pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/5 "2016-08-03T13:23:36Z")

</div>

Sorry, Phil, I have my head deep into making 7.9 full of awesome, so I’m not checking the forums every morning. 😃

---

<div class="post-metadata">

**Author:** ![Trent.Boudreaux](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/trent.boudreaux/32/4138_2.png) [@Trent.Boudreaux](https://forum.inductiveautomation.com/u/Trent.Boudreaux)\
**Post date:** [May 25, 2017, 3:29pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/6 "2017-05-25T15:29:55Z")

</div>

Thanks for posting this Phil.  
I tested it out and got it working.  
Neat workaround, hopefully nested datasets get supported in the python API soon.

---

<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:** [September 29, 2017, 2:35pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/7 "2017-09-29T14:35:32Z")

</div>

Hey Kathy,  
The problem with importing across classloaders could be mitigated by placing the following in the Java class that gets loaded to [system.report](https://docs.inductiveautomation.com/display/DOC79/system.report):

```auto
public static final Class<?> QueryResults = com.inductiveautomation.reporting.common.api.QueryResults.class;

```

Then anyone’s scripts could do:

```auto
from system.report import QueryResults

```

Pretty please?

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [September 29, 2017, 3:56pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/8 "2017-09-29T15:56:30Z")

</div>

I made a ticket for that. I may have some pull with the person who prioritizes. 😉

---

<div class="post-metadata">

**Author:** ![jake.richard](https://avatars.discourse-cdn.com/v4/letter/j/74df32/32.png) [@jake.richard](https://forum.inductiveautomation.com/u/jake.richard)\
**Post date:** [October 17, 2017, 4:12pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/9 "2017-10-17T16:12:22Z")

</div>

Thanks for posting the solution.  
I tried your solution and was able to make it work.  
This is exactly what I was trying to do in order to make it more intuitive for others that come behind me in the future to organize the data. It really makes it easy for them to layout the data using the report designer interface.

---

<div class="post-metadata">

**Author:** ![rudi.putzeys](https://avatars.discourse-cdn.com/v4/letter/r/ce7236/32.png) [@rudi.putzeys](https://forum.inductiveautomation.com/u/rudi.putzeys)\
**Post date:** [November 26, 2018, 10:31am UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/10 "2018-11-26T10:31:33Z")

</div>

Hi Phil,

can you also script nested queries in nested queries (so 3 levels)?  
I want to generate a table (scripted) to see some detailed information (including a timeseries table) about some machines ordered by date.  
My table should look like:  
(I got so far by using queries)

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/9/9f89a0eb32b9a5337ddce31e5c0b82b106afe192.JPG)

I tried this:

```auto
 	QueryResults = data['Qdatum'].getClass()

 	dataParent = system.dataset.toDataSet(['Parent data'],['1', '2'])
 	dataChild = system.dataset.toDataSet(['Child data'],['a','b'])
 	dataChildChild = system.dataset.toDataSet(['ChildChild data'],['I', 'II'])
 	
 	QueryParent = QueryResults(dataParent)
 	QueryChild = QueryResults(dataChild)

 	
 	ListChild = []
 	for x in range(dataParent.rowCount):
 		ListChildChild = []
 		for y in range(dataChild.rowCount):
 			ListChildChild.append(QueryResults(dataChildChild, QueryChild, y))	
 		
 		QueryChild.addNestedQueryResults('ChildChild', ListChildChild)
 		
 		ListChild.append(QueryResults(dataChild, QueryParent, x))
 	
 	QueryParent.addNestedQueryResults('Child', ListChild)
 	
 	data['Parent'] = QueryParent

```

Thanx for your help!

Kind regards,

Rudi

---

<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:** [November 26, 2018, 2:07pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/11 "2018-11-26T14:07:38Z")

</div>

I would expect it to be possible, if the order of operations is observed carefully. In particular, the nested and subnested queries should be generated within the loops from the specific parent row’s criteria. Your toDataSet() calls don’t look valid, even for a test.

---

<div class="post-metadata">

**Author:** ![rudi.putzeys](https://avatars.discourse-cdn.com/v4/letter/r/ce7236/32.png) [@rudi.putzeys](https://forum.inductiveautomation.com/u/rudi.putzeys)\
**Post date:** [November 26, 2018, 3:12pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/12 "2018-11-26T15:12:35Z")

</div>

I don’t see what I’m doing wrong.  
Ok, the headers are confusing. I changed them to ‘parent data’ etc. But they are real datasets, aren’t they?  
In the designer, I get:  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/b/b6c8780b438ae0a805692e0a45f1c091accca9cf.png)

In preview mode:  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/c8200f3b8186a51ed94c8d9e0188df82287df7a1.png)

So far, so good…

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [July 28, 2020, 1:31pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/13 "2020-07-28T13:31:29Z")

</div>

Resurrecting this thread for a moment to add my thanks, Phil. I had the same sort of thing going on with-- yep-- an SQL Server 2000 instance.

---

<div class="post-metadata">

**Author:** ![joe.hammond](https://avatars.discourse-cdn.com/v4/letter/j/e9c0ed/32.png) [@joe.hammond](https://forum.inductiveautomation.com/u/joe.hammond)\
**Post date:** [October 22, 2020, 8:06pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/14 "2020-10-22T20:06:35Z")

</div>

Any hope getting this workaround into a straightforward solution in Reporting? When working with Sepasoft MES data we must work with Analysis datasets. So this thread seems like a path to be able to nest datasets but it seems to be more of a hack than a feature. Its been 4 years, is this available somewhere in version 8 that I am not aware of? Thanks!

---

<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:** [October 22, 2020, 8:30pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/15 "2020-10-22T20:30:23Z")

</div>

Someone would have to define exactly how they think it should work and write it up over at the ideas portal. The only piece of the above that I consider a work-around is the hack I used to get the QueryResults class. The rest is just using its methods to attach inner datasets to an outer dataset.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 22, 2020, 9:10pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/16 "2020-10-22T21:10:49Z")

</div>

Also, the `QueryResults` workaround Phil mentioned was implemented (exactly as described) in 7.9.6, so you don’t even have to use that anymore.

---

<div class="post-metadata">

**Author:** ![craigl](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/craigl/32/5010_2.png) [@craigl](https://forum.inductiveautomation.com/u/craigl)\
**Post date:** [October 23, 2020, 5:16pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/17 "2020-10-23T17:16:19Z")

</div>

What Joe is getting to is our current effort to created nested datasets for reporting from data that did not originate from SQL queries. We are building datasets on the fly from MES analysis results and turning them into QueryResults object to take advantage of the addNestedQueryResults feature. Ideally there would be a way to create a nested dataset that could be recognized by the reporting tools without going through QueryResults.

---

<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:** [October 23, 2020, 5:20pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/18 "2020-10-23T17:20:49Z")

</div>

I’m a bit confused. The QueryResults wrapper _ **IS** _ the composite object–parent dataset plus nested datasets. How can you **not** have it? Datasets do not support nesting themselves and have no meaningful way to do so.

---

<div class="post-metadata">

**Author:** ![craigl](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/craigl/32/5010_2.png) [@craigl](https://forum.inductiveautomation.com/u/craigl)\
**Post date:** [October 23, 2020, 5:27pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/19 "2020-10-23T17:27:02Z")

</div>

I think that’s the heart of the matter, and the answer.  
If the only object that supports nesting is QueryResults then that’s probably the only way to do it. It just feels odd creating something called QueryResults from data that isn’t really the result of a query.  
I just want to make sure that there isn’t some other way I’m overlooking.  
Sounds like we are going down the only possible path for what we want to do. I’m just struggling a bit getting it to work right.

Here is what I have, and I’m clearly doing something wrong. It works f I comment out the second lineData.append, but crashes the report if I use both

```auto
from system.report import QueryResults
	
logger = system.util.getLogger("cLogger")

headers = ["Area", "LinePath", "Line", "EquipmentName", "OEE", "OEE Availability", "OEE Quality", "OEE Performance"]
lineData = []
lineData.append(["1", "2", "3", "4", "5", "6", "7", "8"])
lineData.append(["x1", "x2", "x3", "x4", "x5", "x6", "x7", "x8"])
	
primaryDS = system.dataset.toDataSet(headers, lineData) 
parentqr = QueryResults(primaryDS)
	
count = 0	
for theRow in lineData:
	eqPath = theRow[1]
		
	logger.info("start row process path %s" % eqPath)
	headers2 = ["Duration", "Reason"]
	subData = []
	childList = []
	
	for reasonCount in range(3): 
		subData.append(["Dur %s" % reasonCount, "Reas %s" % reasonCount])
	tempDS = system.dataset.toDataSet(headers2, subData);
	logger.info("ok %s" % tempDS)
	childList.append(QueryResults(tempDS, parentqr, count))
	logger.info("added subdata %s" % count)
	
	count += 1
		
parentqr.addNestedQueryResults('DowntimeReasons',childList)
		
data['OEEDT'] = parentqr 

```

Exception thrown in designer console is:

```auto
11:06:50.754 [AWT-EventQueue-0] WARN reporting.Preview - Error generating preview
java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1
at com.inductiveautomation.reporting.designer.workspace.design.PreviewPanel$PreviewDataXmlWriter.writeQueryResults(PreviewPanel.java:342)
at com.inductiveautomation.reporting.designer.workspace.design.PreviewPanel$PreviewDataXmlWriter.writeObject(PreviewPanel.java:385)
at com.inductiveautomation.reporting.designer.workspace.design.PreviewPanel$PreviewDataXmlWriter.writeMap(PreviewPanel.java:312)
at com.inductiveautomation.reporting.designer.workspace.design.PreviewPanel$PreviewDataXmlWriter.toXml(PreviewPanel.java:294)
at com.inductiveautomation.reporting.designer.workspace.design.PreviewPanel$PreviewDataXmlWriter.toXml(PreviewPanel.java:271)
at com.inductiveautomation.reporting.designer.workspace.design.PreviewPanel$PreviewGenerator.done(PreviewPanel.java:111)
at java.desktop/javax.swing.SwingWorker$5.run(Unknown Source)

```

---

<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:** [October 23, 2020, 5:36pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/20 "2020-10-23T17:36:36Z")

</div>

I see a typo: `tempeDS` vs. `tempDS`. Otherwise looks right.

[Next page](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801.md?page=2)