Module 7: Reports and Queries

Open the Standard Reports window in Open Dental and you will count well over forty reports. That is useful, and also the problem: when everything is available, owners tend to glance at a production number, feel good or bad, and close the window. The reports that change decisions are fewer, and each one only makes sense if you know exactly what it counts and what it leaves out.

This module does three things. It maps business questions to the reports that answer them. It explains the Production and Income report in enough depth that you can reconcile it to your bank account. And it shows how to use User Query and Query Favorites safely, with a few simple read-only examples. The quality of every report here depends on how cleanly your team posts, so if you have not read Module 6, start there.

Where the reports live and who can see them

The Reports menu contains Standard, Graphic, User Query, eService Activity Log, and Unfinalized Payments. Standard Reports groups its reports into sections:

Reports a user cannot open show [Locked] after the name. Grant or deny access per report in Setup > Reports (Report Setup, Security Permissions). A separate Production and Income permission for viewing all providers controls whether a user can see production for providers other than themselves. Associates usually should see their own numbers; front desk staff usually should not see everyone's. Report Setup: Display Settings lets you mark favorites so the reports you run weekly are at the top.

Reports > Graphic offers five visual reports (Production, Income, Accounts Receivable, New Patients, and Broken Appointments) for the practice, by provider, or by clinic. They need the Graphical Reports permission. Use them for trends at a monthly meeting; use the text reports for anything you plan to reconcile.

Which report answers which question

For the business meaning behind these numbers, including which KPIs are worth tracking and which are noise, read The Dental Practice KPIs Worth Tracking.

Production and Income, explained properly

Open it from Reports > Standard using one of the date shortcuts (Today, This Month, and so on) or More Options for the full window.

The four report types

• Daily: transaction-level detail (procedures, adjustments, payments) for a date or range. Use it to investigate.
• Monthly: totals by day across a range, and it also shows scheduled production. Use it for pacing.
• Annual: totals by month. Use it for year-over-year trends.
• Provider: totals by provider for a range. Use it for provider performance and compensation.

Options include provider and clinic selection (with "All (Includes hidden)" to catch providers who have left), Include Unearned to add an unearned income column on Monthly, Annual, and Provider types, and clinic display options for multi-location offices.

What the numbers mean

Open Dental defines production as what the practice expects to collect, and income as money actually received. Gross production is the sum of fees for completed procedures. Net production adjusts gross production for adjustments and insurance write-offs. Income is split into insurance income and patient income. Offices with no PPO plans have no contractual write-offs, so their total production already reflects adjustments.

The write-off setting that changes everything

The Show Insurance Write-offs option has three choices, and they place PPO write-offs on different dates:

No option is "right." What matters is that the whole office uses the same one every time, and that anyone comparing numbers (an owner, a CPA, a buyer doing due diligence, an associate checking pay) knows which one was used. If you pay associates on adjusted production, write the setting into the compensation calculation so there is no dispute later.

Why production by provider can be wrong

The manual warns that when payments are not allocated carefully, income drifts toward the practice's default provider, and that a patient's primary provider assignment matters. Three habits fix most of it: set each procedure's provider correctly in the chart, use line-item payment allocation (see Allocations Setup in Module 6), and keep patients' primary providers current. If you are buying a practice, ask for this report by provider for several years and ask how allocation was configured; our acquisition guide covers what else to request.

A worked reconciliation (hypothetical example)

Hypothetical month: gross production of $120,000, PPO write-offs of $22,000, and net negative adjustments of $3,000. Net production is $120,000 minus $22,000 minus $3,000, which is $95,000. If income for the month was $91,000, the collection rate on net production is about 96%. Reconcile the income figure to the month's deposits: the difference should be explainable by card batches in transit, refunds, and any payment types you exclude from deposits. If you cannot explain the gap, look for unfinalized insurance payments and deleted or backdated transactions in the Audit Trail.

Treatment acceptance and unscheduled treatment

Presented treatment reports

