I am ultimately trying to add a dynamic goal line to my report chart. Currently trying to reference a data source in the configurechart script. I have tried to access the data two ways. It is in a nested query that will change from chart to chart in the report. Any ideas?
This is another try but I get the following error.
AttributeError: ‘com.inductiveautomation.ignition.common.BasicDatas’ object has no attribute ‘getNestedQueryResults’
Are you sure that Additives is a nested dataset? The error is saying it is a basic dataset type. I would expect a parent dataset of a nested query to return a type of QueryResults.
This is assuming your error is being thrown at line 11 and you don’t have a getNestedQueryResults() call somewhere else in your script that we can’t see.
It looks like you are close, but I think you don’t need any methods to get the data. I played with the nested queries a bit, and you can’t get the nested query results from the chart script (or at least I couldn’t). It seems that once the data source is bound to the chart, it doesn’t contain the subqueries any more.
Your last attempt is probably what you need but as a scripted datasource, once you get the goal set it as its own data source…data['goal'] = goal
Then in your chart script get the goal value by
goal = data['goal']
Side note, I’ve found that I was able to get rid of all the nested queries I was using before by using table joins and subqueries with in a single query. It proved to be much faster and easier this way.
A general rule of thumb for SQL is that well indexed tables will do JOINs faster than subqueries. Most of the time too subqueries can be written as joins, sub queries should only be used in the event that it cannot be done with JOINs.
Usually, the only time that I use subqueries is in complex calculations where I need to use one item I have selected as a variable for the rest of the query, but not so complex that I need to write a stored procedure.
If you use @var:=<selection calc> (mysql/mariaDB syntax) in the select, then also try to select max(@var) as max, it won’t work, because there is no way to be sure that @var will be assigned before max(@var). But if you use a subquery to put the calculations in, you can select from it without worry of execution order.
Additives is the primary query. I am using Report Script Using Nested Query - #4 by bfearnow as an example.