# Report Design **URL:** \ **Category:** Ignition\ **Created:** [April 12, 2012, 9:40pm UTC](https://forum.inductiveautomation.com/t/report-design/4284 "2012-04-12T21:40:55Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![jamess](https://avatars.discourse-cdn.com/v4/letter/j/87869e/32.png) [@jamess](https://forum.inductiveautomation.com/u/jamess)\ **Post date:** [April 12, 2012, 9:40pm UTC](https://forum.inductiveautomation.com/t/report-design/4284/1 "2012-04-12T21:40:55Z")
We are trying to design a report using data from a relational database. Table 1 contains a Work\_Order\_ID. Table 2 contains all Codes associated with the Work\_Order\_ID. Table 3 contains all Technicians associated with the Work\_Order\_ID. Table 4 contains all Parts associated with the Work\_Order\_ID. In MSAccess (or other reporting tools we’ve used) we would create a report with the Work\_Order\_ID and any other information needed from Table 1. Below that, there would be a SUB-REPORT containing the Codes and other Table 2 data, which would be linked to the correct Work\_Order\_ID, usually via pkey. Below that, there would be another subreport listing all of the related Table 3 data, such as Technicians, and so on. The reporting tool in Ignition does not have anything specifically called “subreport,” but given the power of the rest of the product, I would be very surprised if there isn’t something that performs the same function. Can anyone point me in the right direction? Thanks, James ---
**Author:** ![markdobtech](https://avatars.discourse-cdn.com/v4/letter/m/71e660/32.png) [@markdobtech](https://forum.inductiveautomation.com/u/markdobtech)\ **Post date:** [April 13, 2012, 2:53pm UTC](https://forum.inductiveautomation.com/t/report-design/4284/2 "2012-04-13T14:53:14Z")
You will need joins, groups, and depending on the schema, the sql statement for the retrieve may also require subselects. Google any of these topics and you will get some good examples to start from. ---
**Author:** ![ecujak](https://avatars.discourse-cdn.com/v4/letter/e/839c29/32.png) [@ecujak](https://forum.inductiveautomation.com/u/ecujak)\ **Post date:** [April 17, 2012, 7:50pm UTC](https://forum.inductiveautomation.com/t/report-design/4284/3 "2012-04-17T19:50:41Z")
You could have your master table results as selectable rows and have the sub tables be results of items selected from the master table maybe driven by any of the action events you seem to choose… onClick? Just an idea… ---
**Author:** ![jamess](https://avatars.discourse-cdn.com/v4/letter/j/87869e/32.png) [@jamess](https://forum.inductiveautomation.com/u/jamess)\ **Post date:** [June 10, 2013, 7:32pm UTC](https://forum.inductiveautomation.com/t/report-design/4284/4 "2013-06-10T19:32:25Z")
I figured this might be worth posting, as I assume I’m not the only person in the world who struggles with SQL now and again: I needed to report some basic Work Order data from our maintenance database, and show all involved assets, technicians/time applied, and parts used for each work order. In the past, using something like Access as a front end, I could have easily dropped the asset, technician and parts data into sub-reports. There is no sub-report analogue in the Ignition Report Designer, so to prevent me from going skydiving without a parachute, one of my guys decided the best solution was to just treat them as data types. I had already built a [color=#FF8000]SQL View[/color] for each sub-report category so we were already starting with only useful data. In an aggregating View, he used [color=#BF4080]UNION[/color] statements to bring the data that would normally be contained in a sub-report into a single [color=#FF00BF]results table[/color], adding a “[color=#4000FF]type[/color]” column to identify which sub-report the data row is part of. The sub-report data is returned with the [color=#408080]type[/color], a [color=#408080]parameter column[/color], and a [color=#408080]value[/color] column. ------- Start of horrible SQL stuff (Jimmy’s Code of Doom) SELECT wo\_num, date\_completed, WO\_Description, date\_added, requested\_by, CASE WHEN Asset\_ID IS NOT NULL THEN Asset\_ID WHEN Resource\_Description IS NOT NULL THEN Resource\_Description WHEN KP\_Part\_Number IS NOT NULL THEN KP\_Part\_Number END AS [color=#408080]Col1[/color], CASE WHEN Asset\_Description IS NOT NULL THEN Asset\_Description WHEN Resource\_Actual\_Hours IS NOT NULL THEN CAST(CAST(Resource\_Actual\_Hours AS DECIMAL(9, 1)) AS nvarchar(255)) + ’ hours’ WHEN Part\_Description IS NOT NULL THEN Part\_Description END AS [color=#408080]Col2[/color], [color=#4000FF]WO\_Type[/color] FROM (SELECT wo\_num, date\_completed, WO\_Description, date\_added, requested\_by, Work\_Order\_Type, Asset\_ID, Asset\_Description, NULL AS Resource\_Actual\_Hours, NULL AS Resource\_Description, NULL AS KP\_Part\_Number, NULL AS Part\_Description, ‘Asset’ AS WO\_Type FROM [color=#FF8000]dbo.ignition\_report\_work\_order\_assets[/color] [color=#BF4080]UNION[/color] SELECT wo\_num, date\_completed, WO\_Description, date\_added, requested\_by, Work\_Order\_Type, NULL AS Asset\_ID, NULL AS Asset\_Description, Resource\_Actual\_Hours, Resource\_Description, NULL AS KP\_Part\_Number, NULL AS Part\_Description, ‘Resources’ AS WO\_Type FROM [color=#FF8000]dbo.ignition\_report\_work\_order\_resources[/color] [color=#BF4080]UNION[/color] SELECT wo\_num, date\_completed, WO\_Description, date\_added, requested\_by, Work\_Order\_Type, NULL AS Asset\_ID, NULL AS Asset\_Description, NULL AS Resource\_Actual\_Hours, NULL AS Resource\_Description, KP\_Part\_Number, Part\_Description, ‘Parts’ AS WO\_Type FROM [color=#FF8000]dbo.ignition\_report\_work\_order\_parts[/color]) AS [color=#FF00BF]tbl[/color] ------- End of horrible SQL stuff The following columns are common to each work order, so they always contain data: wo\_num, date\_completed, WO\_Description, date\_added, requested\_by The sub-report data is returned in the three new columns like this: col1\_\_\_\_\_\_\_\_\_\_\_\_\_col2\_\_\_\_\_\_\_\_\_\_\_\_\_\_WO\_Type 13-0144\_\_\_\_\_\_\_\_\_\_EMS Light\_\_\_\_\_\_\_\_\_\_Asset 13-0145\_\_\_\_\_\_\_\_\_\_EMS Light\_\_\_\_\_\_\_\_\_\_Asset 001-0000\_\_\_\_\_\_\_\_\_NUT, C, TO WEXXAR\_\_Parts 121-0005\_\_\_\_\_\_\_\_\_COIL, SOLENOID\_\_\_\_\_Parts Brown, Standard\_\_\_\_0.5 hours\_\_\_\_\_\_\_\_\_\_Resources Once the data was in this form, building the Ignition report was a lot easier. This has saved me a lot of frustration, so I hope it is helpful to someone else out there. It’s also why I only hire people smarter than me. ![:thumb_left:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e26afb081ddde6df016df6ee49159cf56aaca688.gif ":thumb\_left:") ---
**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\ **Post date:** [June 26, 2013, 6:05pm UTC](https://forum.inductiveautomation.com/t/report-design/4284/5 "2013-06-26T18:05:34Z")
That looks like you have a good solution. You can also use Table Groups in the Reporting module to make data from completely separate datasets show up in one long list. In that case, you would have 3 datasets and each one would join your table1 to each of the other tables. ie SELECT \* FROM table1 JOIN table2 SELECT \* FROM table1 JOIN table3 SELECT \* FROM table1 JOIN table4 ---
**Author:** ![jamess](https://avatars.discourse-cdn.com/v4/letter/j/87869e/32.png) [@jamess](https://forum.inductiveautomation.com/u/jamess)\ **Post date:** [June 26, 2013, 7:12pm UTC](https://forum.inductiveautomation.com/t/report-design/4284/6 "2013-06-26T19:12:13Z")
We tried using Table Groups, but ran into the following problem: Col1 Col2 Col3 Col4 data data1 NULL NULL – from table 1 data NULL data2 NULL – from table 2 data NULL NULL data3 – from table 3 Table Grouping on the above data would return three rows of data, when we really wanted to get: Col1 Col2 Col3 Col4 data data1 data2 data3 When considering the specific report we were trying to build (described earlier in the thread), we would end up with: ``` a) each resource listed and all of the parts used listed under each resources ----resource1 ------part1 ------part2 ------part3 ----resource2 ------part1 ------part2 ------part3 ``` – OR – ``` b) each part listed and all of the resources from the work order listed under each part ----part1 ------resource1 ------resource2 ----part2 ------resource1 ------resource2 ----part3 ------resource1 ------resource2 ``` We were unable to find a way to prevent this behavior/problem without resorting to the SQL script described above. If there is a way to achieve the results we got, purely within Report Designer, then there is still something we don’t understand – and would be very interested in learning. ---
**Author:** ![Robert.McKenzie](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/robert.mckenzie/32/4134_2.png) [@Robert.McKenzie](https://forum.inductiveautomation.com/u/Robert.McKenzie)\ **Post date:** [June 26, 2013, 11:52pm UTC](https://forum.inductiveautomation.com/t/report-design/4284/7 "2013-06-26T23:52:33Z")
Using a table group you should be able to get something like this: Col1 Col2 data data1 – from table 1 Col1 Col3 data data2 – from table 2 Col1 Col4 data data3 – from table 3 If that isn’t right, you can always make 5 datasets and use a propertyChange event to mash the 4 together in scripting if they are all the same number of rows. `if event.propertyName == "data1" or event.propertyName == "data2" or ...: data1 = ... data2 = ... data3 = ... data4 = ... header = ["data1", "data2", "data3", "data4"] finaldata = [] for i in len(data1): finaldata.append([data1[i][0], data2[i][0], data3[i][0], data4[i][0]]) event.source.finaldata = system.dataset.toDataSet(header, finaldata)` ---
**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:** [March 26, 2018, 8:30pm UTC](https://forum.inductiveautomation.com/t/report-design/4284/8 "2018-03-26T20:30:54Z")
Split this topic on March 26, 2018, 8:30pm UTC A post was split to a new topic: [Reporting checkboxes?](https://forum.inductiveautomation.com/t/reporting-checkboxes/17503)