Purpose of this query
This SuiteQL statement summarizes posted vendor bills by vendor, department, and expense account, using NetSuite’s BUILTIN.CONSOLIDATE to ensure amounts reflect consolidated financial reporting. It joins TransactionLine for department detail and TransactionAccountingLine for general ledger impact, then groups results to show total spend and bill count. The query filters to the primary accounting book and excludes annual and quarterly rollup periods to prevent double counting. This is especially useful for finance teams managing departmental budgets, expense accountability, and vendor concentration analysis across subsidiaries.
SuiteQL
SELECT
BUILTIN.DF(t.entity) AS Vendor,
BUILTIN.DF(tl.department) AS Department,
a.fullName AS ExpenseAccount,
SUM(
TO_NUMBER(
BUILTIN.CONSOLIDATE(
tal.amount,
'LEDGER',
'DEFAULT',
'DEFAULT',
1,
t.postingperiod,
'DEFAULT'
)
)
) AS TotalSpend,
COUNT(DISTINCT t.id) AS BillCount
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 Account a
ON a.id = tal.account
INNER JOIN accountingperiod ap
ON ap.id = t.postingperiod
WHERE t.type = 'VendBill'
AND t.posting = 'T'
AND tal.posting = 'T'
AND tal.accountingbook = 1
AND ap.isyear = 'F'
AND ap.isquarter = 'F'
AND a.accttype IN ('Expense','OthExpense')
AND ap.startdate >= TO_DATE('2025-01-01','YYYY-MM-DD')
GROUP BY
BUILTIN.DF(t.entity),
BUILTIN.DF(tl.department),
a.fullName
ORDER BY TotalSpend DESC
Sample Output
How the query works
Linking vendor bills to GL impact
The query starts from Transaction filtered to vendor bills, then joins TransactionLine for department detail and TransactionAccountingLine for the accounting impact. The link tal.transactionline = tl.id ensures the GL entry matches the exact transaction line.
Applying consolidation
BUILTIN.CONSOLIDATE converts each accounting amount into the consolidated currency for subsidiary ID 1 using the posting period ID. This ensures results align with consolidated financial statements.
Filtering to primary accounting book
tal.accountingbook = 1 restricts the query to the primary book, preventing double counting in multi-book environments.
Excluding rollup periods
The join to accountingperiod and filters ap.isyear = ‘F’ and ap.isquarter = ‘F’ exclude summary rollup periods, ensuring accurate period-level reporting.
Filtering to expense accounts
The filter a.accttype IN (‘Expense’,‘OthExpense’) isolates expense accounts only, excluding balance sheet and revenue accounts.
Grouping and summarizing
Results are grouped by vendor, department, and expense account.
SUM(…) calculates total consolidated spend.
COUNT(DISTINCT t.id) returns the number of vendor bills contributing to that spend.
Why this report is valuable
• Departmental budgeting: see which departments are driving expense spend.
• Vendor concentration analysis: identify top vendors by total consolidated spend.
• Cost control: detect unusual expense spikes by account or department.
• Multi-subsidiary reporting: safely combine spend across subsidiaries using consolidation logic.
• Audit readiness: reconcile expense account activity with vendor bills.
Customization notes
• Date filtering: adjust ap.startdate to define a specific reporting period.
• Subsidiary: change the subsidiary ID inside BUILTIN.CONSOLIDATE if needed.
• Add class or location: include BUILTIN.DF(tl.class) or BUILTIN.DF(tl.location) in SELECT and GROUP BY for additional segmentation.
• Restrict vendor list: add AND t.entity = <internal_id> to analyze one vendor.
• Rolling periods: replace the fixed date with ADD_MONTHS(CURRENT_DATE, -3) for dynamic reporting.