# Epicor BAQ: Add a Percentage Column and Change the Date Format in Kinetic > Source: https://netray.co/erp-help/epicor-baq-percentage-column-and-date-format-kinetic > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## 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. ## Kind how-to ## System epicor ## Erp Epicor Kinetic ## Module BAQ / Calculated Fields / Display Formats ## Query how to add percentage in value in baq epicor ## Alternate Queries - epicor baq date format - epicor kinetic date format - epicor kinetic change date format ## Applies To Epicor Kinetic and Epicor ERP 10 BAQs; expressions are evaluated by SQL Server, so use T-SQL syntax ## Steps ### How to add a percentage and format a date **Items:** - Open the BAQ in Query Builder, go to the Display Fields or Calculated Fields area, and add a new calculated field. - Set 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. - Multiply by 100 if you want 12.5 rather than 0.125, and name the column clearly, such as Margin Pct. - Set the number of decimals in the column format or display properties, or round in the expression. - For dates, keep the field as a date and set the display format on the column, for example a short date pattern. - If 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. - Test on the Analyze tab, then check how the dashboard or report shows the column for a user with a different culture. ## Sections ### Percentage expressions that do not fail **Body:** 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. **Code:** - -- 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 **Body:** 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 **Body:** 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. ## 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. ## Ai Angle 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. ## Faq ### How do I show a percentage in an Epicor BAQ? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms how to add percentage in value in baq epicor, epicor baq date format, epicor kinetic date format, epicor kinetic change date format, epicor baq calculated field percent, epicor baq divide by zero, epicor baq format date text, epicor baq margin percent --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json