Data displayed null value while run sql query for the report format

Ignition
1

Hii All,

Faced issue while preview of report data,it's showing null value.

Configured a SQL query for the report format. This query for the calculate total machine working hours during 24 hour cycle.

Problem: As per mentioned below query for fetch total hours without defining the parameter. (Like a StartDate, EndDate) The data is displayed in the output window.

image
image1322×827 160 KB

Configure a SQL query as per below.

SELECT Min(sqlt_data_2_2024_10.intvalue) AS Min_intvalue,
  Max(sqlt_data_2_2024_10.intvalue) AS Max_intvalue,
  Max(sqlt_data_2_2024_10.intvalue) - Min(sqlt_data_2_2024_10.intvalue) AS
  Total_Hour
FROM sqlt_data_2_2024_10
WHERE sqlt_data_2_2024_10.tagid = 889

image
image1037×853 38.9 KB

Defined the query data.

image
image1430×908 76.8 KB

But, when defined a StartDate and EndDate parameter, displayed data a null value.

SELECT Min(sqlt_data_2_2024_10.intvalue) AS Min_intvalue,
  Max(sqlt_data_2_2024_10.intvalue) AS Max_intvalue,
  Max(sqlt_data_2_2024_10.intvalue) - Min(sqlt_data_2_2024_10.intvalue) AS
  Total_Hour
FROM sqlt_data_2_2024_10
WHERE sqlt_data_2_2024_10.tagid = 889 AND t_stamp BETWEEN ? AND ?

image
image1320×827 160 KB

2

You are directly querying the historian's SQL tables. Don't do this. While their current architecture is documented, it is not guaranteed to not change and is unsupported. Use system.tag.queryTagHistory(). It will handle datetime conversions for you and will union data partitions as needed.

3

system.tag.queryTagHistory()
How implement above query in a report format?

Can you give example for better understanding?

4

In a report, use a tag historian data source--it takes the same arguments that the scripting function expects.

1 Like
5

Thank you for reply,

Yes, it worked, implemented a tag calculation query to count the various aggregate functions.