Report Design

[jamess]

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

[markdobtech]

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.

[ecujak]

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…

[jamess]

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.

[Robert.McKenzie]

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

[jamess]

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.

[Robert.McKenzie]

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)