Report Script Using Nested Query

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!

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.

Also, you were looking for an example.

If you go to the Data Sources - Scripting documentation and expand the Script Datasource example, you can see how to access a query called “Area Data”

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’


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.

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’

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
#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

Thank you very…VERY much!

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

Yes. If I understand your question. It’s a bit tricky, though. See this topic.

1 Like

Good shot @pturmel exactly what I seeked !