Epicor BAQ: Mastering Business Activity Query in Epicor

17/10/2024

Epicor ERP solutions are built to store large volumes of data. However, this data is not useful unless it is easily accessible. Epicor BAQ (Business Activity Queries) are an invaluable tool for accessing and working with data from across the Epicor system, allowing organizations to leverage the information for decision-making purposes.

Browse our comprehensive guide to BAQ in Epicor to understand the key features and purpose of BAQs and how to utilize each tool effectively. For more help, do not hesitate to contact TeccWeb’s Epicor experts.

What is BAQ in Epicor?

Business Activity Query in Epicor, commonly called BAQ, allows you to retrieve and update data within your ERP. Database systems use query languages to access and modify data. Within Epicor, BAQs are built within the Business Activity Query Designer, a feature-rich environment where queries can be developed and tested.

Importance of BAQ in Data Management and Reporting

BAQs are helpful for understanding data and relationships within the Epicor system, including as a dashboard component or as the base data for custom reports. BAQs can be referenced within customizations, and queries can be set up within BPMs to retrieve or update records.

BAQ Tools And Features for Managing Queries

Drag and drop business activity query design tools for adding and linking tables make building BAQs accessible to non-programmers. More technical users can also leverage advanced tools and features such as calculated fields and various subquery types.

Building BAQs with Query Designer

When building BAQs, related database tables link automatically when added to the query, allowing newer users not yet wholly familiar with the Epicor database structure to successfully find data and link it appropriately to return the results desired. Tables that aren’t formally linked in the database structure can also be connected, allowing users the flexibility of returning data for specific reporting purposes.

The Query Designer clearly lists all available fields that can be accessed through each individual table, and subqueries can be developed and defined through specific options.

Epicor BAQ Report Designer

The Report Designer allows users to design and format reports for printing, sharing and presentations. It has many tools for adding headers, footers, charts and other visual elements to the report.

Automation Tools

Epicor’s built-in automation tools, such as Task Schedules, can trigger the execution of BAQs and the generation of recurring reports without manual intervention. It is especially handy to automate end-of-month processes.

Epicor BAQ Parameters

Users can set conditions and filters to narrow down the query results. Parameters are added to allow dynamic input at runtime, making the queries more flexible. There are 4 types of parameters:

• Text parameters (names, codes, etc.)
• Date parameters for time-based queries (start date and end date, compare dates, date aggregation, oldest date, etc.)
• Numeric parameters for quantities or amounts
• BAQ parameters dropdown: User input parameter configurable to provide a list of predefined options from which the user can choose.

Epicor BAQ Subquery

Subqueries are queries within queries. Using subqueries allows the user to create complex queries, which is particularly useful for retrieving data dependent on another query’s result. There are three types of subqueries, known as

• Single-value subqueries: Usually a count or maximum value that is then used in the main query
• Multi-value subqueries: Multiple rows or columns matched against data in the main query
• Correlated subqueries: Columns in the outer query are evaluated once for each row processed by the outer query

Using subqueries allows for enhanced data filtering based on aggregates, conditional filtering, existence checks and dynamic filtering.

Updatable BAQ

Updatable BAQs can also be created through the same designer. These queries can be set up to change or add data to various records in the database and allow the development of an Updatable Dashboard (a user-friendly view of the data).  

Analyze Tab

The Analyze tab within the Designer makes validating and running a query possible. In the case of an updateable BAQ, it is possible to test the update function before rolling out the results to end users. 

New BAQ Feature in Epicor Kinetic Release 2023.2

Starting in Kinetic release 2023.2, a BAQ is available as a Data Source within other BAQs. This update makes it possible to reuse one query and return specific results within another query, which can save time and effort when building more complex data relationships that have logic in common. 

Expert Tips and Best Practices

1. Understanding what data you need and how it is structured in the Epicor database is crucial to choosing the right tables and fields.
2. Planning your query logic is necessary to ensure the accuracy of your results and the efficiency of your BAQ.
3. Using descriptive names for your BAQs and parameters helps others (and yourself later) quickly understand the purpose of the query.
4. Leveraging subqueries when you need to retrieve data based on criteria that depend on another query is especially useful for nested data conditions or when dealing with aggregate functions.
5. Building and testing your BAQs incrementally ensures accuracy and performance. Start with simple queries and gradually add complexity.
6. Using too many joins or subqueries can slow down query execution. Instead, simplify where possible, or consider using indexing if supported by your database.
7. Incorporating parameters into your BAQs enables dynamic filtering and makes your queries more responsive to different situations.

Importing BAQ from External Data Sources

• Use Epicor Import BAQ functionality to bring in external BAQs.
• Validate the source data
• Carefully map external fields to the correct Epicor tables and fields to prevent data mismatches.
• Run the BAQ to verify that it retrieves and displays data correctly within your Epicor system
• Use staging tables to review and clean the imported data before integrating it into your live system
• Establish rules for handling conflicts (duplicates, null, etc.) to ensure data consistency
• Use scripts or automated tools to resolve common data issues

Contact TeccWeb for Further Assistance with Epicor BAQ

Business Activity Queries are a powerful tool which can pull data from across your system and display it in a format that works to benefit your organization. BAQs give great insight into the data behind your business and can be used to build Dashboards and BAQ reports that will be valued by users across the organization. 

If you have any questions about Epicor BAQ, the Epicor experts at TeccWeb will gladly help you.

Daniel Sauvlet