Not sure what’s going wrong. Consider using The DatasetBuilder class to construct your datasets with explicit data types. system.dataset.toDataSet implicitly sets types with the first row.
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.
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
Indent the above line to be inside the loop. No, that can't be it.
This should be outside of your main for loop.
2 Likes
Yes, that was it. Variable scoping error on my part.
Thanks!
Thank you very much for this! I was able to build my report entirely using scripted data sources.
1 Like
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
2 Likes