Finance
Revenue
Invoiced product sales by month, branch, and product group. Revenue is net of tax, freight, and miscellaneous charges so the number ties to the Sales History view.
What Counts as Revenue
In Prophet 21, revenue is the invoiced value of product lines shipped to customers — the extended_price column on p21_sales_history_report_view.
This page excludes tax lines, freight and other charge lines, deleted invoices, and projected (pro-forma) orders. The result is a pure product-sales figure that ties to the same data feeding the Sales History and Sales Trends pages.
Per P21's own documentation: "Sales is calculated from invoices and roughly equates to revenue."
Margin Only Counts Costed Revenue
Revenue on this page ties exactly to the invoice register. Cost does not. Prophet 21 stamps cost onto the invoice line when it is invoiced, and many lines here were written with no cost at all — even where the item master holds one.
So every margin divides gross profit by only the revenue whose lines carry a cost, never by total revenue. Dividing by everything would credit uncosted lines with 100% profit, which is what previously produced margins like 99.5% for a month that simply had no cost recorded.
A margin marked * is based on less than 95% of that row's revenue — hover it for the exact share. Rows reading no cost data have no costed lines at all, so their margin is unknown rather than zero or perfect.
To fix this at the source, populate item costs in Prophet 21 before invoicing. Cost is captured at invoice time and cannot be recovered onto lines afterwards.
Why Monthly, Not Fiscal Period
Months are calendar months (truncated from invoice_date), not P21 fiscal periods. This keeps the view comparable even if your fiscal calendar shifts. The fiscal year and period are shown alongside each month for reconciliation.
The trailing 12 months (TTM) number is rolling, not calendar-year. That matches how the Sales Master Inquiry computes "Last 365 Days Sales" and lets you see the current run-rate without waiting for fiscal period close.
Three Ways to Define Revenue
1. Product revenue (this page): from p21_sales_history_report_view. Best for sales leadership — lets you slice by rep, branch, product group, customer.
2. Billed revenue: invoice_hdr.total_amount net of tax, freight, other. Best for the CFO — ties to the general ledger.
3. Recognized revenue: the GL journals posted to each revenue account. Required for GAAP-accurate reporting when progress billing, advance bills, or deferred revenue are in play.
SQL Behind This Page
Monthly revenue (Option 1 — from p21_sales_history_report_view)
SELECT
DATEFROMPARTS(YEAR(r.invoice_date), MONTH(r.invoice_date), 1) AS month_start,
MAX(r.year_for_period) AS fiscal_year,
MAX(r.period) AS fiscal_period,
SUM(COALESCE(r.extended_price, r.sales_price, 0)) AS revenue,
SUM(COALESCE(r.cogs_amount, 0)) AS cogs,
-- Profit and margin count ONLY lines that carry a cost. Dividing by total
-- revenue would treat uncosted lines as 100% profit.
SUM(CASE WHEN COALESCE(r.cogs_amount, 0) <> 0
THEN COALESCE(r.extended_price, r.sales_price, 0) - r.cogs_amount
ELSE 0 END) AS gross_profit,
SUM(CASE WHEN COALESCE(r.cogs_amount, 0) <> 0
THEN COALESCE(r.extended_price, r.sales_price, 0)
ELSE 0 END) AS costed_revenue,
CASE WHEN SUM(CASE WHEN COALESCE(r.cogs_amount, 0) <> 0
THEN COALESCE(r.extended_price, r.sales_price, 0) ELSE 0 END) = 0
THEN NULL -- no costed lines: margin is unknown, not zero
ELSE CAST(
SUM(CASE WHEN COALESCE(r.cogs_amount, 0) <> 0
THEN COALESCE(r.extended_price, r.sales_price, 0) - r.cogs_amount
ELSE 0 END)
/ SUM(CASE WHEN COALESCE(r.cogs_amount, 0) <> 0
THEN COALESCE(r.extended_price, r.sales_price, 0) ELSE 0 END) * 100
AS DECIMAL(7,2))
END AS gross_margin_pct,
COUNT(DISTINCT r.invoice_no) AS invoice_count
FROM dbo.p21_sales_history_report_view r
WHERE r.invoice_date >= DATEADD(month, -25, GETDATE())
AND COALESCE(r.other_charge_item, 'N') = 'N' -- exclude freight / misc
AND COALESCE(r.tax_item, 'N') = 'N' -- exclude tax lines
AND COALESCE(r.delete_flag, 'N') = 'N'
AND COALESCE(r.projected_order, 'N') = 'N'
GROUP BY DATEFROMPARTS(YEAR(r.invoice_date), MONTH(r.invoice_date), 1)
ORDER BY month_start;