Don't use table joins in the main report.  Try a sub-report instead.  You can't break the main report that way and can focus on getting your sub-report filtered properly.

If you are trying to dramatically reshape the report data to be grouped differently, start fresh from raw data (instead of trying to manipulate a Sage report into a different structure).

Ok I'll give it a shot! Do you know of any resources that detail the structure of BOMs? I feel like my biggest hurdle so far is data-wise I have little to no understanding of how it's structured. Checked the knowledgebase and didn't see much...

Check the file layouts. The table primary key fields give strong hints at the relationship, but BM history follows the same patterns of other transactions Header > Details (and optionally to TierDist... so use an outer join here)

https://help-sage100.na.sage.com/2021/FLOR/index.htm#File_Layouts/Bill_of_Materials/BM_ProductionHistoryHeader.htm?TocPath=File%2520Layouts%257CBill%2520of%2520Materials%257C_____42

Sub-report ended up being a slam dunk, for the most part. The only hole in the logic is regarding the serial numbers. The only reliable link we can make is BM_ProductionHistoryHeader.LotNoOrStartingSerialNo to AR_InvoiceHistoryWrk.LotSerialNo. The issue with that obviously is if there are multiple serialized components, we're not going to get the rest of those serial numbers. My manager and I have been scouring the tables and can't find a link for this. Any ideas?