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.
SELECT
e.firstname || ' ' || e.lastname AS sales_rep_name,
COUNT(DISTINCT t.id) AS total_invoices,
SUM(
TO_NUMBER(
BUILTIN.CONSOLIDATE(tl.netamount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, t.postingperiod, 'DEFAULT')
) - NVL(
(
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')
AND t.trandate >= '10/01/2025'
AND t.trandate <= '12/31/2025'
AND t.voided = 'F'
AND tl.mainline = 'T'
GROUP BY
e.firstname,
e.lastname
ORDER BY
total_amount_usd DESC
FETCH FIRST 10 ROWS ONLY
Sample Output
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.