Acumatica GI with Multiple Data Sources

Acumatica GI with Multiple Data Sources sits at the intersection of three forces: what the user wants to see, what the database can deliver, and what the platform will let you wire up. Most Acumatica GIs are simple lists. The ones that pay dividends are the ones that solve a real business question — and those are the ones where the patterns below make the difference between "the report loads" and "the report tells the story".

Anatomy of a powerful GI

A GI is a saved query with five parts: tables, relations, results, conditions, and parameters. The first three define what the GI returns; the last two define what filters and inputs the user sees. Most GIs that fail do so because one of these five parts was wrong. The most common failure: the conditions part was an afterthought.

The join strategy

Joins are where the GI's performance is won or lost. The rule: start with the most-restrictive table, join outward, and never join a table you do not need a field from. Each join is a multiplication factor on the row count.

SELECT
  ar.RefNbr, ar.DocDate, ar.CuryOrigDocAmt, c.AcctName, b.BranchCD
FROM ARInvoice ar
INNER JOIN Customer c ON ar.CustomerID = c.BAccountID
INNER JOIN Branch b ON ar.BranchID = b.BranchID
WHERE ar.DocDate BETWEEN '2026-01-01' AND '2026-12-31'
  AND ar.Status = 'O'
  AND ar.BranchID IN (1, 2, 3)
ORDER BY ar.DocDate DESC;

If your GI is slow, the most likely cause is one of: a missing index on a join column, an unneeded join, or a missing filter in the conditions. Check the actual SQL Acumatica generates (System Monitor > Slow Queries) before you change anything else.

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

Calculated columns that earn their keep

Calculated columns are expressions over the joined fields. The most useful ones for this kind of GI are aggregations, time deltas, and status labels. The expression language is Acumatica-specific but SQL-flavoured — read the cheat sheet once and you have it.

// Days outstanding
=IIf([Status] = 'O', DateDiff('d', [DueDate], Today()), 0)
// Status label
=IIf([Status] = 'H', 'On Hold', IIf([Status] = 'O', 'Open', IIf([Status] = 'C', 'Closed', 'Other')))
// Line total
=[Qty] * [UnitPrice]
// Aggregate: =Sum([CuryOrigDocAmt])

For the full pattern catalog, see the aggregations and rollups article.

Parameters that change behaviour

Parameters are what make a GI reusable. The patterns I use the most:

• Date range with sensible defaults. First of current month to today. The user can override but rarely has to.
• Branch multi-select. Defaults to the current branch. Allows multi-tenant reporting.
• Customer / vendor selector. Bind to a screen for a typed lookup. Faster and safer than free text.
• Status checkboxes. Released vs unreleased, or open vs closed, depending on the document.

// First of current month
=DateSerial(Year(Now), Month(Now), 1)
// Today
=Today()
// 30 days ago
=DateAdd('d', -30, Today())
// Current branch
=GetBranch()
// Current user
=GetUserID()

For the full parameters reference, see the GI parameters and default values guide.

Conditional formatting that tells a story

Formatting is not decoration; it is information density. A GI with the right conditional formatting reads like a dashboard; a GI without it reads like a spreadsheet. The patterns I reuse:

For a complete style template, see the conditional formatting guide.

Row-level security

Acumatica's role-based access control governs which screens a user can see. It does not, by default, govern which rows a GI returns. For GIs that touch sensitive data, you need to add an explicit row-level filter.

// Always include the current user/branch in the WHERE clause
Conditions tab:
  [BranchID] IN (GetAccessibleBranches())
  OR GetUserID() = 'ADMIN'

For the full security playbook, see the row-level security guide and the row-level security best practices.

Publishing to the right surface

Where a GI is published changes its access model. The options:

• Workspace — visible to the user, scoped to a workspace, typical for an internal analytical tool.
• Site Map — full screen, classic UI navigation, for a report the user accesses directly.
• Self-Service Portal — customer-facing. Strict row-level security required.
• Dashboard widget — a small GI on a dashboard. Top N rows, usually aggregated.
• Data source — for a report, an integration, or another GI. No direct user access.

For the portal pattern specifically, see the portal publishing guide.

GI performance at scale

When a GI is slow, the diagnostic flow is:

1. Run the GI with a single parameter set. Time it.
2. Capture the SQL Acumatica generates (System Monitor > Slow Queries).
3. Look at the execution plan. Find the largest cost step.
4. Add the missing index, or rewrite the join.
5. Re-run. Repeat until sub-second.

SELECT TOP 20
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) AS improvement_measure,
    OBJECT_NAME(d.object_id, d.database_id) AS table_name,
    d.equality_columns, d.inequality_columns, d.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details d ON mig.index_handle = d.index_handle
ORDER BY improvement_measure DESC;

For the full performance playbook, see the cross-table joins deep-dive, the aggregations guide, and the performance tuning guide.

Wrapping up

A powerful GI is part query, part data model, part presentation. Get all three right and you have a tool the business uses daily. Get any one wrong and the GI gets ignored. The patterns above cover the 90% case; the remaining 10% is knowing when to give up on a GI and build a real report or a real custom screen instead.