Acumatica Report Designer — The Definitive Guide

Report Designer is the most-used, most-misunderstood tool in the Acumatica platform. Used casually, it produces slow, fragile PDFs. Used well, it is the difference between a finance team that trusts the system and one that does not. This is the second kind of usage, distilled from years of report work across East and Southern Africa.

1. The schema is the report

Most report problems start in the layout, but the fix is in the schema. The schema is a SQL definition: which tables, which joins, which fields, which parameters. Get the schema right and the layout is trivial. Get it wrong and no amount of layout tweaking will save you.

2. Building blocks

• Tables / Views — the DACs you query. Use the Acumatica view catalogue — views are optimised.
• Relations — joins. Start with predefined relations; add explicit joins only when needed.
• Parameters — runtime inputs.
• Filters — the WHERE clause. Push everything you can down to SQL.
• Groups — collapse rows for sub-totals and grand totals.
• Layout — the RDLC or the Modern UI designer. The visual layer.

3. Parameters that empower users

Reports without parameters are reports nobody runs twice. The Acumatica parameter system supports defaulted values, dropdowns, multi-select lists, and visibility expressions. Use all of them.

=DateSerial(Year(Now), Month(Now), 1)   // first of this month
=Today()
=DateAdd('d', -30, Today())              // 30 days ago
=GetBranch()                             // current branch

4. Conditional formatting without losing your mind

The Visibility, BackgroundColor, and Font properties on every cell accept expressions. This is how you build reports that read like dashboards.

5. Subreports for hierarchical data

Common need: invoice header with its line items. The cleanest approach is a subreport tied to the parent invoice's RefNbr:

1. Build the parent report (header data: invoice number, customer, date, total).
2. Build a separate, simpler report for line items. This report takes RefNbr as a parameter.
3. On the parent, drop a Subreport control and bind its RefNbr parameter to the parent row's RefNbr.

6. Performance — the silent killer

Acumatica Report Designer runs your schema as one big SQL query at execution time. If your report takes more than 4–5 seconds on test data, it will take 30+ seconds on production. Fix it early.

The three performance fixes that have rescued every slow report I have inherited:

1. Filter at schema level, not in expressions. A WHERE clause on the schema lets SQL Server use indexes. A row-level Visibility expression forces SQL to return every row anyway.
2. Avoid SELECT * implicit joins. Only join the DACs you actually pull fields from. Each unnecessary join multiplies row count and IO.
3. Push aggregation into the schema. If you need a sum, use a SQL SUM() in the schema rather than a report group footer expression — the database is far faster at this than the report engine.

7. Cross-tab and matrix reports

Acumatica Report Designer supports cross-tab output (rows × columns × value). Powerful, but expensive. Two rules: keep the source set small, and pre-aggregate into a view for very large data.

8. Version control and packaging

Reports live in source control as RDLC + schema + parameter XML. The Customization Project Editor is for inspection; the actual authoring should happen in your IDE with the schema files under Git.

9. Upgrade safety

The single biggest reason custom reports break on Acumatica upgrades is direct DAC field references that get renamed or relocated. Two habits to adopt:

• Wrap your reports in a customisation project so they ship with versioned, exportable definitions.
• Add a header comment documenting which DACs the report depends on. When you upgrade, you have a checklist.

10. Common patterns I reuse

Across most distribution and manufacturing clients in East Africa, the same handful of reports keep coming up. Build them once, parameterise them well, and reuse:

• Aged receivables and payables (with branch, customer class, and date filters)
• Sales by salesperson / branch / item class with drill-down by period
• Inventory valuation snapshot at a given date
• VAT / fiscal summary reports (especially in fiscalisation-regulated markets like Kenya KRA eTIMS, Rwanda RRA, Zambia ZRA)
• Production cost variance for manufacturing

11. Common mistakes

1. Building the report first, schema second.
2. Using expressions to filter instead of WHERE.
3. Joining too many tables "just in case".
4. Hiding rows with Visibility instead of filtering.
5. Not indexing the join columns.
6. Letting the report run on production-scale data without a test on production-scale data.

Related reading

• Report Designer Performance Tuning
• Subreports for Hierarchical Data
• Report Parameters vs Prompts

Going deeper: production-grade patterns

The patterns above cover the basics. In production, the same patterns have to survive three things: scale, edge cases, and the next Acumatica upgrade. Here are the patterns that distinguish a working customisation from a great one — the ones I have applied to every client project in East and Southern Africa, and the ones that make the difference between a customisation the user trusts and a customisation they curse.

Defensive coding for the unexpected

Production is where the assumption dies. Every customisation that "works in test" fails in production the first time a customer name has a special character, an invoice is in a foreign currency, or a record has a null in a field you thought was required. The defensive habit is to explicitly handle the null, the empty, the special character, and the foreign currency in every event handler and every code path. The cost is 20% more code. The payoff is 95% fewer production tickets.

Three patterns I apply everywhere:

