The Epicor Sales Gross Margin Report and the Homepage Query have flaws. I spent a solid 1 month trying to balance the BAQ out to the Sales Journal, SGM Report, Trial Balance, Chart Tracker etc…
I can’t recall all the flaws but what I learned is that on an Invoice all things go into “seperate journals” or “GL Accts” and the SGM Report out of the box will exclude numbers and throw you off.
For example:
- Discounts go into a seperate bucket
- Credit Memos
- Deferred Revenue
- Misc Charges
- Taxes
- Adv Less Billing
Not only that but you can’t even use the Invoice Date or Apply Date, you have to go to TranGLC and find the “Actual Apply Date” which is the TranDate.
(ISNULL(Erp.FiscalYear(InvcHead.Company, TranGLC.TranDate), ISNULL(Erp.FiscalYear(InvcHead.Company, InvcDtlMiscChargesSubQuery.TranGLCMisc_TranDate), InvcHead.FiscalYear))) as [Calculated_ApplyYear],
(ISNULL(Erp.FiscalPeriod(InvcHead.Company, TranGLC.TranDate), ISNULL(Erp.FiscalPeriod(InvcHead.Company, InvcDtlMiscChargesSubQuery.TranGLCMisc_TranDate), InvcHead.FiscalPeriod))) as [Calculated_ApplyPeriod],
Also for Epicor out of the box to calculate certain Misc Charges, you must enable those Categories or they will be excluded:
Anyways - as promised, attached are my BAQs (which are still missing some Targeted GL Acct #s) but it was good enough for us.
DB-SGMDetails.baq (58.8 KB) DB-SGMSummary.baq (66.1 KB)