Mastering Grouping and Aggregation in Acumatica Generic Inquiries

Generic Inquiries are one of Acumatica's most powerful reporting tools, but their true potential lies in grouping and aggregation. Whether you need to summarize sales by customer, calculate average invoice amounts, or count orders by status, mastering these techniques will transform your reporting capabilities.

⚡ Key Insight: Grouping and aggregation work together—you define how to group records on the Grouping tab, and then apply aggregate functions on the Results Grid to calculate sums, averages, counts, and more [citation:10].

Understanding Grouping and Aggregation

Before diving into examples, it's essential to understand how grouping and aggregation work in Acumatica GIs:

Grouping – Defines how records are organized into logical groups (e.g., by Customer, by Date, by Category)
Aggregation – Performs calculations on values within each group (e.g., SUM, COUNT, AVG, MIN, MAX)
Total Aggregation – Calculates overall totals across all groups (displayed at the bottom of the GI) [citation:1]

The key principle is that aggregate calculations only work when you have grouping defined. Without grouping, the system doesn't know how to segment the data for calculations [citation:1].

💡 Pro Tip: For aggregate functions to work correctly, you need to add fields to the Grouping tab and then apply aggregate functions to fields in the Results Grid. The grouping tells the system how to segment the data [citation:10].

Available Aggregate Functions

Acumatica provides several aggregate functions that can be applied to fields in the Results Grid. The function must match the data type of the field [citation:10]:

Function	Description	Best Used On
SUM	Returns the total of all values in the group	Numeric fields (Amounts, Quantities)
AVG	Returns the average of all non-null values in the group	Numeric fields
COUNT	Returns a count of all values in the group	ID fields, Reference numbers
MIN	Returns the minimum value in the group	Numeric or Date fields
MAX	Returns the maximum value in the group	Numeric or Date fields
STRINGAGG	Concatenates values in the group into a string	Text fields

⚠️ Important: Selecting SUM for a character data type (like a customer name) will cause a runtime error. Always ensure the aggregate function matches the field's data type [citation:10].

Example 1: Grouping by Customer

Let's start with a common scenario: grouping sales orders by customer to see total sales per customer. This is one of the most frequent use cases for aggregation in GIs [citation:8].

Step-by-Step Setup

Create a new GI based on the SOOrder table
Go to the Grouping tab – Add CustomerID (or BAccountR.AcctName for customer name) [citation:7]
Go to the Results Grid tab – Add the following fields:

Data Field	Aggregate Function	Result
CustomerID	None (grouped field)	One row per customer
OrderNbr	COUNT	Number of orders per customer
TotalAmount	SUM	Total sales per customer
TotalAmount	AVG	Average order value per customer

Why This Works

By adding CustomerID to the Grouping tab, the system creates one group per customer. All aggregate functions are then calculated within each customer group. The COUNT on OrderNbr counts how many orders exist for that customer, while the SUM on TotalAmount adds up all order amounts for that customer [citation:10].

Example 2: Aging Category Reports

A common requirement in manufacturing and finance is to group records by aging categories (e.g., 0-30 Days, 31-60 Days, etc.). This requires a calculated field and careful grouping configuration [citation:4].

Step-by-Step Setup

Create a calculated field for Aging Category using nested IIf logic [citation:4]:

=IIf(DateDiff('d',[AMProdItem.StartDate], Today()) <= 30, '0-30 Days',
IIf(DateDiff('d',[AMProdItem.StartDate], Today()) <= 60, '31-60 Days',
'Above 60 Days'))

Add the calculated field to the Results Grid – do not apply any aggregate function to it
Add the field you want to count (e.g., ProdOrdID) with Aggregate Function = COUNT
Go to the Grouping tab – Add only the Aging Category calculated field

⚠️ Important: Do not add ProdOrdID to the Grouping tab. If you do, each production order will appear as a separate row, breaking the aggregation [citation:4].

Understanding the Configuration

Grouping: Groups records by the Aging Category
COUNT on ProdOrdID: Counts the number of production orders within each aging category
Result: One row per aging category with the total number of orders in each bucket [citation:4]

Example 3: Multiple Level Grouping

You can group by multiple fields to create hierarchical summaries. For example, grouping by Customer and then by Order Status [citation:11].

Step-by-Step Setup

Go to the Grouping tab – Add fields in the desired hierarchy order:

CustomerID (first level)
Status (second level)

Go to the Results Grid tab – Add fields with aggregate functions:

Data Field	Aggregate Function	Result
CustomerID	None	Customer grouping
Status	None	Status grouping within customer
TotalAmount	SUM	Total by customer and status

💡 Pro Tip: The order of fields in the Grouping tab determines the hierarchy. The first field is the primary grouping, and subsequent fields create subgroups within each primary group [citation:11].

Example 4: Creating a Customer Summary Report

This example demonstrates how to create a comprehensive customer summary with key metrics. This is a common requirement for sales and finance teams [citation:7].

Setup

