I have a named query I’m running and it’s interpreting some data from my database (mostly totals). I’m trying to calculate a percentage based off of those totals and I haven’t been able to do it in the query itself. Is there a way I can calculate this percentage using scripting and have it show up in my report? If so, how do I reference my query data? I want to calculate a percentage of Good Parts to Total Parts.
I’m surprised you can’t do that calculation in the query, but never fear, you can use a script datasource.
https://docs.inductiveautomation.com/display/DOC80/Scripting+Data+Source
Just make sure it’s lower down in the list than your query datasource and it will have access to the results of the query.
@KathyApplebaum Thank you for your answer. I’m wondering if you could maybe share an example of how I reference a specific column or row of data from the query? I’m new to programming and sometimes struggle with the proper syntax.
Also, my script doesn’t show up as a data source in the report. (I can’t insert my script as a data source in a text box)
The linked docs have examples towards the bottom of the page.
Another option that may be simpler than a script datasource in this limited case would be a keychain expression: https://docs.inductiveautomation.com/display/DOC79/Keychain+Expressions
Something like @TotalParts/GoodParts@ - filling in the correct data keys as needed.
Where do I implement these expressions? In the data tab of the Reporting Module as a script? Or a parameter?
Keychain Expressions are used in the Design tab of the Reporting module, so you would write the expression on a Text Shape/cell of your table.
That worked Perfectly! Thank you @paul-griffith and @Paul.Scott! It might be something worth adding to the Ignition manual that the Keychain functions are implemented in the design tab of the Reporting Module.
Thank you all for your help!
Can you not calculate a percentage of a column value based off its own total?
I keep getting <N/A> results in preview mode when trying to do the following:
I would like to have the % column show the percent of each row relative to the total.
so for that last row, the value of 7 would correspond to 50% in the second column.
Did you ever get anywhere on this? I am having the exact same problem, and this is the only thread I could find about it.
