Epicor BAQ: Add a Percentage Column and Change the Date Format in Kinetic
how to add percentage in value in baq epicor
Also searched as
- epicor baq date format
- epicor kinetic date format
- epicor kinetic change date format
Short answer
Add a percentage to a BAQ with a calculated field that divides one value by another, guards against zero, and multiplies by 100 (or formats the ratio as a percent). Dates are stored as dates, so display is controlled by a column format in the BAQ or dashboard and by the user's culture settings, or by converting the date to text in the expression.
Applies to: Epicor Kinetic and Epicor ERP 10 BAQs; expressions are evaluated by SQL Server, so use T-SQL syntax
How to add a percentage and format a date
- 1Open the BAQ in Query Builder, go to the Display Fields or Calculated Fields area, and add a new calculated field.
- 2Set the data type to decimal, and write the expression with a divide-by-zero guard, for example a CASE that returns 0 when the denominator is 0.
- 3Multiply by 100 if you want 12.5 rather than 0.125, and name the column clearly, such as Margin Pct.
- 4Set the number of decimals in the column format or display properties, or round in the expression.
- 5For dates, keep the field as a date and set the display format on the column, for example a short date pattern.
- 6If you need a fixed text format, convert in the expression, for example with FORMAT or CONVERT, and accept that the column becomes text and sorts as text.
- 7Test on the Analyze tab, then check how the dashboard or report shows the column for a user with a different culture.
Percentage expressions that do not fail
A typical margin percentage is the extended price minus cost, divided by the extended price. In T-SQL, guard the divisor so that rows with zero price do not raise a divide-by-zero error and break the whole query. A CASE expression or NULLIF on the denominator both work.
Cast to decimal before dividing. Integer division truncates, so 1 divided by 3 gives 0 rather than 0.33. Multiplying by 100.0 first, or casting one side to a decimal type, avoids this. In aggregates, compute the percentage from the sums (sum of margin divided by sum of revenue), not as an average of row percentages.
-- Calculated field: Margin Pct (decimal)
CASE WHEN OrderDtl.ExtPriceDtl = 0 THEN 0
ELSE ((OrderDtl.ExtPriceDtl - OrderDtl.Cost) / OrderDtl.ExtPriceDtl) * 100.0
END-- Date as text in a fixed pattern (column becomes text) FORMAT(OrderHed.OrderDate, 'yyyy-MM-dd')
Controlling date format
Date columns are displayed according to a column format and the culture of the user. For a classic dashboard, the Display Fields area of the BAQ lets you set a format string per column. In Kinetic, user culture and language settings influence the default date display, so two users may see different orders of day and month on the same dashboard.
If the requirement is a fixed format in an export or a report, convert the date to text in the query, or format it in the report layout instead. For sorting and filtering keep the original date column in the query and hide it if necessary, and display the text column next to it.
Percent as a format versus a value
A .NET style percent format string multiplies the value by 100 for display, so a stored ratio of 0.125 shows as 12.5 percent. If you also multiply by 100 in the expression, you get 1250 percent. Pick one approach per column and document it so report authors do not apply both.
When a BAQ feeds another system through REST, deliver the raw ratio and let the consumer format it. Display formats are a presentation choice and are not carried into the API result.
Try it here, free
Epicor BAQ Calculated Field Examples Reference
A searchable cheat sheet of BAQ calculated field expressions you can copy into the Query Builder, grouped by dates, quantities, pricing and text.
Common pitfalls
- !Integer division returning 0 for every row.
- !Dividing by a field that is sometimes zero or NULL.
- !Averaging row percentages instead of computing from sums.
- !Converting dates to text and then wondering why they sort alphabetically.
- !Applying a percent format and multiplying by 100 in the expression.
How an ERP-grounded AI assistant handles this
An assistant grounded in the Epicor schema can draft the calculated field for you, including the NULL and zero guards and the right table and field names, and show the SQL so a BAQ author can test it before saving. It reads the schema and does not change the BAQ on its own.
Frequently asked questions
How do I show a percentage in an Epicor BAQ?
Add a calculated field that divides two values with a zero guard, cast to decimal and multiplied by 100, then set the number of decimals in the column format.
Why does my percentage calculation return 0?
Both values are integers, so SQL Server does integer division. Cast one side to decimal or multiply by 100.0 before dividing.
How do I change the date format in Epicor Kinetic?
Set the display format on the column in the BAQ or dashboard, or adjust the user's culture and language settings. For a fixed text pattern, convert the date to text in the expression.
Does the BAQ date format apply to the REST API?
No. API results return the underlying date value, and formatting is done by the consumer. Display formats apply to the grids and reports that use the BAQ.
Related
How to Add a Calculated Field in an Epicor BAQ
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.
How-toEpicor BAQ Cookbook: Group By, Union, Distinct, Null Tests, Pivot and Subqueries
Most BAQ patterns map to plain SQL: Group By with aggregates, Combined Queries for union, a Distinct setting, null tests in criteria or expressions, and subqueries for per-row lookups. Pivot and running totals have no single switch, so build them with conditional aggregates and subqueries.
How-toFixing a Slow Epicor BAQ (Business Activity Query)
A slow BAQ in Epicor is usually caused by unindexed join columns, a subquery or calculated field forcing a table scan, or the BAQ pulling far more rows than the dashboard actually displays before filtering client-side. Fix it by reading the SQL Server execution plan Epicor generates, moving filters into the BAQ criteria instead of the dashboard filter panel, and replacing subqueries with joins where possible.
Error fixFixing Epicor BusinessObjectException: BPM Directive Errors
A BusinessObjectException that says "A Business Process Management (BPM) directive has raised the following error" means a directive on that business object stopped the transaction, either deliberately via a Raise Exception widget or accidentally via an unhandled .NET error in Custom Code. Expand the InnerException on the error dialog to see the real message and the directive name, then open BPM Designer for that object and method to find the widget that fired.
How-toEpicor MRP Runs but Generates No Suggestions
When Epicor's MRP process completes without a job or purchase suggestion for a part you expect one for, the cause is almost always the part's own configuration (Part Class Type, Make Direct, Non-MRP flag, planning Time Fence) or the demand not being linked in a way MRP recognizes, not a defect in the MRP engine. Work through the part's Planning tab, its safety stock and lead time setup, and the demand source (sales order line status, job material requirement) before assuming the run itself failed.
Error fixFixing Epicor REST v2 API 401 Unauthorized Errors
A 401 Unauthorized calling Epicor's REST v2 (api/v2/odata) endpoint almost always comes down to one of three things: a missing or wrong x-api-key header, valid credentials but an API key scoped to a different company than the one in the URL, or REST services simply not enabled for that endpoint. Confirm the API key exists and is active in Application Studio (or the classic REST API help page), matches the company segment in the URL, and that the account used for Basic auth or OAuth has the right security group.
AI for ERPAI for Epicor Kinetic, Beyond What Prism Covers
Add AI to Epicor Kinetic beyond Prism: private LLM over BAQs, BPM data, and REST v2, on-prem or private cloud, with honest guidance on when Prism already covers you.
AI for ERPPrivate AI for Epicor Kinetic Cloud, Outside the Multi-Tenant Boundary
Run a private LLM on Epicor Kinetic private cloud or Kinetic Cloud data via BAQs, REST v2 and Data Fabric, keeping sensitive manufacturing data off shared AI.
Stuck on Epicor Kinetic?
Talk to engineers who work inside Epicor Kinetic every week, and who build private AI that answers these questions from your own ERP data.
Keep exploring
Free tools
- Epicor BAQ Calculated Field Examples ReferenceA searchable cheat sheet of BAQ calculated field expressions you can copy into the Query Builder, grouped by dates, quantities, pricing and text.
- Epicor BAQ Performance Tuning ChecklistWork through a practical list of checks when a BAQ, dashboard or BPM lookup runs slowly in Epicor Kinetic.
- Epicor BAQ Query Template GeneratorFill in company, days back and status filters to get a starter SQL for an open jobs BAQ, plus a design checklist for the Query Builder.
ERP fixes and how-tos
- How to Add a Calculated Field in an Epicor BAQAdd a calculated field to an Epicor Kinetic BAQ: syntax, CASE, NULL and divide by zero guards, date math and when to use a subquery instead. Working examples.
- How to Create a BAQ in Epicor KineticCreate a BAQ in Epicor Kinetic Query Builder: pick tables, set joins, display fields, criteria, calculated fields, test, and use it in dashboards and reports.
- Epicor Kinetic UD Tables and UD Fields: Setup, Studio Binding, BAQ and SecurityHow to create and use UD tables and UD fields in Epicor Kinetic: setup, binding in Application Studio, reading them in a BAQ, and securing access.
AI on your ERP
- Private AI for Epicor Kinetic Cloud, Outside the Multi-Tenant BoundaryRun a private LLM on Epicor Kinetic private cloud or Kinetic Cloud data via BAQs, REST v2 and Data Fabric, keeping sensitive manufacturing data off shared AI.
- AI for Epicor Kinetic Job Shops: Quote History, Job Variance and Schedule RiskPrivate AI for Epicor Kinetic job shops: quote-to-job history, JobHead and JobOper variance, subcontract delays and schedule risk, via REST and BAQs.
Integrations
- How to add Slack to Epicor KineticConnect Slack to Epicor Kinetic with a Slack app, slash commands, interactive approvals and BPM-driven alerts, using the Kinetic REST API and BAQs.
- How to use ChatGPT with Epicor KineticConnect ChatGPT to Epicor Kinetic through a custom GPT with Actions, an exported dataset, or a private gateway. Covers BAQs, auth, data controls and when to go private.