Acumatica Subreports for Hierarchical Data

Sooner or later every Acumatica report hits data that will not flatten: a sales order with shipments, each shipment with packages; a project with tasks, each task with transactions; a BOM whose components are themselves BOMs. The Report Designer's grouping sections handle one level of master-detail comfortably. Beyond that, you want subreports — and subreports in Acumatica have enough sharp edges that they deserve their own write-up.

First: exhaust grouping before reaching for subreports

A surprising amount of "hierarchical" reporting is really just two levels, and two levels is what group headers and footers are for. Join SOOrder to SOLine in the report's Schema Builder, group by SOOrder.OrderNbr, put header fields in the group header and line fields in the detail section. One query, one pass, fast.

Reach for subreports when one of these is true:

• You need two independent child collections under one parent — e.g. an order's lines and its payments. Joining both into one dataset multiplies rows (the classic fan-out), and your totals silently double.
• The hierarchy is recursive (multi-level BOMs, company trees) and the depth is not fixed.
• A child section is reused across reports — a remittance block, a tax summary box, a bank-details footer.

How Acumatica subreports actually work

A subreport in the .rpx world is not an embedded dataset — it is a full report file invoked per parent row. You drop a SubReport control into a section, set its ReportName to the child report (by its file name in the report catalogue, e.g. KG.SO.Payments), and pass context through parameters:

ReportName:  KGSO601P
Parameters:
  Name:  OrderType    Value:  =[SOOrder.OrderType]
  Name:  OrderNbr     Value:  =[SOOrder.OrderNbr]

Inside the child report, declare matching parameters (same names, case matters) and use them in the child schema's WHERE conditions:

Field:      ARAdjust.AdjdRefNbr
Condition:  Equal
Value:      =[@OrderNbr]

The parent renders, and for each row where the subreport control appears, the engine executes the child report's query with those parameter values. That "executes per row" clause is the whole performance story, and we will come back to it.

Recursive hierarchies: subreports calling themselves

For a multi-level BOM explosion, the trick is a report that embeds itself as its own subreport, passing the component's inventory ID down as the parameter for the next level. It works, and it is how I built an indented BOM cost report for a furniture manufacturer — but two constraints apply:

• You must bound the depth. The engine will not detect a cycle in your BOM data; a part that (through bad data) contains itself recurses until the render times out. Pass a Level parameter, increment it at each call, and suppress the subreport control with a visibility expression like =[@Level] < 6.
• Indentation is manual. There is no tree control; multiply an indent width by [@Level] in a padding expression, or prefix the description with a computed string.

Performance: the N+1 problem, printed

A subreport per row is an N+1 query pattern by construction. A 40-line invoice with one subreport is fine. A 3,000-row register report with a subreport per row will take minutes and can pressure the app server, because each child execution is a full report pipeline: schema load, query, render.

Mitigations, in order of preference:

1. Move the subreport up a level. If the child data can be filtered by the group key rather than the row key, put the control in the group footer instead of the detail band — one execution per group instead of per row.
2. Collapse to a join where fan-out does not hurt. If one of your two child collections is aggregate-only (say, total payments), replace that subreport with a joined aggregate in the parent schema and keep only one subreport.
3. Pre-aggregate in a GI or projection and join the parent to that.

Deployment gotchas

Subreport references are by report name, resolved at runtime from the site's report catalogue. Package the child reports in the same customization project as the parent, or at least publish them together — a parent published without its child renders an empty box with no error on some builds, and a hard failure on others. Also note that the child report needs no sitemap entry; it can exist purely as a callee, which keeps the report list clean for end users.

Versioning bites here too: if you rename a child report, nothing warns you that three parents reference the old name. I keep a naming convention (KGxx6nnP for subreport-only children) and a one-line note in each parent's Description field listing its children. Low-tech, but it has saved me during more than one upgrade.

Wrapping up

Subreports are the right tool for sibling child collections, reusable document blocks, and bounded recursion — and the wrong tool for anything executed thousands of times per render. Exhaust grouping first, pass keys through parameters exactly, bound your recursion depth, and package parents with their children. When the hierarchy gets genuinely deep or rule-laden, flatten it in C# and let the report do what reports are good at: layout.