How to Create a BAQ in Epicor: Build, Run, Search, Export to Excel and Publish
how to create baq in epicor
Also searched as
- how to create a baq in epicor 10
- epicor create baq report
- how to run a baq in epicor
- epicor baq to excel
- epicor baq search
- make baq a dashboard epicor kinetic
- epicor baq table
Short answer
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.
Applies to: Epicor Kinetic and 10.x. Tab and menu names are close across releases but may differ slightly.
How to create a BAQ in Epicor
- 1Search the menu for Query Builder (also under the Business Activity Query / Executive Analysis area).
- 2Click New, enter a Query ID such as ZOpenOrdersByCustomer and a Description.
- 3On the Query tab, add the tables you need, for example OrderHed and OrderDtl. Check the Table Relations so the join matches (OrderHed to OrderDtl on Company and OrderNum).
- 4Open Display Fields, tick the columns you want and set labels. Check any needed aggregate on the Group By tab.
- 5Open Table Criteria and add filters, for example OpenOrder = true, or a parameter if the user should choose a value.
- 6Use the Analyze or Test action to check the SQL and the result. Fix any join problems.
- 7Save, then Publish if the BAQ is to be used in dashboards or with a different company, and give security access.
Choosing tables and joins
Start from the table that is the row you want, not the one that has the most columns. If you report on order lines, start from OrderDtl and join OrderHed for header data. Always join on Company plus the key. A missing Company join is the usual cause of duplicate rows in multi-company databases.
The default join is inner, which drops rows with no match. Change the relation to a left outer join where the second table is optional, for example parts that may have no inventory record. Use the BAQ search or a database diagram to find tables if you do not know the name. Epicor Data Dictionary Viewer helps.
Running, searching and exporting
Once saved, run the BAQ from the Execute or the Analysis tab in Query Builder. Searches in the grid help for small results. For ad hoc use, open the BAQ in a dashboard or the Business Activity Query menu and filter there. Results export to Excel from the grid with the Export action, or through the Excel integration if it is installed on your system.
Large results are slow in the grid and in Excel. Add criteria first, use Top N for samples, and see the BAQ performance page if the query is slow.
Publishing as a dashboard or report
To show a BAQ to users, build a dashboard around it in Application Studio or use the dashboard generator available in Query Builder in your release. Add the dashboard to a menu and grant security. See the Epicor dashboard page for the steps.
To print it, create a BAQ report from the BAQ and give it parameters. See the BAQ report parameter page. Test with a normal user account, because BAQ access is controlled by security groups and a developer account can hide permission problems.
Try it here, free
Epicor BAQ Query Template Generator
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
- !Forgetting the Company join and getting duplicate lines in multi-company systems.
- !Using an inner join where rows without a match should remain.
- !Building one giant BAQ for everything. Smaller BAQs are faster and reusable.
- !Testing only as an admin and finding users cannot run it.
- !Returning all rows to Excel instead of filtering first.
How an ERP-grounded AI assistant handles this
An assistant grounded in the Epicor schema can turn a plain request such as open order lines by customer into a proposed table list, joins and criteria for a developer to check in Query Builder, and can explain an existing BAQ in plain language. It is read-only by default. ERPray works this way over live ERP data.
Frequently asked questions
How do I run a BAQ in Epicor?
Open it in Query Builder and use the Execute or Analysis tab, or open it through a dashboard or a Business Activity Query menu item. Users need security access to the BAQ.
How do I export a BAQ to Excel?
Run the BAQ and use the grid Export action to Excel. If your site uses the Epicor Excel add-in, you can also pull the BAQ into a sheet. Filter first for large result sets.
How do I make a BAQ into a dashboard?
Use the dashboard generation action in Query Builder if your release offers it, or build a dashboard in Application Studio bound to the BAQ. Add a menu item and security.
Why does my BAQ return duplicate rows?
Most often a join is missing Company, or you joined a table that has many rows per key. Check the relations and add the missing key fields.
Related
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-toFixing 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.
How-toHow to build a Kinetic homepage dashboard in Epicor
Kinetic homepage dashboards are built from Active Homepage Maintenance: create a BAQ that returns the metric or list you want, add a KPI or List widget referencing that BAQ, arrange widgets on the homepage layout, and assign the homepage to a role or company so the right users see it.
How-toEpicor BAQ Cookbook: Group By, Union, Distinct, Null Tests, Pivot and Subqueries
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.
Error fixFixing 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-toEpicor 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 ERPAI 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.
Keep exploring
Free tools
- Epicor BAQ Query Template GeneratorFill 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.
- Epicor BAQ Performance Tuning ChecklistWork through a practical list of checks when a BAQ, dashboard or BPM lookup runs slowly in Epicor Kinetic.
- Epicor BAQ OData REST URL BuilderBuilds Epicor REST v2 OData URLs and curl commands for BAQs and business object services, with API key header and filters.
ERP fixes and how-tos
- How to Create a BAQ in Epicor KineticCreate a BAQ in Epicor Kinetic Query Builder: pick tables, set joins, display fields, criteria, calculated fields, test, and use it in dashboards and reports.
- Fixing a Slow Epicor BAQ (Business Activity Query)A slow Business Activity Query in Epicor is almost always an execution plan problem, not an Epicor problem. Here is how to find the real cause and fix it without rewriting the BAQ from scratch.
- How to Add a Calculated Field in an Epicor BAQAdd a calculated field to an Epicor Kinetic BAQ: syntax, CASE, NULL and divide by zero guards, date math and when to use a subquery instead. Working examples.
AI on your ERP
- Private AI for Epicor Kinetic Cloud, Outside the Multi-Tenant BoundaryRun a private LLM on Epicor Kinetic private cloud or Kinetic Cloud data via BAQs, REST v2 and Data Fabric, keeping sensitive manufacturing data off shared AI.
- AI for Epicor Kinetic Job Shops: Quote History, Job Variance and Schedule RiskPrivate AI for Epicor Kinetic job shops: quote-to-job history, JobHead and JobOper variance, subcontract delays and schedule risk, via REST and BAQs.
Integrations
- How to connect Power BI to Epicor KineticConnect Power BI to Epicor Kinetic using BAQ OData feeds, a data warehouse or a SQL replica, with an on-prem gateway, key dashboards and natural-language Q&A.
- How to build an MCP server for Epicor KineticDesign a Model Context Protocol server for Epicor Kinetic: which read tools to expose, BAQ and REST v2 mapping, auth, stdio vs HTTP transport and audit logging.