How to Build Advanced Epicor BAQ Reports

Overview

Business Activity Queries (BAQs) are one of the most powerful reporting tools available in Epicor Kinetic. Advanced BAQs allow organizations to combine multiple data sources, create calculated fields, build executive dashboards, and generate operational reports without direct database access. For BAQ performance questions, search the EpiUsers BAQ best practices

Well-designed BAQs provide real-time visibility into sales, inventory, purchasing, production, finance, and customer activity. They also serve as the foundation for dashboards, BAQ reports, quick searches, BPMs, and integrations.

Start with a Simple Query Structure

A common mistake is building highly complex BAQs from the beginning. Experienced Epicor developers recommend starting with a simple query, validating results, and then gradually introducing joins, calculated fields, and subqueries as needed.

Building incrementally makes troubleshooting easier and helps identify performance bottlenecks before they become difficult to resolve.

Use Parameters for Flexible Reporting

BAQ Parameters allow users to dynamically filter results without modifying the query. Instead of creating multiple versions of the same report, a single parameterized BAQ can support different customers, parts, date ranges, warehouses, or job numbers.

• Customer parameters
• Part number parameters
• Date range filters
• Warehouse selection
• Salesperson filtering

Parameterized BAQs improve maintainability and reduce duplicate queries throughout the system.

Leverage Calculated Fields

Calculated fields transform raw ERP data into meaningful business metrics. Rather than exporting data to Excel for analysis, calculations can be performed directly inside the BAQ.

Examples include:

• Gross margin percentages
• Inventory aging calculations
• Days late calculations
• Open order values
• Production efficiency metrics

Calculated fields are one of the most valuable features available within the BAQ Designer.

Use Subqueries Carefully

Subqueries allow developers to perform advanced filtering, aggregation, and conditional reporting. While powerful, excessive use of nested subqueries can negatively impact performance.

Whenever possible:

• Use joins before creating subqueries.
• Limit deeply nested query structures.
• Test performance with realistic data volumes.
• Use aggregate subqueries only when necessary.

Many Epicor developers prefer multiple focused BAQs connected through dashboard publish-and-subscribe functionality instead of creating one extremely large query.

Build Dashboard-Friendly BAQs

Most advanced BAQs eventually become dashboards. To improve dashboard performance:

• Select only required fields.
• Apply filters at the BAQ level.
• Use parameters whenever possible.
• Avoid unnecessary calculated columns.
• Reduce large result sets.

Optimizing the BAQ itself is often more important than optimizing the dashboard.

Advanced Reporting Examples

• Open Sales Order Dashboard
• Production Efficiency Reporting
• Inventory Aging Analysis
• Customer Profitability Reports
• Late Purchase Order Tracking
• Supplier Performance Dashboards
• MRP Recommendation Analysis
• Executive KPI Scorecards

Performance Best Practices

• Return only required fields.
• Avoid unnecessary joins.
• Use indexes effectively through proper table selection.
• Filter data as early as possible.
• Test with production-sized datasets.
• Review query execution times regularly.

Many dashboard performance issues originate from inefficient BAQs rather than the dashboard itself. Proper query design can significantly improve responsiveness.