Skip to main content

Finance | Year-to-Date P&L

This SuiteQL query returns consolidated year-to-date profit and loss totals by grouping accounts into standard finncial statement sections and uses BUILTIN.CONSOLIDATE to ensure results reflect consolidated financials across subsidiaries.

Purpose

This SuiteQL statement calculates year-to-date profit and loss balances for every P&L account for a specified fiscal year. It applies NetSuite's BUILTIN.CONSOLIDATE function so the results reflect consolidated reporting across subsidiaries and convert amounts to the consolidated currency. Users get account-level totals that are ready for dashboards, summaries, or Excel pivot tables. The query includes account number, account name, parent account, account type, financial statement section, and the year-to-date activity. Users can change the hard-coded fiscal year filter to run the report for any other year, modify subsidiary ID or accounting book settings, and tailor account type filters as needed.

Sample Output

Sample data — actual results will vary.

The SuiteQL

/*
 * INCOME STATEMENT (PROFIT & LOSS) - YEAR TO DATE
 *
 * This query generates a YTD income statement by account, showing:
 * - Revenue, Cost of Goods Sold, Operating Expenses, Other Income, Other Expense
 * - Amounts are consolidated to the target subsidiary's currency
 * - Only accounts with non-zero balances are included
 *
 * Compatible with both OneWorld (multi-subsidiary) and standard NetSuite accounts.
 */

SELECT * FROM (
    SELECT
        a.acctnumber AS account_number,
        a.accountsearchdisplaynamecopy AS account_name,
        -- Alternative: For full hierarchy path (e.g., "Parent : Child : Account"), use:
        -- a.fullname AS account_name,
        pa.acctnumber AS parent_account_number,
        a.accttype AS account_type,

        /*
         * FINANCIAL STATEMENT SECTIONS
         * Maps NetSuite account types to standard income statement sections.
         * The numbered prefixes ensure proper sort order.
         */
        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,

        SUM(
            TO_NUMBER(
                BUILTIN.CONSOLIDATE(
                    tal.amount,
                    'LEDGER',        -- Consolidation type: Uses ledger amounts
                    'DEFAULT',       -- Accounting book: 'DEFAULT' uses primary book
                    'DEFAULT',       -- Currency: 'DEFAULT' uses subsidiary's base currency
                    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'        -- Exchange rate type: 'DEFAULT' uses standard rates
                )
            )
            * CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN -1 ELSE 1 END
        ) AS ytd_amount

    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

    WHERE
        t.posting = 'T'
        AND tal.accountingbook = (SELECT id FROM accountingbook WHERE isprimary = 'T')
        AND ap.isyear     = 'F'
        AND ap.isquarter  = 'F'
        /*
         * DATE RANGE FILTER — change both dates to your fiscal year
         */
        AND ap.startdate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
        AND ap.enddate   <= TO_DATE('2025-12-31', 'YYYY-MM-DD')
        AND COALESCE(a.eliminate, 'F') = 'F'
        AND a.accttype IN (
            'Income', 'COGS', 'Cost of Goods Sold',
            'Expense', 'OthIncome', 'OthExpense'
        )

    GROUP BY
        a.acctnumber,
        a.accountsearchdisplaynamecopy,
        pa.acctnumber,
        a.accttype
    ORDER BY section, a.acctnumber
)
WHERE ytd_amount <> 0

How the Query Works

  1. Retrieving posting-only transactions for the selected fiscal year. The query filters for posting transactions, excludes annual and quarterly periods, and limits results to the fixed reporting year of 2025. Change the two date filters in the WHERE clause to run for any fiscal year.

  2. Always applying NetSuite's consolidation logic. BUILTIN.CONSOLIDATE is executed for every transaction line, even when only one subsidiary exists. This ensures that all amounts are returned in fully consolidated form. The subsidiary ID defaults to 1 — see the Customization Notes below for how to verify and change this for your account.

  3. Standardizing the sign for revenue-related 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 in the output. Expense accounts are already stored as debits and do not require adjustment.

  4. Grouping accounts into financial statement sections. The CASE expression assigns each account to one of five standard sections — Revenue, COGS, Operating Expenses, Other Income, or Other Expense — using numbered prefixes to control sort order in Excel.

  5. Calculating year-to-date totals. The SUM expression aggregates all consolidated transaction amounts for each account across every posting period in the fiscal year, producing a single YTD figure per account row.

  6. Removing accounts with no activity. The outer WHERE clause filters out any rows where the consolidated year-to-date amount is zero, keeping the output focused on accounts with real activity.

Customization Notes

  • Fiscal year: change the two dates in the WHERE clause (2025-01-01 and 2025-12-31) to your desired reporting year. Both must be updated together.

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

  • Accounting book: the dynamic subquery (SELECT id FROM accountingbook WHERE isprimary = 'T') selects the primary book automatically. To target a specific secondary book, replace it with the book's internal ID. Run SELECT id, name, isprimary FROM accountingbook to find IDs.

  • Account name format: accountsearchdisplaynamecopy returns the account name without hierarchy prefixes. Switch to a.fullname for the full parent-to-child path (e.g., "Revenue : Subscription Revenue").

  • Zero-balance accounts: remove the outer WHERE ytd_amount <> 0 to include all P&L accounts regardless of activity.

Related Articles
Did this answer your question?