NetSuiteFree generatorPDF, Excel, Word exportNetSuiteAll NetSuite tools

NetSuite SuiteQL Trial Balance and Comparative Trial Balance Query

This generator builds SuiteQL that totals posted general ledger lines into a trial balance for one subsidiary, accounting book and period range, plus a by-department version and a two-period comparative. Paste the output into a SuiteQL tool, the N/query module or a REST call to query/v1/suiteql.

Fill in

Subsidiary internal ID

Internal ID from Setup > Company > Subsidiaries.

From period name

Exactly as shown in Setup > Accounting > Manage Accounting Periods.

To period name

Accounting book internal ID

Primary book is usually 1. Block 4 lists your books.

Comparative period A

Comparative period B

Generated code

Download:

1. Trial balance by account (period range, one subsidiary)

SELECT
  a.acctnumber,
  a.fullname,
  BUILTIN.DF(a.accttype) AS account_type,
  SUM(NVL(tal.debit, 0)) AS debit,
  SUM(NVL(tal.credit, 0)) AS credit,
  SUM(NVL(tal.debit, 0)) - SUM(NVL(tal.credit, 0)) AS net
FROM transactionaccountingline tal
INNER JOIN transaction t ON t.id = tal.transaction
INNER JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
INNER JOIN account a ON a.id = tal.account
INNER JOIN accountingperiod ap ON ap.id = t.postingperiod
WHERE tal.posting = 'T'
  AND tal.accountingbook = 1
  AND tl.subsidiary = 1
  AND ap.isyear = 'F'
  AND ap.isquarter = 'F'
  AND ap.startdate >= (SELECT startdate FROM accountingperiod WHERE periodname = 'Jan 2026' AND isyear = 'F' AND isquarter = 'F')
  AND ap.enddate <= (SELECT enddate FROM accountingperiod WHERE periodname = 'Mar 2026' AND isyear = 'F' AND isquarter = 'F')
GROUP BY a.acctnumber, a.fullname, BUILTIN.DF(a.accttype)
ORDER BY a.acctnumber

2. Trial balance by account and department

SELECT
  a.acctnumber,
  a.fullname,
  BUILTIN.DF(tl.department) AS department,
  SUM(NVL(tal.debit, 0)) AS debit,
  SUM(NVL(tal.credit, 0)) AS credit,
  SUM(NVL(tal.debit, 0)) - SUM(NVL(tal.credit, 0)) AS net
FROM transactionaccountingline tal
INNER JOIN transaction t ON t.id = tal.transaction
INNER JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
INNER JOIN account a ON a.id = tal.account
INNER JOIN accountingperiod ap ON ap.id = t.postingperiod
WHERE tal.posting = 'T'
  AND tal.accountingbook = 1
  AND tl.subsidiary = 1
  AND ap.isyear = 'F'
  AND ap.isquarter = 'F'
  AND ap.startdate >= (SELECT startdate FROM accountingperiod WHERE periodname = 'Jan 2026' AND isyear = 'F' AND isquarter = 'F')
  AND ap.enddate <= (SELECT enddate FROM accountingperiod WHERE periodname = 'Mar 2026' AND isyear = 'F' AND isquarter = 'F')
GROUP BY a.acctnumber, a.fullname, BUILTIN.DF(tl.department)
ORDER BY a.acctnumber, department

3. Comparative trial balance: period A vs period B

SELECT
  a.acctnumber,
  a.fullname,
  SUM(CASE WHEN ap.periodname = 'Feb 2026' THEN NVL(tal.debit, 0) - NVL(tal.credit, 0) ELSE 0 END) AS net_period_a,
  SUM(CASE WHEN ap.periodname = 'Mar 2026' THEN NVL(tal.debit, 0) - NVL(tal.credit, 0) ELSE 0 END) AS net_period_b,
  SUM(CASE WHEN ap.periodname = 'Mar 2026' THEN NVL(tal.debit, 0) - NVL(tal.credit, 0) ELSE 0 END)
    - SUM(CASE WHEN ap.periodname = 'Feb 2026' THEN NVL(tal.debit, 0) - NVL(tal.credit, 0) ELSE 0 END) AS change_b_minus_a
FROM transactionaccountingline tal
INNER JOIN transaction t ON t.id = tal.transaction
INNER JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
INNER JOIN account a ON a.id = tal.account
INNER JOIN accountingperiod ap ON ap.id = t.postingperiod
WHERE tal.posting = 'T'
  AND tal.accountingbook = 1
  AND tl.subsidiary = 1
  AND ap.periodname IN ('Feb 2026', 'Mar 2026')
  AND ap.isyear = 'F'
  AND ap.isquarter = 'F'
