# BAQ fundamentals, and the calculated-field mistakes that make totals wrong > How Kinetic Business Activity Queries are built, why query grain decides whether totals are right and the calculated-field mistakes behind wrong BAQ reports. Source: https://docs.lumina-erp.com/epicor-kinetic/baq-fundamentals/ **In short.** A BAQ total is only right if every value you sum lives at the grain of the query. Most wrong totals come from joins that repeat parent values, grouping that drifts and formulas built on the wrong quantity. A Business Activity Query, or BAQ, is the built-in query designer in Kinetic software. It is how most dashboards, many reports, grid views and a good share of REST integrations get their data. BAQs are easy to start and easy to get subtly wrong. A query that returns plausible rows can still produce a total that is off by a factor of 10. We start with the fundamentals that matter for correctness. Then we work through the calculated-field mistakes we find most often when a BAQ report "stopped being trusted." ## The mistakes at a glance Each mistake below has its own section later on the page. | Mistake | What you see | Fix | |---|---|---| | 1. Summing a parent value after a join | A total too large by roughly the average number of lines per order | Sum at the right level, or reduce the child to one row per parent in a subquery | | 2. Aggregating in the wrong subquery | More rows than expected, because the grouping moved to a finer grain | Pre-aggregate in an inner subquery, join descriptions at the outer level | | 3. Deriving a quantity from the wrong fields | Right on most rows, wrong on a few | Requirement minus fulfilled | | 4. NULLs from outer joins | Rows drop out of a sum with no error | `ISNULL(field, 0)` where zero is the right meaning | | 5. Mixing currencies | Totals that add different currencies together | Use the base-currency field, or convert explicitly | | 6. Calculated field types and precision | Truncated decimals, cut-off large values | Decimal type with enough precision, and multiply before dividing | | 7. Counting rows instead of things | An order count that is a line count | Count distinct keys, or aggregate at the right grain first | ## What a BAQ is made of A BAQ is a SQL query assembled through a designer rather than typed by hand: | Part | What it does | |---|---| | Tables | Come from the Kinetic schema, such as order header, order line and order release | | Joins | Connect the tables, inner or outer | | Criteria | Filter rows, on a table or on the whole query | | Display fields | The columns returned, including calculated fields defined by an expression | | Grouping and aggregation | Produce totals rather than rows | | Subqueries | Queries nested inside the BAQ and joined like tables | The designer can show you a SQL view of the query. Read it, because it is often clearer than the designer panels when something is wrong. :::note[The SQL view is a reference] Treat the SQL view as a reference rather than the exact statement the server runs, and confirm behavior by testing the BAQ against rows you understand. ::: ## Grain decides correctness Every query has a **grain**: what one row represents. One row per order, one row per line, one row per release. The grain is set by the most detailed table you join. Grain matters because any value that lives at a higher level is repeated on every lower-level row. Join the order header to its lines and the header's total appears once per line. Join the lines to their releases and each line's values appear once per release. Nothing about the rows looks wrong. The trouble starts when you add them up. Before building a BAQ, write down in one sentence what a row should represent. Then check that every value you plan to sum lives at that level. ## Summing a parent value after a join The most common wrong total. A query joins order headers to lines to filter on something at line level, then sums a header-level amount. Every order's total is counted once per line. The symptom is a total that is too large by roughly the average number of lines per order. On a spot check, the order-level numbers look right, which makes it confusing. To fix it, aggregate at the right level. One way is to sum the line-level amount instead of the header amount. The other is to build a subquery that reduces the child table to one row per parent (for example, "orders that have at least one line matching the filter") and join that to the header. ## Aggregating in the wrong subquery When you group a query, every non-aggregated display field joins the grouping. Add a harmless-looking descriptive field from a child table and the grouping moves to a finer grain. Your "one row per customer" becomes "one row per customer per part." To fix it, decide the grain of each subquery explicitly. Pre-aggregate detail in an inner subquery that groups by the key you need, then join the result to the descriptive tables at the outer level. ## Deriving a quantity from the wrong fields An open quantity is what is still required after what has already been fulfilled. The mistake is a calculated field that derives it from some other quantity that equals the requirement on most rows. With invented numbers, say a line requires 100 units and 60 have been fulfilled, so 40 are open. A formula that subtracts 60 from a different field returns 40 on every row where that field also happens to be 100. On any row where it is not, the formula returns the wrong answer, and nothing in the result flags it. Because the formula is right on most rows, spot checks pass. To fix it, derive an open amount from **the requirement minus what has been fulfilled**, never from an intermediate quantity. Then test the formula on rows where the other quantities are unusual, as well as on typical rows. ## NULLs from outer joins With an outer join, rows without a match have NULL in every column from the joined table. In SQL, arithmetic involving NULL returns NULL, so a calculated field like `A - B` returns nothing when `B` is missing, and that row drops out of a sum without any error. To fix it, wrap values from outer-joined tables in `ISNULL(field, 0)` inside calculated fields when zero is the correct meaning of "no match." Decide deliberately, because sometimes NULL is the honest answer. :::caution[A filter can turn an outer join back into an inner join] A criterion placed on an outer-joined table in the wrong place removes exactly the rows you used an outer join to keep. If an outer join is not returning unmatched rows, look for a filter on the joined table. ::: ## Mixing currencies Many Kinetic amount fields come in pairs: one in the document currency (commonly prefixed `Doc`) and one in the company base currency. Check which one a field holds in your version before you sum it. A BAQ that sums document-currency amounts across orders in several currencies adds euros to dollars. For totals across documents, use the base-currency field, or convert explicitly. Label columns so readers know which currency they are looking at. ## Calculated field types and precision A calculated field has a declared data type and format. If the type is an integer, decimal results are truncated. If the format has too few digits, large values can be cut off or shown in ways that are easy to misread. Integer division can also truncate before you multiply. To fix it, declare calculated numeric fields as decimal with enough precision for your largest real value, and multiply before dividing where the order of operations allows it. ## Counting rows instead of things `COUNT` of rows at line grain is not the number of orders. If the question is "how many orders," count distinct order numbers, or aggregate in a subquery at order grain first. ## A checklist before trusting a BAQ total Work through these steps before anyone relies on a BAQ total. 1. **State the grain.** One sentence: one row equals what? 2. **Check every summed field lives at that grain.** 3. **Read the generated SQL.** Look at the joins and where criteria landed. 4. **Test outer joins.** Confirm unmatched rows appear. 5. **Look at the extremes.** Sort by the calculated value and read the top 10 rows. Absurd totals are usually a handful of absurd rows. 6. **Reconcile one number** to a screen or standard report the business already trusts. 7. **Check currency and units** in every amount and quantity column. ## Performance habits that keep BAQs fast Correctness first, but a few habits keep BAQs fast: - Filter as early as possible, on indexed keys such as company, dates and IDs. - Return only the columns you need. Wide BAQs are slow to render in grids and slow over REST. - Pre-aggregate in subqueries rather than returning detail and summing in a dashboard. - Be careful with calculated fields in criteria. Filtering on an expression can prevent the database from using an index. ## Where BAQs go next Once a BAQ is right, it becomes a building block. Dashboards display it, reports use it as a data source and REST callers can run it and read the result, which we cover in [Calling the Kinetic REST v2 API](/epicor-kinetic/rest-v2-api/). :::tip[Replacing an old Crystal report?] If your BAQ exists to replace an old Crystal report, confirm what the old report computed, including its known bugs, before you rebuild it. ::: ## Sources - [Business Activity Queries (Epicor)](https://www.epicor.com/en/products/enterprise-resource-planning-erp/epicor-kinetic/tools-and-technology/business-activity-queries/) - [Edit query phrase in a BAQ (EpiUsers forum, answer by an Epicor employee)](https://www.epiusers.help/t/edit-query-phrase-in-a-baq/78785) --- Epicor, Prophet 21, P21 and DynaChange are trademarks or registered trademarks of Epicor Software Corporation registered in the United States and other countries. Kinetic is a trademark of Epicor Software Corporation. Lumina ERP is an independent consultancy and is not affiliated with, sponsored by or endorsed by Epicor.