I have enabled history for one of the tags in a UDT definitions and expect it to reflect in all its instances to be historized. However, tag history query using indirect {} tag I am getting NULL value for all tags except the one corresponding to the first instance of UDT in the report preview. I don’t know how history is stored in DB for different tags!
You’ll need to show some details of your configuration. Otherwise we’re all guessing.
I assume by “UDT variable” you mean a “UDT definition”? (correct terminology, as is used in the designer, is important to know exactly what you’re talking about)
Have the historised UDT instance tag values changed values? When you check the config of the instance tags, do they in fact have history enabled?
Yes, I meant UDT Definition (sorry for the wrong terminology).
Yes, they are dynamically changing in run mode of the gateway.
Yes, they show "History Enabled" and a clock ICON in each instance of the UDT for the individual tag on which history is enabled.
I wonder where these values are stored in DB. here is a screenshot of database query browser. It shows only one instance! Hope I am looking at the right table!
To query it you can use:
SELECT
t.tagpath, d.*
FROM
sqlt_data_1_2022_07 d
INNER JOIN sqlth_te t on t.id = d.tagid
WHERE
t.tagpath = '{tag path to your tag}'
or
SELECT
*
FROM
sqlt_data_1_2022_07
WHERE tagid IN (
SELECT
id
FROM
sqlth_te
WHERE
tagpath = '{tag path}'
)
The 2nd one I think is faster if you're wanting to use a LIKE in the where clause
I'm not sure what you mean by one instance?
I am enclosing the screenshot of the query browser, masking some of the fields. As you can see the variable flowrate repeats in all instances and the tagpath I have used is [DB]testdb/udtinst {udtinst_number}/flowrate. I am using a nested query which generates the udtinst and udtinst_number parameter for each instance of the udt. The flowrate for the first udt instance works fine , for the subsequent ones it reurns NULL !
My tag history query works as a nested query under a top query which loops on each instance of the UDT returning the UDT Instance number which is used by the tag history query nested inside it. I am using tag path as "[DB]testdb/udtinst {udtinst_number}/flowrate" and Data Key Alias as "flowrate {udtinst_number}" in the query as shown in above screen shot.
I get correct values of flowrate for the first instance of UDT whereas for the rest it returns NULL ! The top query is returning the udt instance number correctly , I don't know why the dynamic tag path is not returning the values for other flowrate variable for rest of the instances!
I am using this query in the Reports module of the designer to generate nested tabular and trend reports for each UDT Instance.
The trend is generated properly for the first instance of UDT but for the rest its a blank trend!
As you can see the values for the first instance are OK, but for the second instance onwards, it prints N/A !
I haven’t used nested queries apart from in the gold cert so I’m not too sure. Might be better to change your topic title to something more relevant like “Report nested query not working as expected” or something
You are right, I have done the change of topic name. Also, I observe that the nested query itself for the tag history query works fine as evident in the XML on the right side of the report preview in designer. However, the trends or table corresponding to the nested tag history query shows up only for the first UDT Instance and NULL for rest of them! don’t know what going wrong!
In my experience:
Use a table, ensure the header is on as well as details for rows. Summary is optional.
Place your chart in the table. The data key of the table needs to be the parent. The data key of the chart comes from the child queries.
I can PM you screenshots later, can’t share them here.
This is almost certainly a table configuration error, not an issue with the data. I would contact support.
I do have details rows for the parent query, but not the header row (because I don’t really need it). Nested inside the details row (configured as unstructured row) is my chart for which the data key is the tag_hist_query which is a child query to the parent query. I have created a table group on the page 1 and added another table showing some other data under the parent query which works fine. I tried adding the tag_hist_query results as another peer table to it in the table group (instead of chart in the details row of the parent table), even there it shows NULL for UDT’s 2 onwards! (as shown in last but one image above!
I am sure I am making a small mistake somewhere in configuring table groups or linking tag_hist_query results with charts dynamically! I am somewhere close but not getting it right! Let me explore further.
I have delivered (not officially endorsed) training to other Integrators. IME, it doesn't work without the header on. Sending PM with screenshots now.