Two Monthly reports measure what happens to treatment you present. Presented Treatment Production totals production from completed procedures that were on saved treatment plans in a date range, and can attribute it to the person who presented the plan. You choose which presenter gets credit when a procedure appeared on more than one plan, and whether you want a detailed or totals report. Treatment Plan Presentation Statistics is the companion report for presentation activity. Both rely on your team actually saving treatment plans when they present them, which is a habit covered in Module 4.

The Treatment Finder

The Treatment Finder report (Standard Reports, Lists) finds active patients with unscheduled planned treatment and shows their annual max, amount used, pending, remaining, and treatment plan total. Filter by insurance benefit month, amount remaining over a threshold, treatment plan dates, provider, billing type, and code range, and you can include patients with no insurance. From the grid you can print letters or labels, export, or right-click to jump to the patient.

Hygiene reappointment and recall

Hygiene reappointment (the share of hygiene patients who leave with their next visit booked) is one of the most useful numbers in a practice, but Open Dental's Standard Reports list does not include a report with that name. You measure it two ways.

The Recall List, operationally

Open Appointment Lists, then Recall. Set a due date range and recall type (Prophy, Perio, or all), filter by provider and clinic, and the list shows patients due who do not have a recall appointment scheduled. Leave the From date blank to include patients who never completed a recall. The Reactivations view lists inactive patients not seen recently. Schedule directly from the list with Sched Patient or Sched Family. How recall types and intervals are set up is covered in Module 3.

A reappointment rate, with a query

For a rate, use a simple query (example below in the query section) that counts patients with a completed hygiene appointment last month and how many of them currently have a future scheduled appointment. It is a snapshot of today, so run it on the first business day of the month for consistency. For what a healthy hygiene department looks like financially, see Is Your Hygiene Department Profitable?

Aging and receivables reports, briefly

Module 6 covers aging in depth. For reporting purposes, remember three things. The Aging of A/R report's As Of date defaults to the most recent aging calculation, so if aging has not run, the report is stale. The Insurance Aging report shows estimates by age, while the Outstanding Insurance Claims report is the one for chasing carriers. The Monthly section also has Custom Aging and Receivables Breakdown reports for other views of receivables; open the manual page for each before relying on them in a board or bank meeting. Track the share of patient A/R over 90 days monthly; a rising number is a process problem long before it is a cash problem.

User Query and Query Favorites

When no standard report answers your question, Open Dental lets you query its MySQL database directly. Open Reports > User Query (it requires the User Query Admin permission). Type or paste SQL, click Submit Query, and results appear in a grid you can sort, print, or export to a text or spreadsheet file. Human-readable format turns dates, amounts, provider numbers, and status codes into readable values; Raw shows the stored values.

Query Favorites (the Favorites button in User Query) stores saved queries. This is the right way to give staff access to custom reports. Users without User Query Admin can run only favorites marked as released, which means an office manager can write and test a query once, release it, and let the team run it without being able to type their own SQL.

Rules for querying safely

• Read-only only. Run statements that begin with SELECT. Never run UPDATE, DELETE, INSERT, DROP, ALTER, or TRUNCATE against your live database, no matter who sent you the query or what forum post it came from. A single bad UPDATE can change thousands of rows with no undo short of restoring a backup.
• Verify names for your version. Table and column names change across versions. Check them against Open Dental's published database schema documentation for the version you run.
• Start from Open Dental's examples. Open Dental publishes a large list of example queries. Adapting one of those is safer than writing from scratch.
• Mind performance. A query that joins big tables across years can slow every workstation. Run heavy queries after hours, and limit date ranges.
• Protect the output. Query results often contain PHI. Exported files belong in secure storage, not on a desktop or in email.

Four simple read-only example queries

The SQL is shown as plain text below; copy it line by line into the User Query window. These use tables and columns that appear in Open Dental's published documentation and example queries. Status values used below (ProcStatus 2 for complete, PatStatus 0 for active patient, AptStatus 1 for scheduled and 2 for complete, ClaimStatus 'S' for sent) match Open Dental's examples, but confirm them against the schema documentation for your version before relying on the results. Change the dates to your range. Test on a copy of the database if you have one.

1. Gross production by provider for a month

