01
About this report
One row per project with general ledger postings or time entries. Revenue is postings to income and other income accounts on lines that carry the project. Direct cost is postings to cost of sales, expense and other expense accounts on those lines, such as vendor bills and expense reports charged to the project. The report adds gross profit, margin, hours from time entries and revenue per hour. Amounts are to date, in base currency from the primary accounting book.
Click Revenue or Direct cost to open Project transactions filtered to that project and side. Time appears as hours only, so labour cost is included only where your account posts it to the general ledger. Filter Status to leave out closed projects.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Project | Customer | Status | Start date | Revenue | Direct cost | Gross profit | Margin | Hours | Revenue per hour |
|---|---|---|---|---|---|---|---|---|---|
| Harbor & Pine Co. : Retail rollout | Harbor & Pine Co. | In Progress | 2026-02-01 | 284,500 | 61,200 | 223,300 | 0.79 | 1,480 | 192.23 |
| Brightline Dental Group : Phase 2 | Brightline Dental Group | In Progress | 2026-05-12 | 96,200 | 38,400 | 57,800 | 0.60 | 610 | 157.70 |
| Summit Analytics LLC : Data platform | Summit Analytics LLC | Closed | 2025-09-01 | 188,000 | 22,150 | 165,850 | 0.88 | 940 | 200 |
| Maple Ridge Clinics : Support | Maple Ridge Clinics | In Progress | 2026-01-01 | 41,800 | 0 | 41,800 | 1 | 262.50 | 159.24 |
03
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT j.id AS project__id,
j.altname AS project,
NVL(cu.altname, cu.entityid) AS customer,
BUILTIN.DF(j.entitystatus) AS status,
TO_CHAR(j.startdate, 'YYYY-MM-DD') AS start_date,
NVL(f.revenue, 0) AS revenue,
NVL(f.cost, 0) AS direct_cost,
NVL(f.revenue, 0) - NVL(f.cost, 0) AS gross_profit,
(NVL(f.revenue, 0) - NVL(f.cost, 0)) / NULLIF(f.revenue, 0) AS margin,
NVL(h.hours, 0) AS hours,
NVL(f.revenue, 0) / NULLIF(h.hours, 0) AS revenue_per_hour
FROM job j
JOIN entity je ON je.id = j.id
LEFT JOIN entity cu ON cu.id = je.toplevelparent
LEFT JOIN (SELECT p.project_id,
SUM(CASE WHEN p.side = 'Revenue' THEN p.amount ELSE 0 END) AS revenue,
SUM(CASE WHEN p.side = 'Cost' THEN p.amount ELSE 0 END) AS cost
FROM (SELECT tl.entity AS project_id,
t.trandate AS tran_date,
t.type AS type_code,
NVL(t.tranid, t.transactionnumber) AS document,
a.displaynamewithhierarchy AS account,
CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN 'Revenue' ELSE 'Cost' END AS side,
CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN -tal.amount ELSE tal.amount END AS amount
FROM transactionaccountingline tal
JOIN accountingbook ab ON ab.id = tal.accountingbook AND ab.isprimary = 'T'
JOIN transaction t ON t.id = tal.transaction
JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a ON a.id = tal.account
WHERE tal.posting = 'T'
AND a.accttype IN ('Income', 'OthIncome', 'COGS', 'Expense', 'OthExpense')
AND tl.entity IN (SELECT j.id FROM job j)) p
GROUP BY p.project_id) f ON f.project_id = j.id
LEFT JOIN (SELECT tb.customer, SUM(tb.hours) AS hours
FROM timebill tb
GROUP BY tb.customer) h ON h.customer = j.id
WHERE f.project_id IS NOT NULL OR h.customer IS NOT NULL
ORDER BY revenue DESCSELECT j.altname AS project,
TO_CHAR(p.tran_date, 'YYYY-MM') AS period,
TO_CHAR(p.tran_date, 'YYYY-MM-DD') AS tran_date,
p.type_code AS type,
p.document,
p.account,
p.side,
p.amount
FROM (SELECT tl.entity AS project_id,
t.trandate AS tran_date,
t.type AS type_code,
NVL(t.tranid, t.transactionnumber) AS document,
a.displaynamewithhierarchy AS account,
CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN 'Revenue' ELSE 'Cost' END AS side,
CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN -tal.amount ELSE tal.amount END AS amount
FROM transactionaccountingline tal
JOIN accountingbook ab ON ab.id = tal.accountingbook AND ab.isprimary = 'T'
JOIN transaction t ON t.id = tal.transaction
JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a ON a.id = tal.account
WHERE tal.posting = 'T'
AND a.accttype IN ('Income', 'OthIncome', 'COGS', 'Expense', 'OthExpense')
AND tl.entity IN (SELECT j.id FROM job j)) p
JOIN job j ON j.id = p.project_id
WHERE p.amount <> 0
ORDER BY project, p.tran_date DESC04