Ignition reporting module - dynamic SQL query

Ignition
1

Ignition 8.1.23

I have a table with a series of measurements at different dates. This is in wide format with a "row" for each date and "columns" for each measurement. This data is inserted into SQL by parsing a text file created by a CNC machine. It is not tag historian data.

I have written a simple script that queries the SQL table and returns a dataset. The dataset is set up as a parameter in the report and I can plot it on a time series chart. However, to do so, I have to manually type in the range key names. This will not work as a solution because the name of each measurement is dynamic as is the number of measurements.

Essentially, I have a dataset input with variable number of keys and variable key names.

Please can someone suggest a better way to achieve my desired result?

Cheers

2

I have the exact same issue. Does anyone have a solution?

3

Reporting can't do variable numbers of keys or names of keys. However, it can hide unwanted table columns or chart pens, and use alternate labels for any particular key. The general solution is to create the report using a generic keys, like t_stamp, col1, col2, ... , col16. Then use a script data source to produce a datasource to supply the desired labels for col1.... and to run a custom query that yields the generic columns names in the output.

(Yes, this is a bit tedious, and you have to know the maximum number of possible columns ahead of time.)

1 Like