Ask a Voyager admin for "the rent roll table" and you'll get a tired look. There isn't one. A rent roll is a point-in-time snapshot — which tenants occupy which units, and what they're being charged, as of a specific date — assembled at query time from several tables that were never designed to answer that question directly. Get the assembly wrong and the query still runs. It just quietly lies.
Here's the conceptual shape of a correct one, and the three traps that account for almost every wrong rent roll we've seen drafted from scratch.
The shape of the query
A rent roll answers one question for a chosen snapshot date: for this property, who's in each unit, and what are they being charged right now? That decomposes into three joins:
- Tenancy — which tenant occupies which unit, on which property. This is the
TENANT→UNIT→PROPERTYchain, and it's the backbone every other join hangs off. - Active charges — what that tenant is currently being billed. In Voyager, recurring charge schedules live in a dedicated rule table (
CAMRULE), keyed to the tenant and carrying its own from/to date window — it is not the same table that stores posted transaction history. Confusing the two is trap #1, below. - Charge descriptions — a lookup table (
CHARGTYP) resolves each charge row to a human-readable name, because the rule table itself stores a charge-type code, not text.
That's the whole conceptual skeleton: tenancy joined to property, left-joined to the active charge schedule, joined to the charge-type lookup for labels. Everything else is filtering.
Trap 1: recurring charges aren't in the transaction table
The instinctive design is to pull charge amounts from wherever transactions live, since that's where money ultimately shows up. That's the wrong table for a rent roll. Transaction-level tables hold posted line items — history, one row per billing event, with no concept of "what's the schedule right now." A rent roll needs the schedule, not the history: the recurring-charge rule that says "this tenant pays this amount for this charge type, effective these dates," independent of whether it's been billed yet this month.
Pull from the transaction ledger instead and you get either zero rows (nothing posted yet this period) or a pile of historical duplicates, depending on the date range — neither is a rent roll.
Trap 2: current vs. historical needs an explicit filter, twice
Two different "current" filters have to both fire correctly, and they're easy to get backwards.
Tenant status. Voyager tracks whether a tenant record is the currently active occupant or a past one via a status flag. Filter the wrong direction and your rent roll lists vacated tenants instead of current ones — the query returns rows, looks plausible, and describes a unit's history instead of its present. Always confirm which value means "current" against the schema before trusting a filter you copied from somewhere else; a flipped boolean or status code is the single most common way a rent roll query silently inverts its own population.
Charge-schedule window. The rule table's active window is its own from/to date pair, plus an inactive flag — none of which automatically track the tenant status filter above. A charge schedule can outlive a tenant record, or start before the snapshot date and end after it. The correct filter is: schedule start on or before the snapshot date, and schedule end either null or on/after the snapshot date, with the inactive flag clear. Skip any piece of that and you'll pick up charges that have already lapsed, or miss ones that just started.
And a filter that's easy to forget entirely: not every row in the property table is a real, billable property. Voyager installs commonly carry model, budget, or list-type property records alongside real ones, distinguished by a property-type code. Leave that filter off and your "per-property" rent roll includes rows for properties nobody actually leases units in.
Trap 3: what looks like double counting usually isn't
A tenant paying base rent plus a CAM charge plus a utility recovery shows up as three rows in a correctly built rent roll — one per charge type, each carrying its own amount. That's not a bug. If you SUM() blindly across those rows expecting "total rent," you'll get the right total-charges figure but the wrong "rent" figure, because CAM and utilities aren't rent. Decide up front whether your rent roll reports per-charge-type detail (the honest default) or a rolled-up total, and if it's the latter, filter to the specific charge type(s) that make up rent before summing — don't sum everything and relabel the column.
The real double-counting risk is different: joining through a table that carries one row per billing event instead of one row per active schedule. That's trap #1 again, wearing a different hat — it multiplies rows by however many periods have been billed, and a naive SUM() on top of that inflates the monthly figure by however many months of history got pulled in.
One more wrinkle: what the amount field actually means
Even once you're reading from the right rule row, the monthly-amount field's exact meaning can vary by how a given install is configured — some conventions store it as the literal monthly figure, others store an annual figure divided down. This is exactly the kind of install-specific convention that's worth confirming against your own instance's billing setup before you trust a number in a report, rather than assuming it travels the same way everywhere.
Voyager's own UDF layer treats "as of" as a first-class idea, for what it's worth — functions like unit-area and lease-area calculators take an as-of date as a parameter rather than assuming "right now," because the platform itself was built around the fact that occupancy and charges are moving targets. A hand-drafted rent roll query should treat the snapshot date with the same seriousness.
Draft it instead of reverse-engineering it
This is the exact kind of query PropETL's SQL Query Assistant exists for: it carries the standard Voyager schema — tables, columns, join paths, and a library of execution-verified ySQL patterns including rent rolls — so you get the tenancy → property → active-charge → charge-type join chain right the first time, with the current-vs-historical and status-flag traps called out instead of discovered in production. It's included in every tier, including the free 7-day trial — no card required.