• Null-safe property access. Use ?. on every property access; the alternative is a NullReferenceException at 2 AM.
• Explicit value handling. If a field can be empty, treat it as empty. Do not assume the default value.
• Defensive database reads. A PXSelect that returns null is a valid result, not an error. Handle it.

public class DefensiveExt : PXGraphExtension<BaseGraph>
{
    protected void _(Events.RowSelected<MyDAC> e)
    {
        var row = e.Row;
        if (row == null) return;                          // null-safe
        var ext = row.GetExtension<MyDACExt>();
        if (ext == null) return;                         // null-safe extension
        var value = ext.UsrField ?? "DEFAULT";           // null-coalesce
        var ok = decimal.TryParse(value, out var n);    // try-parse
        if (!ok) { /* handle */ }
    }
}

Performance: the patterns that scale

Five performance patterns I apply on every customisation, in order of impact:

1. Move heavy logic out of RowSelected. Push validation to RowPersisting, side effects to a graph action triggered by a button. RowSelected fires for every row on every render.
2. Index the join columns. Every BQL Where<> filter needs an index. Check the execution plan before you ship.
3. Filter at the GI, not the UI. A GI that returns 5 million rows and filters in the presentation layer will time out. Push filters into the Conditions tab.
4. Batch the work. Loop with 1,000 calls is slow; loop with 10 calls of 100 records is fast. Batch where you can.
5. Cache the static. Tax schedules, account lists, and other static reference data can be cached for the lifetime of the app pool. Reduce the database load.

For the full performance playbook, see the performance tuning guide and the SQL Server indexing guide.

Upgrade survival

The customisation that breaks on the next Acumatica upgrade is the one that took a shortcut. The patterns that survive:

• Extend, never modify. PXCacheExtension<T> over editing the base DAC. PXGraphExtension<T> over editing the base graph.
• Usr prefix on every field. Acumatica uses this to separate your fields from base fields. Without it, your field collides with a base field on the next upgrade.
• Source control with a clear branch strategy. Main is production; develop is next release; feature branches are work in flight. Tag every release.
• Test on production data. The staging tenant is a copy of production. The data shape is the same. The bugs are the same.

// Base field — Acumatica owns this
[PXDBString(40)]
public string RefNbr { get; set; }

// Your field — always Usr prefix, never collides
[PXDBString(40)]
[PXUIField(DisplayName = "External Ref")]
public string UsrExternalRef { get; set; }

// Your DAC extension — soft extension, survives table drops
[PXTable(IsOptional = true)]
public class MyDACExt : PXCacheExtension<MyDAC>
{
    #region UsrCustomField
    [PXDBString(60)]
    public string UsrCustomField { get; set; }
    public abstract class usrCustomField :
        PX.Data.BQL.BqlString.Field<usrCustomField> { }
    #endregion
}

Testing: the habit that pays for itself

If you are not testing your customisation with the Acumatica Unit Test Framework, you are running blind. The framework ships with every installation, costs nothing, and pays for itself the first time an upgrade changes a method signature on you. The minimum coverage:

• Every graph action with business logic — happy path + the most common error path.
• Every DAC field with a defaulting or validation rule.
• Every workflow transition — that the right state is reached from the right source state.
• Every import scenario with a sample CSV that exercises the validation rules.

For the full test framework walkthrough, see the unit test framework guide.

Operations: what to do after the customisation is live

A customisation is not "done" when it ships. It is "done" when it has run in production for a quarter without a critical incident. The operational habits that get you there:

• Monitor the slow queries. Acumatica's System Monitor has a slow-query log. Review weekly; the slow query you ship is the production incident in two months.
• Track the licence headroom. Every active session counts. A leaking integration can lock out real users within an hour.
• Review the audit log. Not for compliance — for understanding how the system is used. The audit log tells you where to optimise next.
• Document the runbook. When the customisation fails (and it will), the runbook is what saves the on-call. Document the failure modes, the diagnostic flow, the fix.

For the broader operational patterns, see the monitoring guide and the licence concurrency guide.

The migration off the old customisation

Every customisation is eventually replaced. Plan for that day from the start. The patterns:

• Wrap external dependencies. If your customisation talks to an external API, wrap the call in your own service. When the API changes, you change one place.
• Tag the version. Every customisation has a version. The version is in the database, in the metadata, in the package. When you upgrade, you know what you are upgrading from.
• Document the data model. Every custom field, every new table, every relationship. The next person who has to read the customisation should be able to start with the data model.
• Test the migration path. A customisation that ships and cannot be removed is a liability. The migration path off should be tested before the customisation is in production.

For the broader migration patterns, see the data migration guide.

Wrapping up

That is the working approach I use on Acumatica projects. The same patterns show up whether you are in Nairobi, Johannesburg, Kigali, Lusaka or Harare — and they are the things that keep work moving when an upgrade lands at 6 PM on a Friday. If you are stuck on something specific, reach out or keep reading through the rest of the Acumatica blog.