SELECT pr.Abbr AS Provider,
COUNT(*) AS Procedures,
SUM(pl.ProcFee * (pl.UnitQty + pl.BaseUnits)) AS GrossProduction
FROM procedurelog pl
INNER JOIN provider pr ON pr.ProvNum = pl.ProvNum
WHERE pl.ProcStatus = 2
AND pl.ProcDate BETWEEN '2026-08-01' AND '2026-08-31'
GROUP BY pr.Abbr
ORDER BY GrossProduction DESC;

This is gross production only. It will not match the Production and Income report's net figures, because it ignores adjustments and write-offs. Its value is as a cross-check: if gross production here and in the Provider report disagree, find out why.

2. Most frequently completed procedure codes

SELECT pc.ProcCode, pc.Descript,
COUNT(*) AS TimesCompleted
FROM procedurelog pl
INNER JOIN procedurecode pc ON pc.CodeNum = pl.CodeNum
WHERE pl.ProcStatus = 2
AND pl.ProcDate BETWEEN '2026-01-01' AND '2026-08-31'
GROUP BY pc.ProcCode, pc.Descript
ORDER BY TimesCompleted DESC
LIMIT 25;

Useful before a fee review or a PPO negotiation: these are the codes where a fee change moves the most money.

3. Sent claims older than 30 days, by carrier

SELECT ca.CarrierName,
COUNT(*) AS Claims,
SUM(c.ClaimFee) AS BilledAmount
FROM claim c
INNER JOIN insplan ip ON ip.PlanNum = c.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum = ip.CarrierNum
WHERE c.ClaimStatus = 'S'
AND c.DateSent < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY ca.CarrierName
ORDER BY BilledAmount DESC;

This is a summary view; work the actual claims from the Outstanding Insurance Claims report. As written it may include preauthorizations, which are also stored as claims. Check the schema for your version's claim type column before adding a filter for them.

4. Hygiene reappointment snapshot

SELECT COUNT(DISTINCT a.PatNum) AS HygienePatientsSeen,
COUNT(DISTINCT f.PatNum) AS WithFutureAppointment
FROM appointment a
LEFT JOIN appointment f
ON f.PatNum = a.PatNum
AND f.AptStatus = 1
AND f.AptDateTime > NOW()
WHERE a.AptStatus = 2
AND a.IsHygiene = 1
AND a.AptDateTime BETWEEN '2026-08-01' AND '2026-08-31 23:59:59';

Divide the second number by the first for the rate. This depends on your office marking hygiene appointments as hygiene; if your team does not use that flag consistently, the count will be off, and fixing the habit is the first step. It counts any future scheduled appointment, not only recall.

A monthly reporting routine

Hypothetical example of a routine that works for a single-location general practice. Adjust to your own meeting rhythm.

1. First business day: confirm every insurance payment from last month is finalized (Unfinalized Payments count is zero), deposits are reconciled, and aging has run.
2. Run Production and Income, Provider type, for last month with your standard write-off setting. Save it as a PDF.
3. Run Aging of A/R and the Outstanding Insurance Claims report. Note patient A/R over 90 days and insurance claims over 60 days.
4. Run the hygiene reappointment query and the Treatment Plan Presentation Statistics report.
5. Run New Patients and Referral Analysis for marketing review (see Marketing and Patient Acquisition).
6. Set the global lock date to the last day of the month once the numbers are final.
7. Put five or six numbers on one page and review them with the team. Trends matter more than any single month.

Practice exercises

What's next

Reports are only as trustworthy as the database behind them, and a database is only as safe as its last tested backup. Module 8: Administration, Backups, and eServices covers security, backups and disaster recovery, multi-location setups, eServices, updates, and troubleshooting. For turning these reports into owner decisions, see Financial Management for Practice Owners.

ChairsideSource is not affiliated with or endorsed by Open Dental Software, Inc. This is independent educational content. Menus and features change between versions: verify against the official Open Dental manual and contact Open Dental support before changing settings that affect live patient data. This guide is educational content and does not constitute legal, financial, tax, or clinical advice. Laws and regulations vary by state and change over time. Consult your own dental-specific attorney, CPA, and state dental board before acting.