When two CCBHC reports disagree: where to look first

Few things create distrust in data faster than putting two reports side by side and realizing they do not agree.

One report says 1,184 people were served. Another says 1,247. A dashboard shows a follow-up rate of 71 percent, while the file prepared for submission shows 66 percent. Both numbers came from systems your organization uses every day, and both may have been produced by people who know what they are doing.

The first reaction is often to ask which number is correct. That question matters, but it is usually too early.

Start with a different question: Are these reports actually measuring the same thing?

Start with the definition, not the spreadsheet

Before reviewing formulas or pulling another extract, compare the definitions behind the two results. What population is included? What time period is being used? Is the report based on encounter date, admission date, discharge date, or another event? Are clients counted once or once per episode? How are voided encounters, test clients, duplicate records, or services outside the CCBHC scope handled?

Two reports can be technically accurate and still produce different results because they answer different questions.

This is especially important in CCBHC reporting because an organization may be managing federal quality measures, grant performance reporting, state requirements, internal KPIs, operational dashboards, and ad hoc leadership requests at the same time. Similar labels do not guarantee identical specifications.

Then trace the data backward

If the definitions are supposed to match, follow the number back to its source. Do not stop at the final workbook.

Identify the exact fields feeding each report. Confirm which system they came from. Look at how those fields are populated in practice. A field may exist in the EHR but still be unreliable because staff use it inconsistently, documentation happens in free text, workflows changed without the report changing, or one program uses the field differently from another.

This is where reporting problems often turn out to be workflow problems.

SAMHSA's CCBHC guidance explicitly connects quality-measure preparation with technical infrastructure, clinical and administrative workflows, documentation, and reporting. That connection matters. A clean formula cannot repair data that were captured inconsistently at the point of care.

Check the transformation logic

Once the source data are understood, compare what happens between the source and the final result.

Look for filters, joins, deduplication rules, date logic, exclusions, null handling, calculated fields, and manual adjustments. Pay particular attention to anything that happens outside the primary reporting system. A spreadsheet that someone has been updating for three years may contain perfectly reasonable logic that was never documented. It may also contain logic that made sense three years ago and no longer reflects the current specification.

For externally required measures, the authoritative specification controls. Internal documentation should show how that specification has been implemented locally, including the source fields and calculation logic used to produce the result.

Version control matters more than people think

Sometimes both reports were built correctly, just at different points in time.

Measure specifications change. Reporting periods change. EHR fields are remapped. Programs change workflows. Analysts correct logic. If an organization cannot tell which version of a definition or calculation was in effect for a particular reporting period, discrepancies become much harder to resolve.

A useful measure dictionary should answer basic questions without requiring institutional memory: What is the measure? What is the authoritative source? What fields feed it? What logic is applied? When did that logic become effective? Who approved the change?

Do not solve a governance problem with another report

A common response to conflicting numbers is to build a third report. That may temporarily settle the immediate question, but it can also create one more version of the truth.

The better outcome is a controlled reporting process. One documented specification. Defined source fields. Reproducible logic. Named ownership. Validation before submission. A record of material changes.

The standard I use is simple: a measure is not fully defined because everyone on the current team knows what it means. It is fully defined when another qualified analyst can reproduce the same result from the documentation and source data.

When two CCBHC reports disagree, the discrepancy is useful information. It is telling you where your measurement system needs attention. Find that point, fix the underlying process, and document the resolution. That is much more valuable than forcing two spreadsheets to match.