Purpose

This query returns a consolidated Balance Sheet as of a specific accounting period, with one row per GL account. For each account it returns the account number, full hierarchy name, parent account number, account type, balance sheet section, and the consolidated balance in the root subsidiary's reporting currency.

Balance sheet accounts accumulate from the beginning of time, so the query sums all posted transaction lines whose posting period falls on or before the target period's end date — exactly how NetSuite's native Balance Sheet works. The target accounting period is resolved from a single YYYY-MM value in the CROSS JOIN, making it easy to point the query at any month-end.

The subsidiary and accounting book are both resolved dynamically rather than hardcoded: the subsidiary defaults to the root (top-level) entity returned by SELECT id FROM Subsidiary WHERE parent IS NULL, and the accounting book defaults to the primary book. This makes the query portable across NetSuite accounts without any manual ID lookup.

The SuiteQL

SELECT
  a.acctnumber                          AS account_number,
  a.fullname                            AS account_name,
  pa.acctnumber                         AS parent_account_number,
  a.accttype                            AS account_type,
  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,
  SUM(
    TO_NUMBER(
      BUILTIN.CONSOLIDATE(
        tal.amount,
        'LEDGER',
        'DEFAULT',
        'DEFAULT',
        ( SELECT id FROM Subsidiary WHERE parent IS NULL ),
        target_period.id,
        'DEFAULT'
      )
    )
  ) AS balance

FROM 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
  CROSS JOIN (
    SELECT id, enddate
    FROM accountingperiod
    WHERE TO_CHAR(startdate, 'YYYY-MM') = '2025-12'
      AND isquarter = 'F'
      AND isyear    = 'F'
  ) target_period

WHERE
  t.posting = 'T'
  AND tal.accountingbook = ( SELECT id FROM accountingbook WHERE isprimary = 'T' )
  AND a.accttype NOT IN (
    'Income',
    'COGS',
    'Cost of Goods Sold',
    'Expense',
    'OthIncome',
    'OthExpense'
  )
  AND ap.startdate <= target_period.enddate

GROUP BY
  a.acctnumber,
  a.fullname,
  pa.acctnumber,
  a.accttype

HAVING
  SUM(
    TO_NUMBER(
      BUILTIN.CONSOLIDATE(
        tal.amount,
        'LEDGER',
        'DEFAULT',
        'DEFAULT',
        ( SELECT id FROM Subsidiary WHERE parent IS NULL ),
        target_period.id,
        'DEFAULT'
      )
    )
  ) <> 0

ORDER BY
  section,
  a.acctnumber

Sample Output

Sample data — actual results will vary.

How the Query Works

Balance sheet accounts only The WHERE clause excludes all income statement account types — Income, OthIncome, COGS, Expense, and OthExpense — so only balance sheet accounts appear in the results. This mirrors exactly what NetSuite's native Balance Sheet report includes. Income statement accounts are excluded because their balances reset each fiscal year rather than accumulating from inception.
Balances as of a specific period via CROSS JOIN The CROSS JOIN to a subquery on accountingperiod resolves the target period from the single YYYY-MM string you provide. The main WHERE clause then keeps all transactions whose posting period's start date falls on or before that period's end date. This cumulative approach is required for balance sheet accounts, which carry their balances forward across all periods since the account was opened — not just since the start of the fiscal year.
BUILTIN.CONSOLIDATE for multi-currency translation Every transaction line amount is passed through BUILTIN.CONSOLIDATE, which translates foreign-currency postings into the consolidating subsidiary's reporting currency using the LEDGER (closing rate) method. This is the same translation method NetSuite's native consolidated reports use. On single-subsidiary (non-OneWorld) accounts the subsidiary argument is ignored and amounts are returned in the account's base currency.
Dynamic root subsidiary resolution Rather than hardcoding a subsidiary ID, the query uses SELECT id FROM Subsidiary WHERE parent IS NULL to find the top-level subsidiary at query time. This makes the query portable — it runs correctly on any NetSuite account without modification, producing the same consolidated view as running the native Balance Sheet with the Consolidated subsidiary context.
Dynamic primary accounting book resolution Similarly, SELECT id FROM accountingbook WHERE isprimary = 'T' resolves the primary accounting book at runtime. This prevents double-counting in multi-book environments and removes the need to know or hardcode the book ID.
Account hierarchy via fullname The a.fullname field returns the full parent-to-child account path (for example, Assets : Current Assets : Cash and Equivalents). This is useful for grouping and drill-down in Excel pivot tables. If you prefer the account display name without the hierarchy prefix, substitute a.accountsearchdisplaynamecopy.
Parent account number for sub-grouping The LEFT JOIN to the account table on a.parent returns the parent account's number alongside each row. This is optional context — useful if you want to recreate the indented chart-of-accounts structure in Excel using helper columns or Power Query.
Section classification via CASE on accttype The CASE expression maps NetSuite's account type codes to three standard balance sheet sections: Assets, Liabilities, and Equity. A fourth catch-all bucket ("4. Other Balance Sheet") captures any account types not explicitly listed. The numeric prefix on each section label ensures they sort into the correct order alphabetically in any pivot table without requiring a custom sort.
HAVING clause removes zero-balance accounts Accounts with a net consolidated balance of zero as of the target period are excluded. This keeps the output focused on accounts with actual balances and matches the behavior of NetSuite's native report. Remove the HAVING clause to include all balance sheet accounts regardless of balance — useful when verifying that all accounts are present in the chart of accounts.

Customization Notes

Target period: Change '2025-12' in the CROSS JOIN to any YYYY-MM value corresponding to a closed accounting period in your NetSuite account. This is the only place you need to update to repoint the report at a different month-end. The query automatically finds the period whose start date matches that year-month, so it works for any month regardless of your fiscal year structure.
Subsidiary: The default resolves the root subsidiary dynamically using SELECT id FROM Subsidiary WHERE parent IS NULL. To target a specific subsidiary instead — for example, to report on a single legal entity rather than the consolidated group — replace the subquery with a hardcoded subsidiary ID. To find your subsidiary IDs, run: SELECT id, name, parent FROM subsidiary WHERE isinactive = 'F' ORDER BY id. Note that the Subsidiary table may not be accessible in all role configurations; if the query errors, the DBA or a role with full SuiteAnalytics access is required.
Accounting book: The default uses SELECT id FROM accountingbook WHERE isprimary = 'T' to resolve the primary book. If your organization uses a secondary accounting book (for example, a US GAAP book alongside an IFRS book), replace the subquery with the relevant book ID. To find your book IDs, run: SELECT id, name, isprimary FROM accountingbook.
Account name format: a.fullname returns the full hierarchy path including all parent account names separated by colons (for example, Assets : Current Assets : Operating Checking). Switch to a.accountsearchdisplaynamecopy if you want just the account's own display name without the parent path — useful when the full hierarchy makes column contents too long for your report layout.
Zero-balance accounts: Remove the HAVING clause entirely to include all balance sheet accounts in the output, even those with a zero consolidated balance. This is useful when reconciling the output against your full chart of accounts or confirming that no accounts are missing from the result set.
OneWorld accounts where no data returns: If the query returns no rows in a OneWorld account, it is likely that all transactions are posted to child subsidiaries rather than the root. In that case, replace the SELECT id FROM Subsidiary WHERE parent IS NULL subquery with a specific subsidiary ID, or use 0 to consolidate across all subsidiaries.

Finance | Balance Sheet by Account (As of a Specific Period)

Purpose

The SuiteQL

Sample Output

How the Query Works

Customization Notes