# Report Script Using Nested Query

**URL:** <https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362>\
**Category:** Ignition\
**Created:** [March 14, 2016, 9:40pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362 "2016-03-14T21:40:14Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![bfearnow](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfearnow/32/3337_2.png) [@bfearnow](https://forum.inductiveautomation.com/u/bfearnow)\
**Post date:** [March 14, 2016, 9:40pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/1 "2016-03-14T21:40:14Z")

</div>

Is it possible to reference a nested query on a script? If so what is the proper syntax.

Here is why:

I have a query that gets the names of all the production lines and a nested tag historian which gets tag history for each line (1 column is a run status, 1 column is speed)

I only want to average the speed where the run status bit is high. Make sense? Thanks!

---

<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:** [March 15, 2016, 4:00pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/2 "2016-03-15T16:00:21Z")

</div>

You should be able to access any\* previously defined parameter or column in a script. Just make sure your script is below the nested query in your datasource list on the left sidebar.

- This assumes you don’t have any duplicate names. The parser starts working its way up the list and stops at the first match it finds.

---

<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:** [March 15, 2016, 4:09pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/3 "2016-03-15T16:09:30Z")

</div>

Also, you were looking for an example.

If you go to the [Data Sources - Scripting](https://docs.inductiveautomation.com:8443/display/DOC/Data+Sources+-+Scripting) documentation and expand the Script Datasource example, you can see how to access a query called “Area Data”

---

<div class="post-metadata">

**Author:** ![bfearnow](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfearnow/32/3337_2.png) [@bfearnow](https://forum.inductiveautomation.com/u/bfearnow)\
**Post date:** [March 15, 2016, 4:13pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/4 "2016-03-15T16:13:26Z")

</div>

Kathy, thank you for your response. That is the procedure I am following.

My nested query is set up as such

-Lines To Report  
-Tag History

My script only has test=data[‘Tag History’].getCoreResults() in it right now and the following message comes up:

‘NoneType’ object has no attribute ‘getCoreResults’

Thanks!

---

<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:** [March 15, 2016, 4:32pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/5 "2016-03-15T16:32:49Z")

</div>

I’m working on adding an example to the docs for the nested portion. You’ll use getNestedQueryResults(), which returns a map. I’ll let you know as soon as I have that up there.

---

<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:** [March 15, 2016, 5:10pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/6 "2016-03-15T17:10:59Z")

</div>

I need to clean this up and make it a much better documentation example, but this should give you an idea. I have a parent query called ‘query’ and a nested query called ‘sub\_query’. sub\_query has columns called ‘propname’ and ‘intvalue’

```python
nested = data['query'].getNestedQueryResults()	# Gets results from our parent query
subQuery = nested['sub_query']	#Gets the subquery we want -- there can be more than one

header = ['propname', 'intValue', 'Triple']
filteredDataset = []

for child in subQuery:
	i = i + 1
	children = child.getCoreResults()	# children is a dataset
	data['child'+str(i)] = children
	for row in range(children.rowCount):
		valPropname = children.getValueAt(row,'propname')
		valintValue = children.getValueAt(row,'intvalue')
		valTimesThree = None
		if valintValue != None:
		    valTimesThree = 3 * valintValue
		filteredDataset.append([valPropname,valintValue,valTimesThree])
		    
		    
#convert the pydataset to a standard dataset
filteredDataset = system.dataset.toDataSet(header, filteredDataset)
		     
#create a new data source with the filtered results
data['an updated Area Data'] = filteredDataset

```

---

<div class="post-metadata">

**Author:** ![bfearnow](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfearnow/32/3337_2.png) [@bfearnow](https://forum.inductiveautomation.com/u/bfearnow)\
**Post date:** [March 15, 2016, 5:57pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/7 "2016-03-15T17:57:26Z")

</div>

Thank you very…VERY much!

---

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [August 28, 2017, 3:34pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/9 "2017-08-28T15:34:17Z")

</div>

I would like to alter data by scripting from a subquery. Is it possible ?

---

<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 28, 2017, 3:55pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/10 "2017-08-28T15:55:20Z")

</div>

Yes. If I understand your question. It’s a bit tricky, though. See [this topic](http://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801/2).

---

<div class="post-metadata">

**Author:** ![mazeyrat](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mazeyrat/32/10955_2.png) [@mazeyrat](https://forum.inductiveautomation.com/u/mazeyrat)\
**Post date:** [August 28, 2017, 4:34pm UTC](https://forum.inductiveautomation.com/t/report-script-using-nested-query/11362/11 "2017-08-28T16:34:07Z")

</div>

Good shot @pturmel exactly what I seeked !