Report with Scripted Nested Data

pturmel · June 10, 2016, 4:39pm

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?

pturmel · June 13, 2016, 3:36pm

So, I figured this out on my own with a lot of help from inspect.py, which showed me this:

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):

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:

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

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

carlos.poncedeleon · August 2, 2016, 9:01pm

Very helpful. Thank you!

pturmel · August 2, 2016, 11:07pm

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.

KathyApplebaum · August 3, 2016, 1:23pm

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

Trent.Boudreaux · May 25, 2017, 3:29pm

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.

pturmel · September 29, 2017, 2:35pm

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:

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

Then anyone’s scripts could do:

from system.report import QueryResults

Pretty please?

KathyApplebaum · September 29, 2017, 3:56pm

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

jake.richard · October 17, 2017, 4:12pm

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.

rudi.putzeys · November 26, 2018, 10:31am

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)

I tried this:

 	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

pturmel · November 26, 2018, 2:07pm

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.

rudi.putzeys · November 26, 2018, 3:12pm

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:

In preview mode:

So far, so good…

JordanCClark · July 28, 2020, 1:31pm

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.

joe.hammond · October 22, 2020, 8:06pm

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!

pturmel · October 22, 2020, 8:30pm

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.

PGriffith · October 22, 2020, 9:10pm

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.

craigl · October 23, 2020, 5:16pm

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.

pturmel · October 23, 2020, 5:20pm

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.

craigl · October 23, 2020, 5:27pm

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

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:

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)

pturmel · October 23, 2020, 5:36pm

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