Acumatica Sales by Period Report — Tuning Guide is one of those reports every business needs and almost no business has a clean implementation of. The reason is that the report crosses the boundary between the sub-ledger and the GL — and at that boundary, every misconfigured join, missing index, and unfiltered row is a performance problem. This guide is the field-tested pattern for getting it right the first time.
What the report needs to show
Before you open Report Designer, write down the question the report answers. The question drives the data model, the joins, and the parameters. For an aged receivables report, the question is: "for each customer, how much is outstanding, broken down by age bucket, as of a given date?" The four parts of the question are the four design constraints:
- Per customer — group by customer.
- How much is outstanding — sum of open balance, with credit memos applied.
- By age bucket — split by 0-30, 31-60, 61-90, 90+ days past due.
- As of a given date — back-dated, not just "today".
If you cannot state the report's question in one sentence, you cannot build the report. Most bad reports are bad because the question was not stated — the developer started with "show me everything" and then iterated. The iteration never converges.
The schema, top to bottom
The Acumatica view catalogue is the right place to find the data. For AR-related reports, the views you need:
-- Document-level (ARInvoice)
SELECT RefNbr, DocDate, DueDate, CuryOrigDocAmt, CuryDocBal, Status, CustomerID, BranchID
FROM ARInvoice
-- Customer (Customer)
SELECT BAccountID, AcctCD, AcctName, CustomerClassID
FROM Customer
-- Branch (Branch)
SELECT BranchID, BranchCD, AcctName
FROM Branch
-- Apply credits (ARAdjust)
SELECT AdjgRefNbr, AdjdRefNbr, CuryAdjgAmt, CuryAdjdAmt
FROM ARAdjustFor the report, you join the document to the customer and the branch. You bring in the credit applications only if you need to show the gross-vs-net detail.
-- Report Designer · Schema (the data view)
Tables:
ARInvoice (the document header)
Customer (joined to ARInvoice.CustomerID)
Branch (joined to ARInvoice.BranchID)
Relations:
ARInvoice.CustomerID = Customer.BAccountID
ARInvoice.BranchID = Branch.BranchID
Filters:
ARInvoice.Status = 'O'
ARInvoice.Released = 1
ARInvoice.DocDate <= @AsOfDate
Parameters:
@AsOfDate Date, default = Today()
@BranchID Multi-select, default = GetBranch()For the broader Report Designer patterns, see the Report Designer definitive guide.
Join performance is everything
For an aged AR report on a 2-million-row ARInvoice table, the joins are the performance story. The columns you join on must be indexed. The fields you select must be minimal. The filters must be in the WHERE, not in the UI.
-- 1. Run the report, time it
-- 2. Capture the generated SQL
-- 3. In SSMS, click "Include Actual Execution Plan"
-- 4. Run the SQL, look for: Table Scan, Hash Match without index, Sort warnings
-- 5. Add the missing index, re-runIf your execution plan shows a table scan on ARInvoice, the most common cause is a missing index on the filter column. Add an index on (Status, DocDate, CustomerID) and re-run.
For the full performance playbook, see the Report Designer performance guide and the performance tuning guide.
The age bucket calculation
The age bucket is a calculated column. The pattern:
=IIf(
[DocDate] >= @AsOfDate, 0,
IIf(
DateDiff('d', [DocDate], @AsOfDate) <= 30, 1,
IIf(
DateDiff('d', [DocDate], @AsOfDate) <= 60, 2,
IIf(
DateDiff('d', [DocDate], @AsOfDate) <= 90, 3,
4))))The result is a number (0 = not yet due, 1-3 = days 1-30, 31-60, 61-90, 4 = 90+). Use it in a column expression to show the bucket label, and use it in a group-by to roll up the totals.
| Bucket | Label |
|---|---|
| 0 | Not due |
| 1 | 1–30 days |
| 2 | 31–60 days |
| 3 | 61–90 days |
| 4 | 90+ days |
Grouping and rollup
The natural group-by is customer, with sub-totals by bucket. In Report Designer, add the bucket as a column, group by customer, group by bucket, and the sum is the column total. The grand total is the customer total across all buckets.
// Group 1: Customer
// Group 2: Age Bucket
// Sum: [CuryDocBal]
// Sum: [CuryDocBal] (subtotal by customer)
// Sum: [CuryDocBal] (grand total)Parameters that make the report reusable
The aged report is most useful when it can be run for any date, any branch, any customer class. The parameter set:
- As-of date — back-dated reporting. Default to today.
- Branch — multi-select. Default to current branch.
- Customer class — optional filter, default to all classes.
- Customer — optional, for the case where the user wants a single-customer report.
// @AsOfDate: default to today
=Today()
// @BranchID: default to current branch
=GetBranch()
// @CustomerClassID: default to all (no filter)
=Null
// @CustomerID: default to all
=NullFor the full parameters reference, see the parameters vs prompts guide.
Conditional formatting that makes the report scannable
A well-formatted aged report is one where the user can see the problem customers in 5 seconds. The formatting rules:
// Red on the 90+ bucket (column font color)
=IIf([AgeBucket] = 4, "Red", "Black")
// Bold on customer total
=IIf([RowType] = "Total", true, false)
// Background on the grand total row
=IIf([RowType] = "GrandTotal", "#FEF3C7", "White")
// Italic on rows that have been written off
=IIf([WriteOff] = true, true, false) on font italicHiding rows with Visibility still returns them from SQL. For true filtering, push the condition into the schema WHERE. Visibility is for layout, not query optimisation.
For the broader pattern, see the conditional visibility guide.
Subreports for related detail
The summary report is the user-facing view. The detail (which invoices, which credit memos) goes in a subreport. The pattern:
- Build the parent report with the summary (customer, bucket, total).
- Build a child report with the detail (invoice list with dates, amounts, references).
- On the parent, drop a Subreport control. Bind its
CustomerIDparameter to the parent row's customer. - Place the Subreport in the detail band of the parent.
For the full subreport pattern, see the subreports guide.
Performance at production scale
The aged report is the most-clicked report in most finance teams. Performance is the difference between a report that the team trusts and a report they curse. The numbers that matter:
| Scale | Time to render | Acceptable? |
|---|---|---|
| 10k documents | < 1 second | Yes |
| 100k documents | 1–3 seconds | Yes |
| 500k documents | 3–10 seconds | Borderline |
| 1M+ documents | 10+ seconds | No — needs pre-aggregation |
For very large tenants (1M+ open documents), pre-aggregate the balances into a snapshot table. Run a nightly job to populate the snapshot. The report queries the snapshot, not the live ARInvoice table. Instant results; consistent data.
For the full performance playbook, see the performance tuning guide and the SQL Server indexing guide.
Wrapping up
That is the pattern, top to bottom. The question first, the schema second, the joins third, the bucketing fourth, the formatting fifth, the performance sixth. Get all six right and the report becomes a tool the team uses daily. Get any one wrong and the report gets ignored. The difference is in the discipline of the build, not in the technology.
For the related reading, the Report Designer definitive guide covers the broader pattern catalog. The Generic Inquiries guide is the right starting point if your report is really a query, not a document.