# How to use Script Data Source to Access multi-level Nested Query Data in Report Module

**URL:** <https://forum.inductiveautomation.com/t/how-to-use-script-data-source-to-access-multi-level-nested-query-data-in-report-module/41077>\
**Category:** Ignition\
**Tags:** ignition79, scripting\
**Created:** [November 30, 2020, 11:28pm UTC](https://forum.inductiveautomation.com/t/how-to-use-script-data-source-to-access-multi-level-nested-query-data-in-report-module/41077 "2020-11-30T23:28:33Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![pdc5067](https://avatars.discourse-cdn.com/v4/letter/p/c6cbf5/32.png) [@pdc5067](https://forum.inductiveautomation.com/u/pdc5067)\
**Post date:** [November 30, 2020, 11:28pm UTC](https://forum.inductiveautomation.com/t/how-to-use-script-data-source-to-access-multi-level-nested-query-data-in-report-module/41077/1 "2020-11-30T23:28:33Z")

</div>

I currently have a Nested Query that contains 3 levels:

|- ParentQuery  
|-- ChildQuery  
|— SubChild Query

I first used what is found in the [Scripting Data Source Doc](https://docs.inductiveautomation.com/display/DOC79/Scripting+Data+Source#:~:text=The%20Script%20data%20source%20allows,for%20use%20in%20the%20report.) to pull 5 different datasets and combine them into 1 data source kindof like what Kathy did [here](http://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/4) .

I then added the SubChild Query to my nested query and tried tailoring my script to pull the SubChild data using the same (.getNestedQueryResults()) inside of a nested for loop. However, I feel like I am missing something here. I am not very well versed in python but I know Kathy mentioned that this function returns a ‘map’ and I was thinking this is where my knowledge is breakingdown on the subject.

I am getting an Error at line 17 “sub\_nested = children[‘NestedQuery\_Child’].getNestedQueryResults()” of:

INFO | jvm 1 | 2020/11/30 18:33:44 | com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last):  
INFO | jvm 1 | 2020/11/30 18:33:44 | File “function:updateData”, line 17, in updateData  
INFO | jvm 1 | 2020/11/30 18:33:44 | TypeError: ‘com.inductiveautomation.ignition.common.BasicDataset’ object is unsubscriptable

Below is my code to access the SubChild Query:

```auto
	#This code takes all the resultant subchildren and consolidates them into 1 dataset per child....
	nested = data['NestedQuery_Parent'].getNestedQueryResults() # Gets results from our parent query
	childQuery = nested['NestedQuery_Child'] # Gets the subquery we want -- there can be more than one
	childHeader = ['accesschildCol1']
	i=0
	for child in childQuery:
		i=i+1
		children = child.getCoreResults() # children is a dataset
		
		
		sub_nested = children['NestedQuery_Child'].getNestedQueryResults()
		sub_childQuery = sub_nested['NestedQuery_subChild']
		sub_childHeader = ['accessCol1', 'accessCol2', 'accessCol3']
		sub_alteredDataset = []
		for sub_child in sub_childQuery:
				sub_children = sub_child.getCoreResults() # children is a dataset
				for row in range(sub_children.rowCount):
					val_accessCol1 = children.getValueAt(row,'Col1')
					val_accessCol2 = children.getValueAt(row,'Col2')
					val_accessCol3 = children.getValueAt(row,'Col3')
					sub_alteredDataset.append([val_accessCol1,val_accessCol2,val_accessCol3])

		# convert the pydataset to a standard dataset
		sub_alteredDataset = system.dataset.toDataSet(sub_childheader, sub_alteredDataset)
	
		# create a new data source with the altered results
		data['Updated NestedQuery_Child'+str(i)] = sub_alteredDataset

```

---

<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:** [December 1, 2020, 12:10pm UTC](https://forum.inductiveautomation.com/t/how-to-use-script-data-source-to-access-multi-level-nested-query-data-in-report-module/41077/2 "2020-12-01T12:10:03Z")

</div>

Two things:

- I belive it should be _**getRowCount()**_ and not _ **rowCount** _

- I don’t believe you can use _**getValueAt()**_ with a column name with BasicDataset. Use _**getColumnNames()**_ to help get the column indexes.  
.  
EDIT: You _can_ use a column name in v8.0+, according to the javadocs, but I don’t think that was backported to 7.9

```python
		for sub_child in sub_childQuery:
				sub_children = sub_child.getCoreResults() # children is a dataset
				colNames = list(sub_children.getColumnNames())
				for row in range(sub_children.getRowCount()):
					val_accessCol1 = children.getValueAt(row, colNames.index('Col1'))
					val_accessCol2 = children.getValueAt(row, colNames.index('Col2'))
					val_accessCol3 = children.getValueAt(row, colNames.index('Col3'))
					sub_alteredDataset.append([val_accessCol1,val_accessCol2,val_accessCol3])

```

---

<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:** [December 1, 2020, 2:33pm UTC](https://forum.inductiveautomation.com/t/how-to-use-script-data-source-to-access-multi-level-nested-query-data-in-report-module/41077/3 "2020-12-01T14:33:30Z")

</div>

> [@JordanCClark](#):
>
> it should be _**getRowCount()**_ and not _ **rowCount** _

No, they are equivalent. Jython automatically converts methods that follow NetBeans getter/setter conventions into virtual properties. I always use `rowCount`.

`BasicDataset` has supported named columns in .getValueAt() for ages. It is the core `Dataset` interface that was upgraded in v8 to have some default methods that backport some of BasicDataset's functionality.

---

<div class="post-metadata">

**Author:** ![pdc5067](https://avatars.discourse-cdn.com/v4/letter/p/c6cbf5/32.png) [@pdc5067](https://forum.inductiveautomation.com/u/pdc5067)\
**Post date:** [December 1, 2020, 2:37pm UTC](https://forum.inductiveautomation.com/t/how-to-use-script-data-source-to-access-multi-level-nested-query-data-in-report-module/41077/4 "2020-12-01T14:37:20Z")

</div>

Thank you for the reply! That portion was taken almost directly from [Scripting Data Source](https://docs.inductiveautomation.com/display/DOC79/Scripting+Data+Source) doc and seems to work fine when I have a single nested query.

I seem to be getting hung up before that portion, at the lines containing:

```auto
sub_nested = children['NestedQuery_Child'].getNestedQueryResults()
sub_childQuery = sub_nested['NestedQuery_subChild']

```

It seems like I can get the nested data from the child layer using:

```auto
nested = data['NestedQuery_Parent'].getNestedQueryResults() # Gets results from our parent query
childQuery = nested['NestedQuery_Child']

```

but I cant seem to access the data in the subchild layer.

---

<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:** [December 1, 2020, 2:39pm UTC](https://forum.inductiveautomation.com/t/how-to-use-script-data-source-to-access-multi-level-nested-query-data-in-report-module/41077/5 "2020-12-01T14:39:58Z")

</div>

> [@pdc5067](#):
>
> but I cant seem to access the data in the subchild layer.

I don't see you calling .getNestedQueryResults() on `sub_child`.

---

<div class="post-metadata">

**Author:** ![pdc5067](https://avatars.discourse-cdn.com/v4/letter/p/c6cbf5/32.png) [@pdc5067](https://forum.inductiveautomation.com/u/pdc5067)\
**Post date:** [December 1, 2020, 4:35pm UTC](https://forum.inductiveautomation.com/t/how-to-use-script-data-source-to-access-multi-level-nested-query-data-in-report-module/41077/6 "2020-12-01T16:35:29Z")

</div>

I figured it out. I used a lot of print statements and then looked in the wrapper.log file to see the format of what was returned.

Thank you for the help!

```auto
	#Manipulated from v7.9 User Manual: Scripting Data Source: Building a New Data Source
	
	#This code takes all the resultant subchildren and consolidates them into 1 dataset per child....
	parent = data['NestedQuery_Parent']
#	print parent
	nested = data['NestedQuery_Parent'].getNestedQueryResults() # Gets results from our parent query
#	print nested
	childQuery = nested['NestedQuery_Child'] # Gets the subquery we want -- there can be more than one
#	print childQuery
	childHeader = ['accesschildChuteName']
	i=0
	for child in childQuery:
		i=i+1
# print i
# print child
		sub_nested = child.getNestedQueryResults()
# print sub_nested
		sub_childQuery = sub_nested['NestedQuery_subChild']
# print sub_childQuery

		sub_childHeader = ['accessChuteName', 'accessGoodSorts', 'accessBadSorts']
		sub_alteredDataset = []
		for sub_child in sub_childQuery:
				sub_children = sub_child.getCoreResults() # children is a dataset
# print sub_children
				for row in range(sub_children.rowCount):
					val_accessCol1 = sub_children.getValueAt(row,'ChuteName')
					val_accessCol2 = sub_children.getValueAt(row,'Good Sorts')
					val_accessCol3 = sub_children.getValueAt(row,'Bad Sorts')
					sub_alteredDataset.append([val_accessCol1,val_accessCol2,val_accessCol3])

		# convert the pydataset to a standard dataset
		sub_alteredDataset = system.dataset.toDataSet(sub_childHeader, sub_alteredDataset)
	
		# create a new data source with the altered results
		data['Updated NestedQuery_Child'+str(i)] = sub_alteredDataset

```

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/2/8241d0449a535b22b3830a5ad8ff84bba5295942.png)