Ask Search:
Chris UttleyChris Uttley 

How to report on two parent-child structures

I need to create a report that shows information from the four tables in green on the data diagram below. 
You will notice that Labor Order is a child of Work Order Header, and Work Order Operation is also a child of Work Order Header.
​I know its not possible to build a single report type that can handle this scenario.
Is this a situation where I MUST use a joined report? I havent built a joined report before, but it feels like that would be the solution.

Any advice? All help appreciated!User-added image
Best Answer chosen by Chris Uttley
Tom HoffmanTom Hoffman
Yeah, won't be able to branch out like that in a single report. Thinking joined report is your only option then - lucky for you this just arrived as a beta over the weekend: https://releasenotes.docs.salesforce.com/en-us/summer18/release-notes/rn_rd_joined_reports.htm

All Answers

Tom HoffmanTom Hoffman
Custom Fields via lookup are going to be your friend here I think.  

Are the following assumptions true?
A single work order header record has:
• A single labor order
• Belong to a single project

If both of those are true, I would create a custom report type that starts at the work order header level, then add an object B for work order operation, and object c for work order operations labor bookings.  

Then, if its a lookup field to the labor order object and a lookup field to the project, you can pull those fields onto your report type through those lookup fields. 

User-added image

User-added image
Chris UttleyChris Uttley
Thanks Tom. The relationship between Word Order Header and Labor Header is one to many, as per the diagram. So you can have many Labor ORders for a Work ORder Header.
I believe that means your soltuion wont work, which means I am still looking for help!
Tom HoffmanTom Hoffman
Yeah, won't be able to branch out like that in a single report. Thinking joined report is your only option then - lucky for you this just arrived as a beta over the weekend: https://releasenotes.docs.salesforce.com/en-us/summer18/release-notes/rn_rd_joined_reports.htm
This was selected as the best answer
Chris UttleyChris Uttley
Thanks Tom. But not lucky for us. We dont use Lightning and have no intention of moving to it until forced.......
Tom HoffmanTom Hoffman
No worries - joined reports are standard feature in Classic.  

Some resources to get you on your way:
https://trailhead.salesforce.com/en/modules/reports_dashboards/units/reports_dashboards_report_types
https://help.salesforce.com/articleView?id=reports_working_with_joined.htm&type=5
https://help.salesforce.com/articleView?id=reports_adding_blocks.htm&type=0