Create a copy of the Invoices and Memos (AR3010PL) GI [citation:7]
Group by: BAccountR.AcctName (Customer Name)
Add aggregate fields:

Data Field	Aggregate Function	Caption
BAccountR.AcctName	None	Customer Name
InvoiceNbr	COUNT	Number of Invoices
CuryDocBal	SUM	Total Amount
CuryDocBal	AVG	Average Invoice Amount

Results

This configuration produces a summary with one row per customer, showing the number of invoices, total amount, and average invoice amount for each customer. This is exactly what finance teams need for customer analysis [citation:7].

Total Aggregation (Summary at the Bottom)

In addition to grouping and aggregation, you can also display summary totals at the bottom of the GI using the Total Aggregate function [citation:1].

How to Use Total Aggregate

In the Results Grid tab, locate the field selector (highlighted with an arrow icon)
Select the field and choose the desired aggregate function (SUM, AVG, etc.)
The total will appear at the bottom of the GI

💡 Pro Tip: The Total Aggregate function is separate from the per-group aggregation. It calculates the total across all groups, displayed at the bottom of the GI [citation:1].

Performance Note: Disable Record Counts for Large GIs

For GIs with many records, you can improve loading performance by skipping the calculation of total record counts. Starting in Acumatica 2026 R1, you can select the Disable Record Counts and Totals check box in the Summary area of the GI [citation:2].

Advanced: Row Styles with Aggregated Values

A more advanced technique is applying row styles based on aggregated values. This requires understanding how formulas are evaluated in the context of grouping [citation:3].

The Problem

When you apply a row style formula, the calculation uses the first record in the group, not the aggregated value. This can cause incorrect highlighting [citation:3].

The Solution

To apply row styles based on aggregated values, you need to reference the formula's alias in the SQL generated by the GI. The system creates an alias for each calculated field in the format: [TableName]_Formula[UniqueID] [citation:3].

=IIF(PMTimeActivity_FormulaA4A7ACEFFCC1444DA018CE78DD1BFCA3>=70,'good', 
     IIF(PMTimeActivity_FormulaA4A7ACEFFCC1444DA018CE78DD1BFCA3>=40, 'orange40','bad'))

This formula uses the calculated column's alias (created by the system) rather than the original field, ensuring the row style evaluates the aggregated value [citation:3].

Common Issues and Solutions

❌ Aggregate Function Not Working

Cause: Missing grouping configuration. Aggregate calculations only work when grouping is defined [citation:1].

Solution: Add fields to the Grouping tab.

❌ Incorrect Counts in Grouped GIs

Cause: Duplicate records created by table joins [citation:4].

Solution: Review Relations to ensure you're not creating multiple rows per record.

❌ Calculated Field Not Available in Grouping

Cause: The field may be using aggregate functions in its formula [citation:4].

Solution: Ensure the formula does not contain aggregate functions. Delete and recreate the field if needed.

❌ SQL Error: Column Not in GROUP BY

Cause: A field in the Results Grid lacks an aggregate function and isn't in the Grouping tab [citation:5].

Solution: Either add the field to Grouping or apply an aggregate function.

❌ SUM Applied to Character Field

Cause: Aggregate function doesn't match the data type [citation:10].

Solution: Use MAX or MIN for character fields, or COUNT for text fields.

❌ Row Styles Not Working with Aggregates

Cause: Formula evaluates on the first record, not the aggregated value [citation:3].

Solution: Use the calculated field's SQL alias in the row style formula.

Best Practices

✅ Group Before Aggregating

Always define grouping before adding aggregate functions. The system needs to know how to segment the data [citation:1].

✅ Match Data Types

Ensure the aggregate function matches the field's data type. SUM on numeric, MAX on dates, COUNT on IDs [citation:10].

✅ Use Source Inquiries for Reusability

Create source inquiries for frequently used data layers and join them to multiple GIs [citation:6].

✅ Test with Sample Data

Preview your GI with sample data to verify grouping and aggregation work as expected before saving [citation:7].

✅ Consider Performance

For large GIs, use filters to limit data and consider disabling record counts for faster loading [citation:2][citation:9].

✅ Use InnerJoin When Possible

Inner joins are more efficient than left joins for most cases [citation:9].

Conclusion

Grouping and aggregation are essential techniques for creating meaningful reports and summaries in Acumatica Generic Inquiries. By mastering these features, you can:

Create customer summaries with total sales and average order values
Build aging reports for manufacturing and finance
Analyze data hierarchically with multiple grouping levels
Display summary totals at the bottom of your inquiries
Apply conditional formatting based on aggregated values

📌 Key Takeaway: Grouping and aggregation work together in Generic Inquiries. Define how to group records on the Grouping tab, then apply aggregate functions on the Results Grid to perform calculations within each group. The Total Aggregate function provides overall summary totals across all groups [citation:1][citation:10].

🔍 Further Reading: Explore the Acumatica Developer Network for additional examples and advanced techniques. Experiment with creating your own GIs using the examples in this article to build confidence before applying them in production.