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

</div>

Not sure what’s going wrong. Consider using The [DatasetBuilder](http://files.inductiveautomation.com/sdk/javadoc/ignition80/8.0.10/com/inductiveautomation/ignition/common/util/DatasetBuilder.html) class to construct your datasets with explicit data types. system.dataset.toDataSet implicitly sets types with the first row.

---

<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, 7:13pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/22 "2020-10-23T19:13:10Z")

</div>

Thanks for looking. I’m totally stumped at this point.

Same results when converted to use DatsetBuilder (as I suspected). Works when run as below, but uncomment the 10th line and it will crash the report with the ArrayIndexOutOfBounds exception in Ignitions PreviewPanel code.

```auto
	from system.report import QueryResults
	from com.inductiveautomation.ignition.common.util import DatasetBuilder
	from java.lang import String
	
	builder = DatasetBuilder.newBuilder().colNames("Area", "LinePath", "Line", "EquipmentName", "OEE", "OEE Availability", "OEE Quality", "OEE Performance")
	builder.colTypes(String, String, String, String, String, String, String, String)
	builder.addRow("r1", "r2", "r3", "r4", "r5", "r6", "r7", "r8")
#	builder.addRow("x1", "x2", "x3", "x4", "x5", "x6", "x7", "x8")
	primaryDS = builder.build()

	parentqr = QueryResults(primaryDS)
	
	for count in range(primaryDS.getRowCount()):
		eqPath = primaryDS.getValueAt(count, 'LinePath')

		builder2 = DatasetBuilder.newBuilder().colNames("Duration", "Reason").colTypes(String, String)
		childList = []
	
		for reasonCount in range(3): 
			builder2.addRow("Dur %s" % reasonCount, "Reas %s" % reasonCount)
		tempDS = builder2.build()
		childList.append(QueryResults(tempDS, parentqr, count))
		
	parentqr.addNestedQueryResults('DowntimeReasons',childList)
		
	data['OEEDT'] = parentqr 

```

---

<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, 9:15pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/23 "2020-10-23T21:15:51Z")

</div>

