Purpose of this query
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.
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' )
GROUP BY i.itemid, i.displayname, BUILTIN.DF( tl.location )
ORDER BY COGS DESC
Sample Output
How the query works
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.
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.
Ensuring posted transactions only
The filters t.posting = ‘T’ and tal.posting = ‘T’ ensure that only posted accounting activity is included.
Grouping by item and location
Results are grouped by SKU and location, enabling margin analysis by warehouse, store, or subsidiary (if extended with additional grouping).
Summarizing cost and volume
SUM(tal.amount) provides total COGS.
SUM(tl.quantity) provides total units sold.
Together, these allow calculation of average cost per unit and margin analysis in Excel.
Why this is a high-value use case
• Margin analysis: calculate gross margin by SKU when combined with revenue queries.
• Location profitability: compare cost performance across warehouses or stores.
• Inventory reconciliation: validate that operational quantities align with GL impact.
• Executive dashboards: build pivot tables showing top COGS drivers.
• Planning and forecasting: analyze unit cost trends over time.
Customization notes
• Date range: update the TO_DATE filter to define your reporting period.
• Add subsidiary filter: add AND t.subsidiary = <internal_id> if needed.
• Add revenue join: combine with revenue queries to calculate margin directly in SuiteQL.
• Add rolling periods: replace fixed dates with ADD_MONTHS(CURRENT_DATE, -3) for dynamic windows.
• Add class, department, or location grouping for further segmentation.