GROUP BY a.acctnumber, a.fullname
ORDER BY a.acctnumber

4. Helper: list accounting books and periods

SELECT id, name, isprimary FROM accountingbook;

SELECT id, periodname, startdate, enddate, isadjust, closed
FROM accountingperiod
WHERE isyear = 'F' AND isquarter = 'F'
ORDER BY startdate DESC

Verify in Setup > Records Catalog for your account: account.fullname (some accounts prefer accountsearchdisplayname), transactionaccountingline.debit, credit, posting and accountingbook, and accountingbook.isprimary (present when Multi-Book is enabled). Run each query once in sandbox and tie the totals to the standard Trial Balance report for one period before relying on it. SuiteQL tools run one statement at a time, so run the two helper queries separately.

Get your trial balance query tied out

Send us the query output and the standard report for one period and we will find where they differ and adjust the query for your books and subsidiaries.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How the query works

Posted GL impact in NetSuite lives in transactionaccountingline: one row per transaction line per accounting book, with account, debit, credit, amount and a posting flag. The query joins it to transaction for the posting period, to transactionline for subsidiary and department, to account for number and type, and to accountingperiod for the date range. It keeps only posting = 'T' rows so non-posting transactions such as sales orders and purchase orders are excluded, and filters isyear and isquarter to 'F' so summary periods do not double count. Net is debit minus credit, so assets and expenses are positive and liabilities, equity and income are negative, the same sign convention as a trial balance.

Worked example and how to check it

With subsidiary 1, book 1 and Jan 2026 to Mar 2026, block 1 returns one row per account with activity in those three months. To check income and expense accounts, run the Income Statement (Reports > Financial > Income Statement) for the same subsidiary and the same period range and compare a few accounts. The standard Trial Balance report (Reports > Financial > Trial Balance) shows balances as of the end period, not activity for a range, so to tie balance sheet accounts to it, remove the startdate condition so every period up to the end date is included. The sum of the net column across all rows should be zero for a balanced ledger.

Comparative and budget vs actual

Block 3 puts two periods side by side using conditional sums, which is cheaper than two queries and keeps accounts aligned. Use it for month-over-month reviews or as the actuals half of a budget vs actual comparison: budget amounts live in separate budget records, so export them separately and join on account and period outside NetSuite, or use the Budget vs Actual report. For a general ledger detail extract, drop the GROUP BY and select t.tranid, t.trandate, BUILTIN.DF(t.type), tal.debit and tal.credit per line, filtered to one account, which reproduces the rows behind each trial balance figure.

Gotchas

Amounts in transactionaccountingline are in the subsidiary's base currency for that book, so querying several subsidiaries with different currencies and summing them gives a meaningless total. Consolidated figures need currency translation, which SuiteQL provides through BUILTIN.CONSOLIDATE; check its arguments in NetSuite help before using it. Adjustment periods are included because they are neither year nor quarter rows; exclude them with ap.isadjust = 'F' if you want operating months only. Period names are text and must match exactly, including spacing. SuiteQL respects the role's permissions, so a role without access to some subsidiaries returns partial results without an error.

Frequently Asked Questions

Which SuiteQL table holds NetSuite GL postings?

transactionaccountingline. Each row is the accounting impact of a transaction line in one accounting book, with account, debit, credit, amount, posting and accountingbook. Join it to transactionline on transaction and transactionline for subsidiary, department, class and location, and to transaction for date, period and type.

Why does my SuiteQL trial balance not match the NetSuite report?

Usual causes: the report shows inception-to-date balances while the query sums only the selected periods; the query includes several accounting books; summary year or quarter periods were not excluded; non-posting lines were not filtered; or the role running SuiteQL cannot see every subsidiary. Compare one account at a time using the line-level version.

How do I build a comparative trial balance in SuiteQL?

Filter to the periods you need and use conditional aggregation: SUM(CASE WHEN ap.periodname = 'Feb 2026' THEN debit minus credit ELSE 0 END) for each period column. This keeps every account on one row and lets you add a difference column. Block 3 in this tool generates the full query.

Can I get a general ledger detail report from SuiteQL?

Yes. Use the same joins without GROUP BY and select tranid, trandate, type, memo, debit and credit for one account and period range. Order by trandate and tranid. For large ranges, page through the results with limit and offset or run it through SuiteAnalytics Connect.

If you want controllers to ask for trial balances and variances in plain English and see the SuiteQL behind each answer, running on your own infrastructure, we can set that up.

Talk to an Expert Explore More Free Tools