How to Optimize a report with subreports

@lorach both subreports sit in the Details section, so the report engine runs each one once per detail row. Every run builds its own graph and sends its own query to the database. A period with 10,000 GL lines means 20,000 extra queries on top of the main one.

Hiding them won't help. The engine runs a subreport's query no matter what its VisibleExpr, or the section's, evaluates to.

The fix is to drop both subreports and join the data in the main report at line level, the way you already reach bills through POAccrualDetail. On GL lines that come from inventory, GLTran.TranLineNbr is the INTran line number, so join INTran on INRegister.DocType, INRegister.RefNbr and GLTran.TranLineNbr. INTran carries POReceiptType, POReceiptNbr and POReceiptLineNbr on PO receipt lines and on landed cost adjustments. POReceipt then gives you InvoiceNbr, and POReceiptLine gives you POType and PONbr for POOrder. Each of those joins hits a full key, so no GL line repeats and your running balance stays right.

One catch: if Post Summary on Updating GL is selected on Inventory Preferences (IN101000), TranLineNbr is empty and those columns come back blank.

Three of the joins you added also miss part of a key. Location joins Vendor on BAccountID alone, APInvoice joins on RefNbr alone, and APPOOrder joins on OrderNbr alone. Any of them can match more than one row and print a GL line twice, so join each on its full key (for Location, add LocationID = Vendor.DefLocationID).