# What is the proper way to format query data for Report Tables? **URL:** \ **Category:** Ignition\ **Tags:** reporting\ **Created:** [August 28, 2025, 7:22pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251 "2025-08-28T19:22:37Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\ **Post date:** [August 28, 2025, 7:22pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/1 "2025-08-28T19:22:37Z")
I have an SQL query, it grabs all the daily test data from all the test lines for a date. The query works and grabs all the data I need, I can even get it on a Report in chronlogical order. shiftdate, time\_produced, TLC, BLC, linecode, InitialWgt1, InitialWgt2, EndWgt1, EndWgt2, AvgWgt1, AvgWgt2 I’m not pulling from multiple databases, all the data I need is there from one query. I need to take the data and split the data from the selected date into reports grouped by TLC, then split the TLC into pages for each different item and have them in pdf or printable format This would be an example of the data grabbed from the database: All the reports would be for the selected date. `Date 8/28/25` There would be a report just for this TLC but split up into pages for each subItem. So 2 pages. ` TLC: G5BE013A` ` ITEM:1` ` SUBITEM:A` ` time_produced` ` WGT1:60` ` WGT2:80` ` WGT3:95` ` WGT1_INSPEC:0` ` WGT2_INSPEC:1` ` WGT3_INSPEC:1` ` ...` ` ITEM:1` ` SUBITEM:B` ` time_produced` ` WGT1:143` ` WGT2:216` ` WGT1_INSPEC:1` ` WGT2_INSPEC:1` ` ...` ` ITEM:2` ` time_produced` ` WGT1:83` ` WGT2:116` ` WGT1_INSPEC:1` ` WGT2_INSPEC:1` `...` It would look like this but separated by TLC, then by item. Right now they’re all listed one after another. The “Time Produced” 2350 enteries are sub items from one item. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/5/b5b6e72813a6b39769f390ea5a885bf91c0b45e0.png) I’ve watched the videos on [Table Header, Details, and Summary Rows Video at Inductive University](https://inductiveuniversity.com/videos/table-header-details-and-summary-rows/7.9) It feels like maybe Dataset Grouping or Table Grouping - Child Table videos were the closest to what I need but all my data is already grouped into one dataset. This seems like it should be relatively easy and I’m missing something if I could get some help. Thanks. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [August 28, 2025, 8:13pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/2 "2025-08-28T20:13:22Z")
I threw this together to get you started. It may not be exactly what you want, but again , just to get you started. Using a scripted data source in the report: ```python headers = [ 'shiftdate', 'time_produced', 'TLC', 'BLC', 'linecode', 'InitialWgt1', 'EndWgt1', 'AvgWgt1' ] rows = [ ['8/28/2025','04:00:00','G5BE013A','1','A',60,80,70], ['8/28/2025','04:15:00','G5BE013A','1','B',140,160,150], ['8/28/2025','04:30:00','G5BE013A','2','A',140,160,150] ] data['dataIn'] = system.dataset.toDataSet(headers, rows) ``` Here is the page layout ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/6/9658acacbb0d541d96f205e2d5862acd004627cb.png) grouping below, dataIn has `Header` and `Details` the rest just `Details` checked. For the BLC grouping, `Page Break` is selected. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/f/9fb3ef83a992d898f3b86aaf5794a107afb88dcf.png) Result: page 1 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/d/adedefab5c3825661bf730f2c4f472409713018e.png) page 2 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/b/bb071308a03b8e9afcb7db44604d821fbc203cdf.png) ---
**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\ **Post date:** [August 28, 2025, 8:40pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/3 "2025-08-28T20:40:05Z")
Wow thank you for the great response. I’ll try it out! ---
**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\ **Post date:** [September 1, 2025, 8:55pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/4 "2025-09-01T20:55:30Z")
Thanks so much. I was able to get the data displaying how I wanted. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/6/46eb3facb046c5161412f69765ef0f0980b667b7.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/c/0c929ef8a1ef80e25e98cba97e4df8c4912834f4.png) I’m trying to add a graph at the bottom using the @AvgPct@ from my list, but it’s showing two data points for each X 1-19 on the chart. One is for item1 (Mushrooms) and the other is for item2 (Onions). I have the chart in the AllItems Summary section of the table. I’m using SampleNum which has the correct number of samples taken, 19 as my Domain Key. AvgPct is the number shown on the right column of the table directly above the chart. AllItems is my only data source. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/a/7a35e8dc9943e72ccec969ed97b8732e1108c1c3.png) ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 2, 2025, 12:18pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/5 "2025-09-02T12:18:25Z")
In my experience, I always have a separate data source for charts to make sure it gets the exact data and format it needs to render the way I want. Review the dataset structuring needed for the chart in the user manual carefully. ---
**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\ **Post date:** [September 2, 2025, 12:38pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/6 "2025-09-02T12:38:27Z")
I already have a second data source but I’ve having trouble getting just the data for the current page. I thought I would have been able to grab data from the first datasource for a filter value for the next. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/c/2cd2c9e706b8a47714ced7908793339fef34f6fa.png) I can test it by manually entering a string into the selected\_item value. But I can’t get data from the First Datasource. I have the chart in the Summary row and I added @Item@ as a test to that row and I can see that. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/0/4049d7a262dc745e0ea3f16b70d55f5445ead2c0.png) The Chart shown is using a string ‘Mushrooms’ right now as I can’t figure out how to get @Item@ in the chart. Is there a way to use the first Datasource “AllItems” values in the second? @Item@ and @AllItems.Items@ doesn’t work, which is all I would need for this second query. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 2, 2025, 1:19pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/7 "2025-09-02T13:19:29Z")
I don’t think you can filter the data source this way. I think you’ll need to remove the `ChartData` source and add it as a nested query to `AllItems`. What this will do is run the ChartData query once per key in the parent query, which should do the filtering that you want. [Nested Queries | Ignition User Manual](https://docs.inductiveautomation.com/docs/8.1/ignition-modules/reporting/report-data/nested-queries#downtime_table) ---
**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\ **Post date:** [September 2, 2025, 1:21pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/8 "2025-09-02T13:21:43Z")
Alright, I’ll try that. Thank you. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 2, 2025, 1:27pm UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/9 "2025-09-02T13:27:24Z")
If there are DB performance reasons to collect all the chart data in one query, consider constructing the required nesting in a script data source. Like so: > [@Report with Scripted Nested Data](https://forum.inductiveautomation.com/t/report-with-scripted-nested-data/11801): > > The report datasource scripting examples explain how to access the nested data in another key for use constructing a custom dataset. What I don’t see is how to construct nested data that would be suitable for use with a child table in a table group. I have a parent dataset that I’m constructing via script due to annoying limitations of an ancient SQL Server 2000 instance. I need a subquery for each row that could be done with a normal query, but there’s no way to add a regular subquery to a s… ---
**Author:** ![Kyle\_Latzke](https://avatars.discourse-cdn.com/v4/letter/k/c68b51/32.png) [@Kyle\_Latzke](https://forum.inductiveautomation.com/u/Kyle_Latzke)\ **Post date:** [September 3, 2025, 10:41am UTC](https://forum.inductiveautomation.com/t/what-is-the-proper-way-to-format-query-data-for-report-tables/110251/10 "2025-09-03T10:41:11Z")
There’s no reason it has to be collected all in one query. I just did one query and grabbed all the data did null checking and percentage calculating all in the SQL query. I guess I don’t know a better way. I guess I’m still trying to understand how everything is linked in the report.