Purpose of this query
This SuiteQL statement calculates balance sheet account balances as of a selected accounting period and applies NetSuite's BUILTIN.CONSOLIDATE function so results match consolidated reporting. It groups accounts into assets, liabilities, and equity sections and returns account number, account name (with full hierarchy path), parent account, account type, and the final consolidated balance. The default subsidiary target is dynamically resolved to the root (top-level) subsidiary — the same as running NetSuite's native balance sheet with Consolidated subsidiary context. The query is compatible with both OneWorld (multi-subsidiary) and standard NetSuite accounts.
SuiteQL
/*
* BALANCE SHEET - AS OF DATE
*
* Generates a balance sheet showing account balances as of a specific period.
* Balance sheet accounts accumulate all transactions from inception through the target date.
*
* Shows: Assets, Liabilities, and Equity
* Amounts are consolidated to the root subsidiary's currency.
* Only accounts with non-zero balances are included.
*
* Compatible with both OneWorld (multi-subsidiary) and standard NetSuite accounts.
*/SELECT
a.acctnumber AS account_number,
a.fullname AS account_name,
-- Alternative: accountsearchdisplaynamecopy shows name only (no hierarchy path).
-- Swap above if you prefer shorter names without parent account prefixes.
pa.acctnumber AS parent_account_number,
a.accttype AS account_type, /*
* FINANCIAL STATEMENT SECTIONS
* Maps NetSuite account types to standard balance sheet sections.
* Numbered prefixes ensure correct sort order.
*/
CASE
WHEN a.accttype IN ('Bank', 'AcctRec', 'OthCurrAsset', 'FixedAsset', 'OthAsset', 'DeferExpense', 'Unbilled')
THEN '1. Assets'
WHEN a.accttype IN ('AcctPay', 'CreditCard', 'OthCurrLiab', 'LongTermLiab', 'DeferRevenue')
THEN '2. Liabilities'
WHEN a.accttype IN ('Equity')
THEN '3. Equity'
ELSE '4. Other Balance Sheet'
END AS section, /*
* BALANCE CALCULATION
* Sums all transactions from inception through the target period end date.
* BUILTIN.CONSOLIDATE translates amounts to the root subsidiary's base currency.
* On non-OneWorld accounts the consolidation argument is ignored and amounts
* are returned as-is in the account's base currency.
*/
SUM(
TO_NUMBER(
BUILTIN.CONSOLIDATE(
tal.amount,
'LEDGER', -- Consolidation type: ledger amounts
'DEFAULT', -- Accounting book: primary book
'DEFAULT', -- Currency: subsidiary base currency
/*
* SUBSIDIARY ID FOR CONSOLIDATION TARGET
* Default: dynamically resolves the root (top-level) subsidiary.
* This is the subsidiary with no parent — equivalent to the
* Consolidated view in NetSuite's native financial reports.
*
* NOTE: if this query returns no data in a OneWorld account,
* your transactions may be posted to child subsidiaries.
* Try removing the AND t.subsidiary filter below, or target
* a specific subsidiary by replacing the subquery with a
* hardcoded ID (e.g. 5).
*
* To find subsidiary IDs: SELECT id, name FROM Subsidiary ORDER BY id
* (Note: Subsidiary table may not be accessible in all environments.)
*/
( SELECT id FROM Subsidiary WHERE parent IS NULL ),
target_period.id,
'DEFAULT' -- Exchange rate type: standard rates
)
)
) AS balanceFROM transactionaccountingline tal
JOIN transaction t ON t.id = tal.transaction
JOIN account a ON a.id = tal.account
JOIN accountingperiod ap ON ap.id = t.postingperiod
LEFT JOIN account pa ON pa.id = a.parent /*
* TARGET PERIOD FOR BALANCE SHEET DATE
* The balance sheet shows balances as of the end of this period.
*
* *** CHANGE '2025-12' TO YOUR DESIRED PERIOD (YYYY-MM) ***
*
* Example: '2025-12' = balances as of December 31, 2025
* '2026-03' = balances as of March 31, 2026
*
* Only needs to be changed in one place — the CROSS JOIN below
* is referenced in both the WHERE clause and BUILTIN.CONSOLIDATE.
*/
CROSS JOIN (
SELECT id, enddate
FROM accountingperiod
WHERE TO_CHAR(startdate, 'YYYY-MM') = '2025-12' -- *** CHANGE THIS ***
AND isquarter = 'F'
AND isyear = 'F'
FETCH FIRST 1 ROWS ONLY
) target_periodWHERE
t.posting = 'T' /*
* ACCOUNTING BOOK FILTER
* Dynamically selects the primary accounting book — portable across all accounts.
*
* To use a specific book, replace the subquery with the book ID:
* AND tal.accountingbook = 3
*
* To find accounting book IDs: SELECT id, name, isprimary FROM accountingbook
*/
AND tal.accountingbook = ( SELECT id FROM accountingbook WHERE isprimary = 'T' ) /*
* ACCOUNT TYPE FILTER
* Excludes income statement (P&L) accounts — balance sheet only.
*/
AND a.accttype NOT IN (
'Income',
'COGS',
'Cost of Goods Sold',
'Expense',
'OthIncome',
'OthExpense'
) /*
* DATE FILTER — CUMULATIVE THROUGH TARGET PERIOD
* Balance sheet includes all transactions from inception through
* the end of the target period. enddate comes from the CROSS JOIN above.
*/
AND ap.startdate <= target_period.enddateGROUP BY
a.acctnumber,
a.fullname,
pa.acctnumber,
a.accttype/*
* ZERO BALANCE FILTER
* Excludes accounts with no balance as of the target date.
* Remove the HAVING clause to show all balance sheet accounts regardless of balance.
*/
HAVING
SUM(
TO_NUMBER(
BUILTIN.CONSOLIDATE(
tal.amount,
'LEDGER',
'DEFAULT',
'DEFAULT',
( SELECT id FROM Subsidiary WHERE parent IS NULL ), -- Must match value above
target_period.id,
'DEFAULT'
)
)
) <> 0ORDER BY
section,
a.acctnumberSample Output
How the query works
Here is a plain-English breakdown of how this balance sheet query operates.
Balance sheet accounts only
The WHERE clause excludes all income statement account types (Income, COGS, Expense, OthIncome, OthExpense), ensuring only assets, liabilities, and equity accounts are included.
Balances as of a specific accounting period
The CROSS JOIN retrieves the accounting period matching the target YYYY-MM. The DATE FILTER then includes all transactions with posting periods from inception through that period's end date — balance sheet accounts accumulate from the beginning of time, not just the current year.
Consolidation to root subsidiary
BUILTIN.CONSOLIDATE converts all amounts to the root (top-level) subsidiary's base currency. The subsidiary ID is resolved dynamically using a subquery (SELECT id FROM Subsidiary WHERE parent IS NULL) rather than a hardcoded value, making the query portable across all NetSuite accounts. On non-OneWorld accounts this argument is ignored and amounts are returned in the account's base currency.
Account name with hierarchy
a.fullname returns the full account hierarchy path (e.g. "Assets : Current Assets : Cash"), making it easy to see where accounts sit in the chart of accounts. Use a.accountsearchdisplaynamecopy if you prefer the name only without the hierarchy prefix.
Primary accounting book
tal.accountingbook is filtered using a dynamic subquery to the primary book, preventing double-counting in multi-book environments and making the query portable without hardcoding a book ID.
Categorizing into balance sheet sections
The CASE expression maps NetSuite account types to Assets, Liabilities, Equity, or Other Balance Sheet, with numbered prefixes to control sort order.
Zero balance filter
The HAVING clause excludes accounts with no balance as of the target date. Remove it to show all balance sheet accounts regardless of balance.
OneWorld accounts with AR on child subsidiaries
If the query returns no data in a OneWorld account, transactions may be posted to child subsidiaries rather than the root. In that case, remove the Subsidiary WHERE parent IS NULL subquery and replace it with a specific subsidiary ID, or use 0 to consolidate across all subsidiaries.
Customization notes
Target period: change '2025-12' in the CROSS JOIN to your desired YYYY-MM. This is the only place you need to update it.
Subsidiary: the default dynamically resolves the root subsidiary. To target a specific subsidiary, replace the subquery with a hardcoded ID. Run SELECT id, name FROM Subsidiary ORDER BY id to find IDs. Note: the Subsidiary table may not be accessible in all environments.
Accounting book: the default dynamically selects the primary book. To use a specific book, replace the subquery with the book ID. Run SELECT id, name, isprimary FROM accountingbook to find IDs.
Account name: a.fullname returns the full hierarchy path. Switch to a.accountsearchdisplaynamecopy for shorter names without parent prefixes.
Zero balances: remove the HAVING clause to include all balance sheet accounts regardless of balance.