Profit21 NL

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."

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,
  SUM(COALESCE(r.extended_price, r.sales_price, 0)
      - COALESCE(r.cogs_amount, 0))                             AS gross_profit,
  CASE WHEN SUM(COALESCE(r.extended_price, r.sales_price, 0)) = 0 THEN 0
       ELSE CAST(
         SUM(COALESCE(r.extended_price, r.sales_price, 0) - COALESCE(r.cogs_amount, 0))
         / SUM(COALESCE(r.extended_price, r.sales_price, 0)) * 100
         AS DECIMAL(6,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;