Acumatica Sales by Period Report — Tuning Guide

A sales-by-period report is the report every sales manager wants sliced a different way — by rep, by territory, by product line, by month versus quarter versus rolling twelve — and that flexibility demand is exactly what makes it slow if you build it the naive way: one wide query joining everything, filtered and grouped differently on every run. The fix isn't a faster query, it's picking the right grain to pre-aggregate at.

Pre-aggregate at the finest grain anyone actually asks for, not finer

If the finest slice anyone requests is "by rep, by product class, by month," there's no reason for the report's base query to join down to individual order lines every time — aggregate once to that grain (a projection or a scheduled summary table refreshed nightly) and have the report query the pre-aggregated set, rolling up further (by quarter, by territory) in the report's own grouping rather than re-querying transaction detail for every possible cut. The mistake I see most is a report that joins SOOrder to SOLine fresh on every run regardless of whether the requested grouping needed line-level detail at all — a rep/month summary doesn't need to touch line-level rows if a pre-aggregated table already has rep/month/product-class totals.

Nightly job populates:  KGSalesSummary (SalesPersonID, ProductClassID, FinPeriodID, Amount, Qty)
Report base:            KGSalesSummary   -- not SOOrder JOIN SOLine
Filter:                 FinPeriodID BETWEEN @FromPeriod AND @ToPeriod
Group (report layer):   further rolled up to quarter/territory as requested

FinPeriodID filtering, again, and why it matters even more here

Sales-by-period reports are inherently period-comparison reports — this month vs last, this quarter vs same quarter last year — which means the filter predicate is almost always expressed in period terms even when users think of it as a date range. Filtering by FinPeriodID against a summary table keyed by period is both more correct (avoids date-boundary ambiguity around period-end cutover) and faster (string equality/range on an indexed key column) than converting user-entered dates into a period lookup on every run.

Decide how stale "current period" data is allowed to be

If the report reads from a nightly-refreshed summary table, today's orders won't appear until tomorrow's refresh — fine for a monthly management pack, not fine for a sales manager checking today's numbers at 4pm. Build two paths deliberately: closed/prior periods always read the fast summary table, and the current, still-open period reads live from SOOrder/SOLine directly (a much smaller, recent-only dataset, so the live join is cheap precisely because it's bounded to one open period). A report that tries to serve both needs from one always-live query pays full cost on every run for a need that's mostly about historical trend, not today's number.

Wrapping up

Pre-aggregate to the finest grain anyone actually requests and let the report roll up further from there instead of re-querying line detail on every run. Filter and key by financial period rather than raw dates, split closed-period reporting (fast, from a summary table) from current-period reporting (live, but cheap because it's bounded to one open period), and make sure your refresh job re-touches recent periods, not just the newest one, so backdated orders don't silently vanish from the numbers.