In this second installment of our Epicor BAQ (Business Activity Query) series, we delve into more advanced features, including how to effectively use subqueries, and manage subquery options. These features allow for more complex and powerful queries that can provide deeper insights from your data.
If you haven’t, go check out the first chapter of this series!.
1. Subquery Criteria
Subqueries are one of the most powerful tools in BAQ, allowing you to perform nested queries within your main query. The Subquery Criteria feature helps you filter results within a subquery to narrow down your data even further.
How to Set Subquery Criteria:
- Navigate to the SubQuery Criteria sheet.
- Add conditions to your subquery that work similarly to adding filters in the main query. For example, you can filter results by customer, product, or date ranges.
- The conditions you set here will be applied in the WHERE clause of your SQL query or in the HAVING clause if you are dealing with aggregated data.
This step ensures that only the most relevant data is pulled into your BAQ from each subquery.
2. Creating a Subquery in Epicor BAQ
Once your main query is set, you may need to create a subquery to extract more detailed data. This involves defining relationships between tables and filtering specific data.
Steps to Create a New Subquery:
- Add a New Subquery: Navigate to the SubQuery List and add a new subquery. Define its role (e.g., TopLevel, CTE).
- Select Relevant Tables: Choose the necessary tables and fields.
- Set Criteria: Filter the data as needed within the subquery.
- Combine Results: Use options like UNION or INTERSECT for results integration.
This approach refines your data extraction, making your BAQ more powerful.
3. Subquery Options
The Subquery Options sheet allows you to control how each subquery interacts with others within the BAQ. This is where you can set the sequence of subqueries, their types, and how they combine their results.
Setting Up Subquery Options:
- Go to the SubQuery Options sheet in the Query Builder.
- Define the main subquery and set its type (e.g., TopLevel, InnerSubQuery, CTE).
- Set the result set rows to determine how data is displayed, such as SELECT ALL, DISTINCT, or using TOP to limit the number of rows returned.
These options give you fine control over the structure of your BAQ, allowing for complex data retrieval scenarios.
4. Subquery List
Finally, the Subquery List provides a comprehensive view of all the subqueries in your BAQ. It helps you manage the sequence and properties of each subquery, ensuring they work together to produce the desired results.
Managing the Subquery List:
- Access the SubQuery List sheet to see all the subqueries in your BAQ.
- Create, remove, or reorder subqueries as needed.
- Check the sequence values to ensure the correct order of execution.
This overview is crucial for maintaining complex queries, ensuring all parts of your BAQ are working harmoniously.
Conclusion
By mastering subqueries and advanced options in BAQ, you can unlock new levels of data analysis and reporting in Epicor. These tools allow for greater flexibility and precision, helping you extract exactly the data you need. In the next post, we’ll explore more features that will further enhance your ability to create powerful BAQs. Be sure to stay tune at our news section for next episodes!.