How-toEpicor KineticBAQ / SQL Patterns

Epicor BAQ Cookbook: Group By, Union, Distinct, Null Tests, Pivot and Subqueries

Question
epicor baq group by

Also searched as

  • epicor baq union
  • epicor baq union all
  • epicor baq distinct
  • epicor baq is null
  • epicor baq contains
  • epicor baq pivot
  • epicor baq running total
  • epicor baq subquery types

Short answer

Most BAQ patterns map to plain SQL: Group By with aggregates, Combined Queries for union, a Distinct setting, null tests in criteria or expressions, and subqueries for per-row lookups. Pivot and running totals have no single switch, so build them with conditional aggregates and subqueries.

Applies to: Epicor Kinetic and 10.x Query Builder. Option names vary a little between releases, and expression support depends on the SQL Server version underneath.

How to apply each pattern in Query Builder

  1. 1Group By: on Display Fields mark which columns are grouped, then on the Group By tab set aggregate (Sum, Count, Min, Max, Avg) on the value columns.
  2. 2Union: create the second query as a separate BAQ with the same column count and types, then add it on the Combined Queries tab; choose union or union all.
  3. 3Distinct: turn on the Distinct option in the query settings, or group by every selected column.
  4. 4Null tests: in Table Criteria use the null or not null condition, or a calculated field that wraps the column with ISNULL.
  5. 5Contains: use the contains or like condition, or a calculated field with the T-SQL LIKE operator.
  6. 6Subqueries: add on the Subqueries tab, then link by key to the main query and use as a join, a column or an exists test.
  7. 7Run Analyze to see the generated SQL and compare with the pattern below.

Group by, distinct and null tests

Group By in a BAQ is standard SQL. Every non-aggregated display column must be in the group. Total order value by customer is a group on CustID with Sum of the line total. Distinct removes duplicate output rows and is easiest when you want a unique list, such as the list of parts that were shipped last month.

For null, remember SQL treats null differently from empty text. A criteria of equals blank does not match null. Use is null or is not null, or wrap the column in ISNULL(column, 0) in a calculated field so downstream math works. Left joins create nulls from missing matches, so plan for them.

SELECT c.CustID, SUM(d.DocExtPriceDtl) AS Total
FROM Erp.OrderDtl d
JOIN Erp.OrderHed h ON h.Company = d.Company AND h.OrderNum = d.OrderNum
JOIN Erp.Customer c ON c.Company = h.Company AND c.CustNum = h.CustNum
GROUP BY c.CustID

Union and union all

Use the Combined Queries feature when two sources have the same shape, for example open sales order demand and open job demand into one demand list. Union removes duplicate rows and sorts, union all keeps every row and is faster. Use union all unless you need to deduplicate.

Columns match by position, so keep order and types identical. Add a literal column such as Source to tell which query each row came from. Sorting is applied to the combined result.

Contains, pivot, running totals and subqueries

Contains is a LIKE on percent signs, which cannot use an index, so combine it with other filters. A pivot, such as monthly sales in columns, is built with conditional aggregates: SUM(CASE WHEN month = 1 THEN amount ELSE 0 END) per column, then grouped by customer. Dashboards with a pivot grid can also pivot at display time.

A running total is not a built-in. Common approaches are a correlated subquery that sums rows up to the current date, or doing the running sum in the dashboard grid. Window functions may not be accepted in the expression editor in some releases, so test before building on them. Subquery types let you return a single value per row (a top 1 lookup), a set for in or exists tests, or an inline table to join. Keep them narrow and indexed on the key.

SELECT h.CustNum,
  SUM(CASE WHEN MONTH(h.OrderDate) = 1 THEN d.DocExtPriceDtl ELSE 0 END) AS Jan,
  SUM(CASE WHEN MONTH(h.OrderDate) = 2 THEN d.DocExtPriceDtl ELSE 0 END) AS Feb
FROM Erp.OrderHed h JOIN Erp.OrderDtl d ON d.Company = h.Company AND d.OrderNum = h.OrderNum
GROUP BY h.CustNum

Try it here, free

Epicor BAQ Query Template Generator

Open full tool

Fill in company, days back and status filters to get a starter SQL for an open jobs BAQ, plus a design checklist for the Query Builder.

