Open Dental Production Report by Provider

If you need to see exactly how much production each provider generated over a specific period, the standard reports might not always give you the exact breakdown you need. This custom SQL query provides an accurate open dental production report by provider, allowing you to track performance and compensation metrics in seconds.

The Query

Copy and paste the following code into your User Query window. You can adjust the dates in the first two lines to match the period you want to analyze.

SET @FromDate = '2026-01-01';
SET @ToDate = '2026-01-31';

SELECT 
    p.Abbr AS Provider,
    SUM(pl.ProcFee * pl.UnitQty) AS TotalProduction
FROM procedurelog pl
INNER JOIN provider p ON pl.ProvNum = p.ProvNum
WHERE pl.ProcDate >= @FromDate 
  AND pl.ProcDate <= @ToDate
  AND pl.ProcStatus = 2 -- Only includes 'Complete' procedures
GROUP BY p.ProvNum
ORDER BY TotalProduction DESC;

How to Run This Query

1. In Open Dental, go to Reports in the Main Menu.
2. Click User Query.
3. Paste the query provided above into the large text box.
4. Click Submit Query.
5. The results will display in the grid below the text box.

Understanding the Results

• Provider: This column displays the provider abbreviation as set up in your Setup > Definitions > Providers list.
• TotalProduction: This is the sum of the procedure fees for all completed procedures assigned to that provider within your chosen date range. It accounts for the quantity of units performed (e.g., if a procedure has a UnitQty of 2, it calculates the fee accordingly).

How to Customize

You can easily modify this query to fit your specific needs:

• Change the Date Range: Simply edit the dates inside the single quotes in the first two lines:
  SET @FromDate = '2026-02-01';
SET @ToDate = '2026-02-28';
• Filter by Clinic: If you have multiple locations and only want to see data for one, add this line before the GROUP BY clause:
  AND pl.ClinicNum = 1
  (Replace '1' with your specific ClinicNum. You can find this in the Manage Module under Clinics.)

Variations

Include Adjustments

If you want to see production minus adjustments (like write-offs or discounts), you would need to join the adjustment table. However, for a simple production report, most offices prefer to keep production and adjustments separate to maintain a clear view of gross production.

Filter by Procedure Category

If you only want to see hygiene production, you can filter by the procedure code category:

-- Add this line to the WHERE clause
AND pl.CodeNum IN (SELECT CodeNum FROM procedurecode WHERE ProcCat = 1)

(Note: You will need to verify the ProcCat number that corresponds to your hygiene category in Setup > Definitions > Proc Code Categories.)

Skip the Query — Use DentalCanvas Instead

Don't want to write SQL? DentalCanvas connects to your Open Dental database and shows you this data automatically in a visual dashboard — no queries required.

This article is provided by opendentalsupport.com, an independent community resource. We are not affiliated with Open Dental Software, Inc.