Skip to main content

Finance | COGS by Item and Location (Inventory & Margin Analysis)

This SuiteQL query returns Cost of Goods Sold and units sold by item and location, enabling margin analysis and operational reconciliation directly in Excel.

Purpose

This SuiteQL statement joins Item, TransactionLine, and TransactionAccountingLine to produce a dataset that connects operational item detail with general ledger COGS impact. It is designed for inventory-heavy businesses such as manufacturing, distribution, and retail that need to reconcile COGS by SKU and location, analyze unit economics, and build margin dashboards in Excel. Because COGS accounts are debits in the general ledger, the amounts are already positive and do not require a sign adjustment. The query can be customized by adjusting the date filter, adding subsidiary filters, or grouping by additional dimensions such as class or department. Note that this query does not filter out intercompany elimination accounts — in OneWorld environments, add AND COALESCE(a.eliminate,'F') = 'F' to the WHERE clause to exclude elimination accounts from COGS totals.

Sample Output

Sample data — actual results will vary.

The SuiteQL

SELECT
  i.itemid                     AS ItemSKU,
  i.displayname                AS ItemName,
  BUILTIN.DF( tl.location )    AS Location,
  SUM( tal.amount )            AS COGS,
  SUM( tl.quantity )           AS UnitsSold
FROM Transaction t
INNER JOIN TransactionLine tl
  ON ( tl.transaction = t.id )
INNER JOIN TransactionAccountingLine tal
  ON ( tal.transaction = t.id AND tal.transactionline = tl.id )
INNER JOIN Item i
  ON ( i.id = tl.item )
INNER JOIN Account a
  ON ( a.id = tal.account )
WHERE t.posting  = 'T'
  AND tal.posting = 'T'
  AND a.accttype  = 'COGS'
  AND t.trandate >= TO_DATE( '2025-01-01', 'YYYY-MM-DD' )
  -- AND COALESCE(a.eliminate,'F') = 'F'  /* uncomment in OneWorld to exclude intercompany accounts */
GROUP BY i.itemid, i.displayname, BUILTIN.DF( tl.location )
ORDER BY COGS DESC

How the Query Works

  1. Linking operational and financial data. The query joins TransactionLine (which contains item, quantity, and location) with TransactionAccountingLine (which contains the GL impact). The key relationship is tal.transactionline = tl.id, ensuring the accounting entry matches the exact item line rather than any other line on the same transaction.

  2. Filtering to COGS accounts. The filter a.accttype = 'COGS' isolates cost of goods sold entries only. Because COGS is recorded as a debit, the amount is already positive and does not require a sign flip — unlike revenue accounts which must be multiplied by -1.

  3. Ensuring posted transactions only. The filters t.posting = 'T' and tal.posting = 'T' on both the transaction header and the accounting line ensure that only fully posted activity is included. Draft, pending, or voided transactions are excluded.

  4. Grouping by item and location. Results are grouped by SKU, display name, and location, enabling margin analysis by warehouse, store, or distribution center. Extend the GROUP BY to include subsidiary, class, or department for additional segmentation.

  5. Summarizing cost and volume. SUM(tal.amount) produces total COGS in the subsidiary's base currency. SUM(tl.quantity) produces total units sold. Together these allow average cost per unit and gross margin to be calculated in Excel by combining with a revenue query.

Customization Notes

  • Date range: update the TO_DATE('2025-01-01','YYYY-MM-DD') filter to set the start of your reporting window. Add an end-date filter with AND t.trandate <= TO_DATE('2025-12-31','YYYY-MM-DD') to bound both ends.

  • Subsidiary filter: add AND t.subsidiary = <internal_id> to restrict to a single entity in a OneWorld account. Run SELECT id, name FROM Subsidiary ORDER BY id to find IDs.

  • Elimination accounts (OneWorld): the query does not exclude intercompany elimination accounts by default. In OneWorld environments, add AND COALESCE(a.eliminate,'F') = 'F' to the WHERE clause to avoid including intercompany COGS in totals.

  • Additional dimensions: add BUILTIN.DF(tl.class), BUILTIN.DF(tl.department), or t.subsidiary to the SELECT and GROUP BY to break COGS down by business segment.

  • Margin analysis: combine this query with a revenue query (filtering to a.accttype = 'Income' on the same item and date range) in Excel to calculate gross margin per SKU.

  • Rolling periods: replace the fixed TO_DATE filter with t.trandate >= ADD_MONTHS(CURRENT_DATE, -3) for a dynamic rolling 90-day window.

Related Articles
Did this answer your question?