Common pitfalls

  • !Using union when union all would do, which adds a sort and removes valid duplicate rows.
  • !Comparing to an empty string when the data is null.
  • !Selecting a column not in the group by.
  • !Using contains on a large table without any other filter.
  • !Putting a running total subquery over millions of rows without a date filter.

How an ERP-grounded AI assistant handles this

Writing these patterns is where an assistant helps most: describe the report and it returns the BAQ structure and the generated SQL for a developer to check, using your real table and field names. It runs read-only and shows the query. On-prem models keep your schema private.

See ERPray

Frequently asked questions

How do I do a union in an Epicor BAQ?

Build both queries as BAQs with the same columns in the same order and type, then combine them on the Combined Queries tab. Pick union all unless you need duplicates removed.

How do I test for null in a BAQ?

Use an is null or is not null condition in Table Criteria, or wrap the field in ISNULL in a calculated field. Equals blank does not match null.

Can a BAQ do a pivot?

There is no pivot switch. Use SUM(CASE WHEN ...) per column with a group by, or pivot in the dashboard grid at display time.

How do I get a running total in a BAQ?

Use a correlated subquery that sums values up to the current row, or compute it in the dashboard. Add date filters to keep it fast.

Related

How-to

How to Create a BAQ in Epicor: Build, Run, Search, Export to Excel and Publish

A BAQ (Business Activity Query) is a saved SQL-style query built in Epicor's Query Builder. Open Query Builder, name the query, add tables and join them, choose display fields, add criteria, test it, then run it from the Execute or Analysis tabs, export to Excel, and publish it for use in a dashboard or report.

How-to

How to Add a Calculated Field in an Epicor BAQ

In Epicor Query Builder, open the Phrase Build or Calculated Fields area of the Analyze tab or Display Fields, add a new calculated field with a name and a data type, and write a SQL expression that references columns as Table_Column. Guard against NULL and zero divisors, and use a subquery when the value needs its own aggregation.

How-to

Fixing a Slow Epicor BAQ (Business Activity Query)

A slow BAQ in Epicor is usually caused by unindexed join columns, a subquery or calculated field forcing a table scan, or the BAQ pulling far more rows than the dashboard actually displays before filtering client-side. Fix it by reading the SQL Server execution plan Epicor generates, moving filters into the BAQ criteria instead of the dashboard filter panel, and replacing subqueries with joins where possible.

Advanced

Epicor BAQ Recursive BOM Join on PartMtl, Previous Operation Complete and Stale Headers

A BAQ cannot run a recursive query, so a multi-level bill of materials is built by joining PartMtl to itself once per level, or by using a function or the BOM explosion tools for unlimited depth. Previous operation complete uses a subquery against JobOper. Stale column headers usually come from the saved dashboard layout, not the BAQ.

Error fix

Fixing Epicor BusinessObjectException: BPM Directive Errors

A BusinessObjectException that says "A Business Process Management (BPM) directive has raised the following error" means a directive on that business object stopped the transaction, either deliberately via a Raise Exception widget or accidentally via an unhandled .NET error in Custom Code. Expand the InnerException on the error dialog to see the real message and the directive name, then open BPM Designer for that object and method to find the widget that fired.

How-to

Epicor MRP Runs but Generates No Suggestions

When Epicor's MRP process completes without a job or purchase suggestion for a part you expect one for, the cause is almost always the part's own configuration (Part Class Type, Make Direct, Non-MRP flag, planning Time Fence) or the demand not being linked in a way MRP recognizes, not a defect in the MRP engine. Work through the part's Planning tab, its safety stock and lead time setup, and the demand source (sales order line status, job material requirement) before assuming the run itself failed.

AI for ERP

AI for Epicor Kinetic, Beyond What Prism Covers

Add AI to Epicor Kinetic beyond Prism: private LLM over BAQs, BPM data, and REST v2, on-prem or private cloud, with honest guidance on when Prism already covers you.

Stuck on Epicor Kinetic?

Talk to engineers who work inside Epicor Kinetic every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Guides

AI on your ERP

Integrations

Comparisons

Software

TermsEpicor BAQ (Business Activity Query)Epicor BPM (Business Process Management)