As nice as some things are in the reporting, I’ve been struggling with getting some important things accomplished to finish a single report.
We have a report where we are using a single data source that first uses a SQL query to get out each Oil Well that has a gas meter device installed. This is to generate a report that has a each well on it’s own page. Then we nested a couple of Tag Historian queries to generate both a time weighted and max aggregate with an interval of 24 hours (makes the report have a month’s worth of data (a day on each row).
How do you get two different Tag Historian queries (data keys) in one tables details section? We have a Table group already for different sections of data, but I want to display the max and time weighted average on the same table next to each other (see second picture below, column “Flow Time”). That column will display only the first well’s result and repeat the same for every well. Disregard the two columns with “N/A” since those I left out.
**I was able to do this in a inefficient fashion by putting a child nested query to an existing nested query, but this generates the extra query every time for each query row in the query it’s under, so it basically compounds unnecessarily, producing thousands of extra rows of data as seen in the xml in preview.
Second question, I found out from AI support that there’s a bug since version 7.9 that nested queries don’t display “calculations” and am unable to do total, average, max, min, etc… (Note, we first used a sql query to narrow down the wells that we need, then used tag historian queries with aggregates). The parent query will display calculations, but not the nested. Is there anyway around this? We have gotten into scripting a data source, and were able to get these calculations of columns, but since it’s a separate data source, we are unable to get it to tie in and group nicely with the original data source. Also, seems like it would have been nice to put a script query option under the nested queries.
I think I figured out my second question, so for the first, I went through your post and thought this may work, but I don’t think it will for what we need, unless I’m doing something wrong. What I need is two different tag historian aggregate queries on the same table, but can’t get it to populate correctly.
The below screen shot mixes two different aggregates that need to come from two queries that are nested from the parent query which gets one row per site.
https:/uploads/iatesting/original/2X/e/e3f749bcc9f9fd64768c4b0d42f27bb1894ec347.png
Ugh, sounds complicated. I know how to do the aggregates in a script (queryTagHistory), but don’t know how to construct this into a report’s nested query to use in my Table Groups. We have a parent query that is a SQL query to get the sites we need, then use two nested queries (one for aggregation, and another to grab other tag information. I then use a table grouping with three tables (each one section) from top to bottom. This works with the built in methods if we just need one aggregate.
I'm looking through and trying to build a script based on what you have in your post you linked.
Could you update the format from your original post so it's more readable? Also, for the data key "ZZ" you had to create to go above the script, did you do a Basic Query, what did you put in as a query? Thanks
I used what you have with the below code and my Parent Key and Child Key’s XML data is empty (But the column names show as keys under Parent Key but no data. What am I missing? I created a basic query called ‘ZZ’ and it’s on top (I see now that Ignition might now support from system.report import QueryResults?)
I’m putting in the correct runPrepQuery syntax. If I use the SQL Query data source with the same query, it returns what I need. But what I’m trying to do is build it all in the script, so I can add multiple queryTagHistory functions to a single child key.
#from system.report import QueryResults
QueryResults = data['ZZ'].getClass()
# Build and reprocess parent data
pyds = system.db.runPrepQuery("SELECT SiteName, Meter_isInstalled, DBNum, Serial_Number FROM Config_db WHERE Meter_isInstalled = 1")
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("SELECT * FROM meter_analysis")# WHERE gas_analysis.gas_analysis_ndx = 3")#, [dbnum])
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
You are failing to append newRow to newRows in your “reprocessing” loop. If you don’t need to reprocess the parent query, don’t. Just construct parentqr from pyds.underlyingDataset.
I was able to get these parts working for the parent and one child nested query. One last thing I have left is performing a queryTagHistory function to get my last nested query. I’m unable to get my code to work. Looking at the third section in my code, can someone help correct what I’m doing wrong?
(**Notice some commented out code as I was trying different things, I’m getting errors relating to in the last part of dataList unable to coerce to Queryresults or object doesn’t have the append attribute.)
# Obtain the QueryResults class for our own use
from system.report import QueryResults
# Build and reprocess parent data
pyds = system.db.runPrepQuery("SELECT WellName, Meter_Installed, DBNumber, Serial_Number FROM Config_db WHERE Meter_Installed = 1")
heads = list(pyds.underlyingDataset.columnNames)
newRows = []
for row in pyds:
newRow = [x for x in row]
newRows.append(newRow)
parentds = system.dataset.toDataSet(heads, newRows)
parentqr = QueryResults(parentds)
#=====================================================================
# Build child gas analysis query dataset
gasConfigList = []
for r in range(parentds.rowCount):
dbnum = parentds.getValueAt(r, 'DBNumber')
query = "SELECT * FROM gas_analysis WHERE gas_analysis_ndx = ?"
childpyds = system.db.runPrepQuery(query, [dbnum], 'TESTTEST')
gasConfigList.append(QueryResults(system.dataset.toDataSet(childpyds), parentqr, r))
#Attach list of children to parent
parentqr.addNestedQueryResults('Gas Meter Config', gasConfigList)
#=====================================================================
# Build child tag historian query dataset
endTime = data['EndDate']
StartTime = data['StartDate']
header = ['time', 'avg1']
dataList = []
for r in range(parentds.rowCount):
dbnum = str(parentds.getValueAt(r, 'DBNumber'))
path1 = ['[DB_Trends]wells/well'+dbnum+'/meter/gas sales/_dp']
rawDataset1Avg = system.tag.queryTagHistory(paths= path1, startDate=StartTime, endDate=endTime, aggregationMode="Average", intervalHours = 24)
#rawPyds = system.dataset.toPyDataSet(rawDataset1Avg)
for row in range(rawDataset1Avg.rowCount): #Just need to count from one rawDataset
valTime = rawDataset1Avg.getValueAt(row,0)
valavg1 = rawDataset1Avg.getValueAt(row,1)
dataList.append([valTime, valavg1])
dataList = system.dataset.toDataSet(header, dataList)
#dataList = system.dataset.toPyDataSet(dataList)
dataList.append(QueryResults(system.dataset.toDataSet(dataList), parentqr, r))
#dataList.append(QueryResults(dataList, parentqr, r))
#dataList = system.dataset.toDataSet(header, dataList)
#Attach list of children to parent
parentqr.addNestedQueryResults('Meter Data', dataList)
# Add result to the report data map
data['Well Info'] = parentqr
You are using the dataList variable for multiple purposes within your loop, smashing its content that is supposed to be accumulated from outer loop to outer loop. Also, you have an inner loop that appears to be trying to convert rawDataset1Avg to a dataset, but it is already a dataset. (That inner loop appears to be a cut and paste that carried dataList along for the ride…) Just use rawDataset1Avg directly to make your nested QueryResults.
Hint: If a variable (like dataList) is on the left-hand side of an assignment, and also mixed into the arguments on the right-hand side, with a different datatype, you might be doing something wrong.
Ah yes, I caught this just before you posted, and had to create a separate list variable within the second for loop. This fixed it and now have this working!
