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).

Hi ​@Steve Milner , thank you for the insight, it was so helpful and it has greatly improved the report’s performance, I had attached the modified version as per your suggestions for review, I hope I didn’t misunderstand some of the relationships, I am still having trouble retrieving Inventory Related PO Details which previously I was using “=[POOrderReceipt.PONbr]+' ['+ [POOrder.OrderDesc]+']  '+[POOrder.VendorID]+' ['+[POOrder.VendorID_Vendor_acctName]+']'” to retrieve, how do I introduce the POOrderReceipt

I have also attached screenshots, the first one is for the previous version and the second is after the modifications of the same batch,

@lorach your relationships are right. INTran, POReceipt and POReceiptLine are joined on their full keys, and so are Location, APInvoice and APPOOrder now.

You don't need POOrderReceipt. Its key is ReceiptType, ReceiptNbr, POType and PONbr. Joined on the receipt, it matches every PO on that receipt and repeats the GL line whenever one receipt covers two or more POs. POReceiptLine already carries the PO for its own line.

What's missing is a POOrder joined to that line. Your inventory PO text box takes the description and vendor from APPOOrder. APPOOrder comes through POAccrualDetail on the bill's RefNbr, DocType and line number, so it describes the PO behind a bill line, not an inventory line.

Add a relation with POReceiptLine as the parent and POOrder as the child, with no alias, on POOrder.OrderType = POReceiptLine.POType and POOrder.OrderNbr = POReceiptLine.PONbr. APPOOrder is an alias, so the plain POOrder name is still free. Then point the text box at it:

=[POReceiptLine.PONbr]+' ['+[POOrder.OrderDesc]+']  '+[POOrder.VendorID]+' ['+[POOrder.VendorID_Vendor_acctName]+']'