The Sales Gross Margin Report exists. Most finance teams export it to Excel, reformat it, and then spend ten minutes questioning whether the numbers are right before using them. If you have tried to build a BAQ to replace it in Kinetic, you have probably already found out why that is harder than it looks.
The problem is not the platform. It is the build. There are specific, documented reasons why margin BAQs produce numbers that do not match the standard report or your GL, and most of them are not obvious until you have already spent a week chasing the discrepancy.
Why Teams Try to Replace the Sales Gross Margin Report
The standard report is a Crystal Report. Static output, manual export, reformatted in Excel before anyone can use it. By the time the data reaches a decision, it has passed through at least two manual steps. That is not a reporting problem most finance teams are willing to live with permanently. So they try to build a live BAQ equivalent. That is where the complexity starts.
What a Naive Margin BAQ in Kinetic Gets Wrong
Pulling revenue and cost from InvcDtl and calculating margin looks straightforward. It is not. There are six documented exclusions that a basic query misses, and any one of them produces numbers that do not match your GL.
Discounts go into a separate bucket and are excluded from a basic ExtPrice calculation. Credit memos are not factored in unless handled explicitly. Deferred revenue is treated differently at the GL level and skews margin if not separated out. Misc charges require specific category settings to be enabled in Epicor, or they are excluded entirely. Advanced billing and advance less billing go into their own buckets and behave differently from standard invoice lines. Taxes are excluded from margin but inflate revenue figures if not stripped out.
A BAQ that does not account for all six of these will produce numbers that look plausible but do not reconcile. This is the most common reason margin BAQs fail.
Why the Apply Date Is the Wrong Field
Most users pull InvcHead.InvoiceDate or InvcHead.ApplyDate to assign margin to a fiscal period. Both are wrong. The actual apply date that matches your GL comes from TranGLC.TranDate. If you are not using that field, your margin by period will not reconcile with your Trial Balance or Sales Journal, regardless of how accurate the margin calculation itself is. This is the one that catches experienced users most often.
OurShipQty vs. SellingShipQty
There are two quantity fields in InvcDtl and they are not interchangeable. OurShipQty is used to calculate cost. It represents the inventory unit of measure quantity. SellingShipQty is used to calculate revenue and net unit price. It represents the sales unit of measure quantity.
When the sales UOM differs from the inventory UOM, using the wrong field on either side of the calculation produces incorrect margin. Epicor’s own zHomepage_SalesGrossMargin BAQ has a documented flaw here. The system’s built-in starting point cannot be trusted without review.
Lot Costing Requires a Different Build
For standard costing, cost fields in InvcDtl are populated and usable. For lot costing, StdPartCost fields are empty. Getting actual unit cost at the point of shipment requires joining to PartTran and filtering by the correct transaction type for shipments. This is not obvious from InvcDtl alone, and it is a common reason lot costing environments end up with blank or zero cost fields in margin BAQs.
What a Well-Built Margin BAQ in Kinetic Actually Gives You
Done right, this replaces a report nobody fully trusts with a live view finance can act on. Live margin by customer, salesperson, and product group on a Kinetic dashboard. Fiscal period alignment that actually matches your GL because the apply date is coming from TranGLC.TranDate. Credit memos, discounts, and misc charges handled correctly so the numbers reconcile the first time. Margin % built as a calculated field using the right cost and revenue components on both sides.
This is one of the more complex BAQ builds in Kinetic finance. It is not a beginner project. Getting it right requires understanding how Kinetic posts revenue, handles exceptions, and assigns costs across different costing methods. The teams that get it right tend to have either done it before or worked with someone who has.
Need Help Getting the Numbers Right?
A margin BAQ that actually reconciles with your GL in Kinetic requires getting six exclusions right, using the correct apply date field, handling two quantity fields correctly, and adjusting the build entirely if your company uses lot costing. That is a lot of places for the numbers to drift.
TeccWeb builds and fixes these queries for Kinetic finance teams who have already tried and hit the wall, and for teams that want it done right the first time. If your margin numbers are not adding up, get in touch.
Frequently Asked Questions
You can use it as a reference for structure, but not as a finished solution. The zHomepage_SalesGrossMargin BAQ has documented flaws, including a known issue with how it handles cost calculations when the sales UOM differs from the inventory UOM. Test it carefully against your own data before relying on it.
The most likely causes are one or more of the six exclusions described above: discounts, credit memos, deferred revenue, misc charges, advanced billing, or taxes not being handled correctly. Using the wrong apply date field is also a common cause. Any one of these produces a discrepancy that is easy to miss until you compare against your GL.
The primary table is InvcDtl for revenue and cost at the invoice line level. InvcHead is needed for invoice type, credit memo status, and deferred revenue flags. TranGLC is required for the correct apply date. For lot costing environments, PartTran is needed to get actual unit cost at the point of shipment.
Yes, significantly. Standard costing and lot costing require different approaches to pulling cost data. If your company uses lot costing, the cost fields in InvcDtl alone will not give you what you need. The build diverges at the point where you need actual unit cost at shipment time.