# Report in nested queries **URL:** \ **Category:** Ignition\ **Created:** [July 4, 2022, 5:20pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490 "2022-07-04T17:20:16Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 4, 2022, 5:20pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/1 "2022-07-04T17:20:16Z")
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! ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [July 4, 2022, 6:25pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/2 "2022-07-04T18:25:04Z")
You’ll need to show some details of your configuration. Otherwise we’re all guessing. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [July 4, 2022, 6:39pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/3 "2022-07-04T18:39:16Z")
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? ---
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 5, 2022, 4:31am UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/4 "2022-07-05T04:31:28Z")
> [@nminchin](#): > > I assume by “UDT variable” you mean a “UDT definition”? Yes, I meant UDT Definition (sorry for the wrong terminology). > [@nminchin](#): > > Have the historized UDT instance tag values changed values? Yes, they are dynamically changing in run mode of the gateway. > [@nminchin](#): > > When you check the config of the instance tags, do they in fact have history enabled? 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! ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/6/8646fce0be81cdbc0acf3507ba26fcb2d1c09007.png) ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [July 5, 2022, 4:46am UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/5 "2022-07-05T04:46:38Z")
To query it you can use: ```python 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 ```python 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 > [@prafull\_j](#): > > here is a screenshot of database query browser. It shows only one instance! I'm not sure what you mean by one instance? ---
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 5, 2022, 5:00am UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/6 "2022-07-05T05:00:41Z")
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 ! ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/e/4e47604f0b40c23cc078936479627726dbc8567d.png) ---
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 5, 2022, 5:39am UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/7 "2022-07-05T05:39:13Z")
> [@nminchin](#): > > I’m not sure what you mean by one instance? 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! ---
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 5, 2022, 6:42am UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/8 "2022-07-05T06:42:10Z")
As you can see the values for the first instance are OK, but for the second instance onwards, it prints N/A ! ![Untitled](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/6/0616b81508de5040d9fa155411a5ddb950edb768.png) ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [July 6, 2022, 6:01am UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/9 "2022-07-06T06:01:55Z")
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 ---
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 6, 2022, 11:32am UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/10 "2022-07-06T11:32:11Z")
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! ![Untitled1](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/4/845f6195b0b1b1177f8d472344d2e300eb65a89a.png) ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [July 6, 2022, 4:29pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/11 "2022-07-06T16:29:41Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [July 6, 2022, 4:33pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/12 "2022-07-06T16:33:32Z")
> [@prafull\_j](#): > > 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! This is almost certainly a table configuration error, not an issue with the data. I would contact support. ---
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 6, 2022, 4:55pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/13 "2022-07-06T16:55:22Z")
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! ---
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 6, 2022, 4:59pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/14 "2022-07-06T16:59:15Z")
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. ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [July 6, 2022, 5:00pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/15 "2022-07-06T17:00:27Z")
> [@prafull\_j](#): > > I do have details rows for the parent query, but not the header row (because I don’t really need it). I have delivered (not officially endorsed) training to other Integrators. IME, **it doesn't work without the header on**. Sending PM with screenshots now. ---
**Author:** ![PRAMANJ](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pramanj/32/66935_2.png) [@PRAMANJ](https://forum.inductiveautomation.com/u/PRAMANJ)\ **Post date:** [July 6, 2022, 5:48pm UTC](https://forum.inductiveautomation.com/t/report-in-nested-queries/61490/16 "2022-07-06T17:48:07Z")
I am using tag history query like in [Tag Historian Query - Report Data (inductiveuniversity.com)](https://inductiveuniversity.com/videos/tag-historian-query/8.1)