Skip to main content

Finance | Monthly Profit and Loss by Account (Fixed Monthly Columns)

This SuiteQL query returns a pre-pivoted monthly profit and loss by account, with each month shown as a separate column and totals consolidated using NetSuite’s BUILTIN.CONSOLIDATE for accurate multi-subsidiary financial reporting in Excel.

Purpose

This SuiteQL statement generates a full year of monthly profit and loss activity for each P&L account, with a separate column for each month plus a year-to-date total. It applies NetSuite's BUILTIN.CONSOLIDATE logic so results reflect consolidated currency and financials, even in multi-subsidiary environments. By placing months in fixed columns, this layout makes it easy to compare month-over-month results, build Excel dashboards, and integrate with pivot tables, waterfalls, and trend charts. Users can adjust the hard-coded date filters to cover any fiscal year or period they need, and can change subsidiary and accounting book settings in the comments included in the SQL.

Sample Output

Sample data — actual results will vary.

The SuiteQL

/*
 * INCOME STATEMENT (PROFIT & LOSS) - MONTHLY BY ACCOUNT (PIVOTED)
 *
 * One row per account, one column per month, plus a Total column.
 * 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,

  /*
   * MONTHLY COLUMNS — change the year (2025) in each value to your fiscal year.
   * Must also change the year filter in the WHERE clause below.
   */
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-01' THEN cons_amt ELSE 0 END) AS January,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-02' THEN cons_amt ELSE 0 END) AS February,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-03' THEN cons_amt ELSE 0 END) AS March,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-04' THEN cons_amt ELSE 0 END) AS April,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-05' THEN cons_amt ELSE 0 END) AS May,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-06' THEN cons_amt ELSE 0 END) AS June,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-07' THEN cons_amt ELSE 0 END) AS July,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-08' THEN cons_amt ELSE 0 END) AS August,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-09' THEN cons_amt ELSE 0 END) AS September,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-10' THEN cons_amt ELSE 0 END) AS October,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-11' THEN cons_amt ELSE 0 END) AS November,
  SUM(CASE WHEN TO_CHAR(ap.startdate,'YYYY-MM')='2025-12' THEN cons_amt ELSE 0 END) AS December,
  SUM(cons_amt) AS Total

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

HAVING SUM(cons_amt) <> 0

ORDER BY section, a.acctnumber;

How the Query Works

  1. Retrieving posting activity for the selected year. The query collects posting transaction accounting lines for all P&L accounts and filters out annual and quarterly summary periods. The fiscal year is controlled by a single year filter in the inner subquery's WHERE clause — change '2025' there and update each of the twelve monthly CASE column values in the outer SELECT to match.

  2. Always applying NetSuite's consolidation engine. Every transaction amount is run through BUILTIN.CONSOLIDATE, even if your account has only one subsidiary. This ensures results always match the consolidated view NetSuite would produce. The subsidiary ID defaults to 1 — see Customization Notes for how to verify this is correct for your account.

  3. Standardizing signs for income and revenue accounts. 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 positive debits and do not need 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. Summarizing each month using CASE expressions. Each month column uses a CASE that checks whether the posting period's start date matches that calendar month. Because months are fixed columns, this layout is ideal for presenting a traditional 12-column P&L in Excel without any pivot configuration.

  6. Adding a total column. The Total field sums the consolidated monthly amounts across all twelve columns for each account, providing a full-year YTD figure in the same row.

  7. Filtering out rows with no activity. The HAVING clause removes any accounts that have no activity across the entire year.

Customization Notes

  • Fiscal year: change '2025' in the inner WHERE clause to your desired year, and update each of the twelve '2025-MM' values in the monthly CASE columns to match. Both must stay in sync or some months will show zeros while others capture data from the wrong year.

  • Subsidiary ID: the hardcoded value 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 1 if needed. On non-OneWorld accounts this value is ignored entirely.

  • Accounting book: the subquery (SELECT id FROM accountingbook WHERE isprimary = 'T') selects the primary book automatically. Replace it with a hardcoded book ID to target 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 path (e.g., "Revenue : Subscription Revenue").

  • Zero-balance accounts: remove HAVING SUM(cons_amt) <> 0 to include all P&L accounts regardless of activity for the year.

Related Articles
Did this answer your question?