> [@craigl](#):
>
> parentqr.addNestedQueryResults('DowntimeReasons',childList)

~~Indent the above line to be _inside_ the loop.~~ No, that can't be it.

---

<div class="post-metadata">

**Author:** ![WillMT10](https://avatars.discourse-cdn.com/v4/letter/w/7feea3/32.png) [@WillMT10](https://forum.inductiveautomation.com/u/WillMT10)\
**Post date:** [October 23, 2020, 9:51pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/24 "2020-10-23T21:51:18Z")

</div>

> [@craigl](#):
>
> childList =

This should be outside of your main for loop.

---

<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 24, 2020, 9:40pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/25 "2020-10-24T21:40:43Z")

</div>

Yes, that was it. Variable scoping error on my part.

Thanks!

---

<div class="post-metadata">

**Author:** ![noah.fisher](https://avatars.discourse-cdn.com/v4/letter/n/c37758/32.png) [@noah.fisher](https://forum.inductiveautomation.com/u/noah.fisher)\
**Post date:** [September 14, 2022, 9:06pm UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/26 "2022-09-14T21:06:20Z")

</div>

Thank you very much for this! I was able to build my report entirely using scripted data sources.

---

<div class="post-metadata">

**Author:** ![helge.rosvik](https://avatars.discourse-cdn.com/v4/letter/h/57b2e6/32.png) [@helge.rosvik](https://forum.inductiveautomation.com/u/helge.rosvik)\
**Post date:** [February 2, 2024, 11:41am UTC](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/27 "2024-02-02T11:41:02Z")

</div>

Since there is no example of nesting in many levels, here is my way of doing it:

`
```
def updateData(data, sample):
	from system.report import QueryResults
	trip_id = data['Selected_trip_id']
	tripQuery = '''
		SELECT 
			id, 
			start_time as "Start Time", 
			end_time as "End Time"
		FROM job
		WHERE 
			id = ?::UUID
	'''
	tripArgs = [trip_id]
	trip_ds = system.db.runPrepQuery(tripQuery, tripArgs, 'Postgres')
	trip_qr = QueryResults(trip_ds)
	
	
	site_list = []
	delivery_list = []
	for i in range(trip_ds.getRowCount()):
		trip_id = trip_ds.getValueAt(i, 'id')
		# getChildren
		# sites
		siteQuery = '''
		SELECT 
			id,
			start_time as "Start Time", 
			end_time as "End Time"
		FROM job
		WHERE
			type = 'Site'
			AND
			parent_job_id = ?::UUID
		'''
		siteArgs = [trip_id]
		site_ds = system.db.runPrepQuery(siteQuery, siteArgs, 'Postgres')
		site_qr = QueryResults(site_ds, trip_qr, i)
		
		# Add Cages
		cage_list = []
		for j in range(site_ds.getRowCount()):
			site_id = site_ds.getValueAt(j, 'id')
			cageQuery = '''
			SELECT 
				id, 
				start_time as "Start Time", 
				end_time as "End Time"
			FROM job
				WHERE
					type = 'Cage'
					AND
					parent_job_id = ?::UUID
			'''
			cageArgs = [site_id]
			cage_ds = system.db.runPrepQuery(cageQuery, cageArgs, 'Postgres')
			cage_qr = QueryResults(cage_ds, site_qr, j)
			
			# Add throws
			throw_list = []
			for k in range(cage_ds.getRowCount()):
				cage_id = cage_ds.getValueAt(k, 'id')
				throwQuery = '''
				SELECT 
					id, 
					start_time as "Start Time", 
					end_time as "End Time"
				FROM job
					WHERE
						type = 'Throw'
					AND
						parent_job_id = ?::UUID
						ORDER BY throw_no ASC
				'''
				throwArgs = [cage_id]
				throw_ds = system.db.runPrepQuery(throwQuery, throwArgs, 'Postgres')
				throw_qr = QueryResults(throw_ds, cage_qr, k)
				throw_list.append(throw_qr)
			
			#Add throws query result to parent cage query result 
			cage_qr.addNestedQueryResults('throws',throw_list)			
			cage_list.append(cage_qr)
			
		#add child qr to parent qr
		site_qr.addNestedQueryResults('cages',cage_list)
		site_list.append(site_qr) 

		# deliveries
		deliveryQuery = '''
		SELECT 
			id,
			start_time as "Start Time", 
			end_time as "End Time"
		FROM job
			WHERE
				type = 'Delivery'
				AND
				parent_job_id = ?::UUID
		'''
		deliveryArgs = [trip_id]
		delivery_ds = system.db.runPrepQuery(deliveryQuery, deliveryArgs, 'Postgres')
		delivery_qr = QueryResults(delivery_ds, throw_qr, i)
		
		#Add offloading and CIP as delivery child
		offloading_list = []
		cip_list = []
		for l in range(delivery_ds.getRowCount()):
			delivery_id = delivery_ds.getValueAt(l, 'id')
			# Offloading
			offloadingQuery = '''
			SELECT 
				id,
				start_time as "Start Time", 
				end_time as "End Time"
			FROM job
				WHERE
					type = 'Offloading'
				AND
					parent_job_id = ?::UUID
			'''
			offloadingArgs	= [delivery_id]
			offloading_ds = system.db.runPrepQuery(deliveryQuery, deliveryArgs, 'Postgres')
			offloading_qr = QueryResults(offloading_ds, delivery_qr, l)
			offloading_list.append(offloading_qr)
			
			# CIP
			cipQuery = '''
			SELECT 
				id,
				start_time as "Start Time", 
				end_time as "End Time"
			FROM job
				WHERE
					type = 'CIP Cleaning'
					AND
					parent_job_id = ?::UUID
			'''
			cipArgs	= [delivery_id]
			cip_ds = system.db.runPrepQuery(cipQuery, cipArgs, 'Postgres')
			cip_qr	= QueryResults(cip_ds, delivery_qr, l)
			cip_list.append(cip_qr)
		
		delivery_qr.addNestedQueryResults('offloading', offloading_list)
		delivery_qr.addNestedQueryResults('cip', cip_list)
		delivery_list.append(delivery_qr)
		
				
	# Add nested query result to parent
	trip_qr.addNestedQueryResults('sites',site_list)
	trip_qr.addNestedQueryResults('deliveries',delivery_list)
	
	data['trip'] = trip_qr

```
`

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