SQL Function as a Data Source

<< Click to Display Table of Contents >>

SQL Function as a Data Source

The Live Date Series Data report may be used to chart Live data together with non-Live data in the same Time Series Chart widget. However, the Time Series Chart widget cannot handle a lot of data, and it cannot perform calculations/post-processing on the data.

 

The Live Data Deriver and Data Computation agents can perform calculations/post-processing, and the calculated/post-processed data can then be charted, but they add data to DT_LOGGER_DATUM.

 

An alternative is to use the Live Historical Chart widget, with a table-valued SQL function as the data source. This allows non-Live data to be charted together with Live data, as well as calculations/post-processing to be performed on Live data before it is charted, without adding the data to DT_LOGGER_DATUM.

 

How to Create a Series with an SQL Function as a Data Source

 

1.Create a new DT_LOGGER_SERIES row ('series'), e.g, using EQuIS Professional.

2.Set series_function for this new series to: sql_fn.

3.Set series_function_info for this new series to the name of the function that returns the data, e.g., equis.fn_get_measure_datum, or equis.Live_Pa_mH2O.

 

The SQL function (in Step 3) must accept the following two parameters:

1.INT – The LOGGER_SERIES_ID of the series that is added to the Historical Chart widget (i.e., the series that is created in Step 1).

2.DATETIME – The Start Date that is selected in the Historical Chart widget, when adding the series.

 

The SQL function must also return a TABLE with two columns: DATUM_UTC_DT (DATETIME; UTC), and DATUM_VALUE (REAL), sorted ascending by DATUM_UTC_DT.

 

Note: The Historical Chart widget's default Start Date for "SQL function" series is set to three months ago. The Start Date may need to be set manually to display the desired date range/data.

 

Examples

1.Charting Non-Live Data

2.Charting Calculated Live Data