# How to Add a Calculated Field in an Epicor BAQ > Source: https://netray.co/erp-help/epicor-baq-calculated-field-expression-how-to > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer 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. ## Kind how-to ## System epicor ## Erp Epicor Kinetic ## Module Business Activity Query (BAQ) / Query Builder ## Query how to add a calculated field in an Epicor BAQ ## Alternate Queries - Epicor BAQ calculated field case statement - Epicor Query Builder calculated field syntax - Epicor BAQ subquery field - Epicor BAQ divide by zero calculated field ## Applies To Epicor Kinetic 2021.x to current and Epicor ERP 10.x Business Activity Queries; expressions are Transact-SQL on SQL Server ## Steps ### Build a calculated field **Items:** - Open Business Activity Query Maintenance (Query Builder) and load or create the BAQ. Add the tables you need on the Query Builder tab. - Go to the Display Fields tab and use the Calculated Fields sub-tab. Click New to add a field. - Give it a name and data type. The type must match what the expression returns (decimal, nvarchar, date, bit) or the grid will show errors. - Write the expression in Transact-SQL. Refer to a column as Table_Column, for example JobHead.JobNum is written JobHead.JobNum in the expression editor, with the alias prefix you gave the table. - Wrap division with a guard: CASE WHEN qty = 0 THEN 0 ELSE total / qty END. - Wrap nullable columns with ISNULL or COALESCE so a missing value does not make the whole result NULL. - Add the field to the display, save, and run Test Query. Compare a few rows to what you expected by hand. - Publish the BAQ as a dashboard source or as a BAQ report once the numbers are right. ## Sections ### Expression syntax you will use most **Body:** A calculated field is evaluated per output row as part of the generated SELECT. Anything valid in a SQL Server expression works: arithmetic, CASE, string functions, date functions, CAST and CONVERT. What does not work is an aggregate that needs a GROUP BY the query does not have. The most common examples are a margin percent, an age in days, a concatenated label, or a status text from a code. Use CASE for the last one, so one place defines the label for every dashboard that uses the BAQ. Name your tables with clear aliases in Query Builder before writing expressions. Expressions refer to the alias, so a table named JobHead_1 in a self-join is easy to mix up if you leave defaults. **Code:** - -- Margin percent with a divide by zero guard CASE WHEN ISNULL(OrderDtl.ExtPriceDtl, 0) = 0 THEN 0 ELSE (OrderDtl.ExtPriceDtl - ISNULL(OrderDtl.TotalCost, 0)) / OrderDtl.ExtPriceDtl * 100 END - -- Age of an open job in days DATEDIFF(day, JobHead.StartDate, GETDATE()) - -- Readable status from flags CASE WHEN JobHead.JobClosed = 1 THEN 'Closed' WHEN JobHead.JobReleased = 1 THEN 'Released' WHEN JobHead.JobFirm = 1 THEN 'Firm' ELSE 'Unfirm' END ### NULLs and data types, where most errors come from **Body:** If one column in an arithmetic expression is NULL, the whole expression is NULL. Outer joins make this common, because a missing joined row gives NULLs for every column from that table. Wrap such columns in ISNULL or COALESCE with a sensible default. The data type you choose for the calculated field must match the result. A decimal expression declared as an integer truncates silently in some views, and a text expression declared as a date throws a conversion error at run time. When the grid shows the red error cell, check the type first. Be careful with integer division. In SQL Server, 5 / 2 is 2 when both are integers. Multiply by 1.0 or cast one side to decimal when you want a fraction. ### When to use a subquery instead of a calculated field **Body:** A calculated field cannot aggregate other rows. If you need total received quantity per PO line, or the last receipt date per part, build a Subquery in the BAQ that groups, then join it back on its keys. This is cleaner, faster, and easier to read than correlated SQL in an expression. Correlated subselects inside a calculated field run once per row and are a classic reason for a BAQ that works with 100 rows and times out with 100,000. If performance is the concern, see the BAQ performance guidance and move the logic to a grouped subquery. ### Testing and sharing **Body:** Test with edge cases: a zero quantity, a NULL cost, a closed job, a date in the future. Add these rows to your test filter or check them in SQL Server Management Studio against the same tables. Once the BAQ is correct, name it with your site's convention and set security so only the right groups can run it. Calculated fields are part of the BAQ definition, so exporting the BAQ carries them to another environment. ## Pitfalls - Dividing without a zero guard, so one row with zero quantity makes the whole query error. - Forgetting that NULL plus any value is NULL, which blanks out totals after an outer join. - Declaring the wrong data type for the field, causing conversion errors only at run time. - Using a correlated subselect in the expression and watching the BAQ slow down as data grows. - Hard-coding company-specific IDs or dates in the expression instead of using criteria or a parameter. ## Ai Angle An assistant grounded in the Epicor data model can take a plain request such as margin percent per order line excluding closed orders, propose the tables, the join, the calculated field and the NULL guards, and show the SQL so a BAQ author can paste and test it. It works from the schema it can read and leaves publishing to a person. ## Faq ### Why does my Epicor BAQ calculated field show an error in the grid? **Answer:** Most often the declared data type does not match the expression result, or a NULL or zero divisor causes a conversion or divide by zero failure. Check the type, wrap nullable columns with ISNULL and guard division with CASE. Test the expression in SQL Server for a few rows to isolate which row fails. ### Can a calculated field reference another calculated field? **Answer:** Generally no, because each expression is evaluated within the same SELECT. Repeat the underlying expression, or build the first value in a subquery and reference its column in the outer query. Keeping layered logic in subqueries is also easier to maintain and debug. ### How do I add a parameter or prompt to a calculated field? **Answer:** Calculated fields do not take prompts directly. Add a criteria row on a column, or define a parameter on the BAQ and use it in a Where clause or in the expression, depending on your version. For simple filters, criteria on the Criteria tab are usually the cleaner choice. ## Search terms epicor baq calculated field, epicor query builder calculated field, epicor baq case statement, epicor baq divide by zero, epicor baq isnull, epicor baq subquery, epicor baq expression syntax, epicor baq date difference, business activity query examples --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json