Skip to main content

Finance | Monthly Profit and Loss by Account (Pivot Table Friendly)

This SuiteQL query returns monthly profit and loss data with months shown as rows instead of columns, making it ideal for Excel pivot tables and dashboards while using NetSuite’s BUILTIN.CONSOLIDATE for accurate consolidated reporting.

Purpose

This SuiteQL statement generates profit and loss activity by account and month in an unpivoted format, meaning each account appears once per month as a separate row. Because months are returned as rows instead of fixed columns, users can easily build pivot tables, create charts, calculate trends, and perform month-over-month analysis directly in Excel. The query applies NetSuite's BUILTIN.CONSOLIDATE function to ensure amounts reflect consolidated reporting across subsidiaries. The year is currently hard-coded to 2025 and should be adjusted before running the query. Users can modify the date filters to run for any fiscal year and adjust subsidiary, accounting book, or consolidation settings directly in the SQL as needed.

Sample Output

Sample data — actual results will vary.

The SuiteQL

/*
 * INCOME STATEMENT (PROFIT & LOSS) - MONTHLY BY ACCOUNT (UNPIVOTED)
 *
 * One row per account per month — ideal for Excel pivot tables and charting.
 * Compatible with both OneWorld (multi-subsidiary) and standard NetSuite accounts.
 */

SELECT
  a.acctnumber AS account_number,
  a.accountsearchdisplaynamecopy AS account_name,
  -- Alternative: a.fullname AS account_name,
  pa.acctnumber AS parent_account_number,
  a.accttype AS account_type,

  CASE
    WHEN a.accttype = 'Income'                       THEN '1. Revenue'
    WHEN a.accttype IN ('COGS','Cost of Goods Sold') THEN '2. Cost of Goods Sold'
    WHEN a.accttype = 'Expense'                      THEN '3. Operating Expenses'
    WHEN a.accttype = 'OthIncome'                    THEN '4. Other Income'
    WHEN a.accttype = 'OthExpense'                   THEN '5. Other Expense'
  END AS section,

  TO_CHAR(ap.startdate,'YYYY-MM') AS month,
  SUM(cons_amt) AS amount

FROM (
  SELECT
    tal.account,
    t.postingperiod,
    TO_NUMBER(
      BUILTIN.CONSOLIDATE(
        tal.amount,
        'LEDGER', 'DEFAULT', 'DEFAULT',
        1,               -- Subsidiary ID: change to your root or target subsidiary
                         -- To find IDs: SELECT id, name FROM Subsidiary WHERE parent IS NULL
        t.postingperiod,
        'DEFAULT'
      )
    )
    * CASE WHEN a.accttype IN ('Income','OthIncome') THEN -1 ELSE 1 END AS cons_amt

  FROM transactionaccountingline tal
    JOIN transaction t        ON t.id  = tal.transaction
    JOIN account a            ON a.id  = tal.account
    JOIN accountingperiod apf ON apf.id = t.postingperiod

  WHERE
    t.posting = 'T'
    AND tal.accountingbook = (SELECT id FROM accountingbook WHERE isprimary = 'T')
    AND apf.isyear    = 'F'
    AND apf.isquarter = 'F'
    AND TO_CHAR(apf.startdate,'YYYY') = '2025'  -- *** CHANGE THIS to your fiscal year ***
    AND COALESCE(a.eliminate,'F') = 'F'
    AND a.accttype IN (
      'Income', 'COGS', 'Cost of Goods Sold',
      'Expense', 'OthIncome', 'OthExpense'
    )
) x
JOIN accountingperiod ap ON ap.id = x.postingperiod
JOIN account a          ON a.id  = x.account
LEFT JOIN account pa    ON pa.id = a.parent

GROUP BY
  a.acctnumber,
  a.accountsearchdisplaynamecopy,
  pa.acctnumber,
  a.accttype,
  TO_CHAR(ap.startdate,'YYYY-MM')

HAVING SUM(cons_amt) <> 0

ORDER BY section, a.acctnumber, month;

How the Query Works

  1. Retrieving posting transaction activity for the selected year. The inner query pulls posting-only accounting lines for P&L accounts, excludes annual and quarterly summary periods, and limits results to fiscal year 2025. Change '2025' in the WHERE clause to run for a different year.

  2. Always applying NetSuite's consolidation logic. Every transaction amount is passed through BUILTIN.CONSOLIDATE to ensure results reflect consolidated reporting, even when there is only one subsidiary. The subsidiary ID defaults to 1 — see Customization Notes for how to verify this is correct for your account.

  3. Standardizing income account signs. NetSuite stores Income and Other Income as credits (negative values in the ledger). Multiplying by -1 converts these to positive so revenue appears with a conventional positive sign. Expense accounts are already stored as debits and do not require adjustment.

  4. Categorizing accounts into financial statement sections. The CASE expression assigns each account to Revenue, COGS, Operating Expenses, Other Income, or Other Expense using numbered prefixes to control sort order.

  5. Returning one row per account per month. The month column is produced using TO_CHAR(ap.startdate, 'YYYY-MM'), giving a sortable string like 2025-03. Because the output is unpivoted, users can drop it straight into an Excel pivot table and slice by month, section, or account without any additional reshaping.

  6. Summing monthly consolidated amounts. The SUM(cons_amt) aggregation produces the final value for each account-month combination after BUILTIN.CONSOLIDATE and the sign adjustment have been applied in the inner subquery.

  7. Filtering out zero-activity rows. The HAVING clause removes any account-month combinations with no activity, keeping the dataset compact and free of empty rows that would otherwise clutter pivot tables.

Customization Notes

  • Fiscal year: change '2025' in the inner WHERE clause to your desired year. For a custom date range that doesn't align to a calendar year, replace it with explicit date filters: AND apf.startdate >= TO_DATE('2025-04-01','YYYY-MM-DD') AND apf.enddate <= TO_DATE('2026-03-31','YYYY-MM-DD').

  • Subsidiary ID: the hardcoded 1 inside BUILTIN.CONSOLIDATE is typically the root subsidiary, but this is not guaranteed in all NetSuite accounts. Verify your root subsidiary by running SELECT id, name FROM Subsidiary WHERE parent IS NULL and update the value if needed. On non-OneWorld accounts this value is ignored.

  • Accounting book: the subquery (SELECT id FROM accountingbook WHERE isprimary = 'T') selects the primary book automatically. Replace it with a hardcoded book ID to use a secondary accounting book. Run SELECT id, name, isprimary FROM accountingbook to find IDs.

  • Account name format: accountsearchdisplaynamecopy returns a short name without hierarchy prefixes. Switch to a.fullname for the full parent-to-child path.

  • Zero-balance rows: remove HAVING SUM(cons_amt) <> 0 to include all account-month combinations even with no activity.

Related Articles
Did this answer your question?