Purpose of this query
This SuiteQL statement summarizes customer invoices and customer credits by sales representative. It counts distinct transactions and calculates a consolidated net amount by starting with the mainline net amount and then subtracting the total tax lines on each transaction. This approach is useful when you want a clean net sales number for performance tracking, leaderboard reporting, or commission-related analysis. The date range and subsidiary are hard-coded and should be updated as needed. Users can learn what to change by reviewing the query logic around dates, subsidiary ID, and transaction types.
Sample Output
SuiteQL
/*
Top 10 Sales Reps by Net Invoiced Revenue: sums customer invoices and credits
by sales rep, starting from the mainline net amount, then subtracting tax lines
so the result is clean net revenue. Consolidated to the target subsidiary's
base currency via BUILTIN.CONSOLIDATE.
Change the two TO_DATE filters to your desired period.
Change the subsidiary ID (default 1) to target a specific entity.
Change FETCH FIRST 10 to return more or fewer reps.
*/
SELECT
e.firstname || ' ' || e.lastname AS sales_rep_name,
COUNT(DISTINCT t.id) AS total_invoices,
SUM(
TO_NUMBER(
BUILTIN.CONSOLIDATE(
tl.netamount,
'LEDGER', -- Consolidation type
'DEFAULT', -- Accounting book: primary
'DEFAULT', -- Currency: subsidiary base
1, -- Subsidiary ID: change to your root or target subsidiary
-- To find IDs: SELECT id, name FROM Subsidiary ORDER BY id
t.postingperiod,
'DEFAULT' -- Exchange rate type
)
) - NVL(
(
/* Back out tax lines so total reflects net revenue excluding tax */
SELECT SUM(tltax.foreignamount * -1)
FROM transactionline tltax
WHERE tltax.transaction = t.id
AND tltax.taxline = 'T'
), 0
)
) AS total_amount_usd
FROM transaction t
JOIN employee e ON t.employee = e.id
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type IN ('CustInvc', 'CustCred') /* invoices and credits */
AND t.trandate >= TO_DATE('2025-10-01', 'YYYY-MM-DD') /* *** CHANGE start date *** */
AND t.trandate <= TO_DATE('2025-12-31', 'YYYY-MM-DD') /* *** CHANGE end date *** */
AND t.voided = 'F'
AND tl.mainline = 'T' /* one row per transaction, no line detail */
GROUP BY e.firstname, e.lastname
ORDER BY total_amount_usd DESC
FETCH FIRST 10 ROWS ONLY /* change to return more or fewer reps */
How the query works
Here is a simple breakdown of what the query is doing.
Selecting invoices and credits only
The query includes customer invoices (CustInvc) and customer credits (CustCred). Credits will reduce totals depending on how amounts are stored in your account.
Limiting to a specific date range
Transactions are filtered between 10/01/2025 and 12/31/2025. Users should update these dates to match the period they want.
Linking each transaction to its sales representative
The JOIN to employee connects each transaction to the assigned sales rep so results can be grouped and ranked.
Using mainline = T so each transaction is counted once
Joining transactionline with tl.mainline = ‘T’ ensures the query uses only the main summary line for each transaction and avoids double counting item lines.
Starting with net amount and converting it using consolidation
The query takes tl.netamount and passes it through BUILTIN.CONSOLIDATE so amounts are converted into the consolidated currency. The subsidiary ID is currently set to 1 and can be changed if you want results for a different subsidiary.
Backing out tax lines
For each transaction, a subquery sums the tax lines (taxline = ‘T’) and subtracts them from the consolidated net amount. If no tax exists, NVL defaults the tax subtraction to 0. This ensures totals reflect net invoiced revenue excluding tax.
Counting distinct transactions
COUNT(DISTINCT t.id) returns the number of invoice or credit transactions per sales rep during the period.
Ranking and limiting results
Results are sorted from highest to lowest net consolidated amount and limited to the top 10 sales reps.
Notes for customization
Users can adjust the following elements.
Date range: modify the trandate filters.
Transaction type: replace ‘SalesOrd’ with other transaction types if needed.
Subsidiary ID: change the value 1 inside BUILTIN.CONSOLIDATE to run the report for a specific subsidiary.
Result limit: adjust FETCH FIRST 10 ROWS ONLY to return more or fewer rows.