Consolidation: One Query Across Countries and Currencies

The Reporting Gap

A payroll provider running operations in eleven countries has eleven separate payroll result stores. Each store contains calculation outputs specific to its country: German results have Lohnsteuer, Solidaritätszuschlag, and Kirchensteuer. French results have prélèvement à la source and cotisations sociales. British results have PAYE and National Insurance. Each uses country-specific terminology, country-specific structures, and country-specific calculation logic.

Now the CFO asks a simple question: "What was our total employer cost across all European operations last quarter?" Or the finance team needs a report: "Compare tax withholding rates by country for the board meeting." Or an EOR client wants to know: "How much does it cost to employ my team of twelve people across Germany, France, and the Netherlands — all-in, employer costs included?"

These are straightforward business questions. Answering them with country-specific payroll outputs is anything but straightforward.

The traditional approach involves a chain of manual steps: export German results to a spreadsheet, export French results to another, export Dutch results to a third. Manually align the columns — because German "Arbeitgeberanteil Sozialversicherung" and French "Cotisations patronales" and Dutch "Werkgeversbijdrage" all mean "employer social security contribution" but appear in different formats, different column positions, and different currencies. Convert CHF and GBP to EUR using — which exchange rate? Spot? Monthly average? Period-end? Then sum the aligned, converted values and hope nobody introduced a formula error.

This works for a small operation running payroll in two or three countries. It does not work for a provider managing 200 client companies across eleven jurisdictions. It does not work when the question needs to be answered monthly, automatically, and with audit-trail precision. It does not work when the answer needs to be consistent across reports, periods, and consumers.

PayrollEx solves this with a uniform interface layer built into every country regulation. It doesn't require a separate ETL pipeline, a data warehouse, or a custom reporting stack. The consolidation capability is part of the regulation architecture itself.

The Consolidation Interface: WT 7000–7030

Every country regulation in PayrollEx — all eleven of them — includes a set of consolidation wage types numbered 7000 through 7030. These wage types exist specifically to provide a uniform, cross-country queryable surface. They map country-specific results into a common schema that any consolidation report can consume without knowing what country it's looking at.

The interface defines five standard consolidation wage types:

What makes this powerful is what each wage type aggregates under the hood — and how that aggregation is completely invisible to the consumer.

Take WT 7010 (Tax Withholding). In Germany, this is the sum of Lohnsteuer, Solidaritätszuschlag, and Kirchensteuer — three separate taxes with three separate calculation algorithms. In the United Kingdom, it's PAYE (Pay As You Earn) — a single progressive tax. In Spain, it's IRPF (Impuesto sobre la Renta de las Personas Físicas). In France, it's PAS (prélèvement à la source). Each country calculates tax withholding differently, using different tariffs, different allowances, different algorithms. But WT 7010 in every country answers the same question: "How much income tax was withheld from this employee this period?"

The same principle applies across all five consolidation wage types. WT 7020 (Social Security Employee) in Germany is the sum of KV, PV, RV, and AV employee contributions. In the Netherlands, it's ZVW plus Pensioenpremie. In Switzerland, it's AHV, IV, EO, and ALV employee shares. Different systems, different calculation models, same interface.

This means a consolidation query doesn't need country-specific logic. You don't write "if Germany, sum Lohnsteuer + SolZ + KiSt; if UK, take PAYE; if France, take PAS." You write "give me WT 7010 for all employees across all countries" and the regulation layer has already done the aggregation.

Single-Country Consolidation

The simplest consolidation case is one country, one currency, one tenant. A provider running German payroll for 50 client companies on a single tenant needs an aggregate view: total employer cost across all companies for Q1 2026, broken down by month.

This is a direct query against WT 7030 (Total Employer Cost) — filtered by period range and optionally grouped by division, department, or cost center. No currency conversion needed. No cross-tenant aggregation. The consolidation wage types are already present in every payroll result, so the query is a simple filter-and-aggregate operation on a single result store.

The Regulation.Consolidation package provides pre-built reports for this use case. A single-tenant employer cost report queries WT 7000–7030 across all employees for a specified period range and produces a structured output: per-employee detail, per-department subtotals, and company-wide totals. The report runs against the same payroll results that were already calculated — no re-computation, no separate batch job.

Use Cases for Single-Country Consolidation

• Monthly employer cost report — Total cost per department for the current period. Feeds into finance's monthly close process.
• Quarterly comparison — WT 7030 across Q1 vs. Q4 of the prior year. Identifies trends in employer cost growth.
• Tax withholding summary — WT 7010 aggregated for regulatory reporting. Supports annual tax filings and reconciliation.
• Headcount-weighted cost analysis — Average WT 7030 per employee by department. Identifies cost outliers.

Single-country consolidation is the foundation. It validates the interface — proving that WT 7000–7030 correctly aggregates country-specific wage types into meaningful business metrics. Every regional and global consolidation query builds on this same interface, applied across multiple tenants and currencies.

Regional Consolidation: DACH, Benelux, Iberia

Most multi-country providers don't operate globally from day one. They start regionally — a German provider expands into Austria and Switzerland (DACH). A Belgian bureau adds Dutch and Luxembourg clients (Benelux). A Spanish firm enters the Portuguese market (Iberia). Regional consolidation is the first cross-border step, and it comes with its own requirements.

Multi-Tenant Architecture

Each country regulation runs on a separate tenant — a separate database, a separate configuration, a separate compliance boundary. German payroll runs on one tenant with German regulations, Austrian payroll on another with Austrian regulations, Swiss payroll on a third with Swiss regulations. This isolation is intentional: country regulations have different lifecycle requirements, different update schedules, different data retention rules.

Regional consolidation queries across these separate tenants using ExecuteConsolidatedQuery — a multi-tenant query operation that aggregates results from multiple country tenants into a single report. The query specifies which tenants to include, which period range to cover, and which wage types to aggregate. The consolidation layer handles the cross-tenant data access.

DACH: Germany + Austria + Switzerland

DACH consolidation introduces the first multi-currency challenge. Germany and Austria use EUR. Switzerland uses CHF. A DACH employer cost report that sums German, Austrian, and Swiss employer costs needs a currency conversion step.

PayrollEx handles this with period-specific exchange rates. The January report uses January's average EUR/CHF rate. The February report uses February's rate. This produces consistent, reproducible results — running the same report twice produces the same numbers, unlike spot-rate conversion which varies by the second.

The DACH consolidation package (Regulation.Dach.Consolidation) provides regional reports tailored to the DACH market: Lohnkostenübersicht (employer cost overview) across all three countries, comparative tax withholding analysis, and social security contribution breakdown showing the structural differences between German SV, Austrian SV, and Swiss AHV/IV/EO/ALV systems — all through the uniform WT 7000–7030 interface.

Benelux: Belgium + Netherlands + Luxembourg

Benelux consolidation operates in a single currency (EUR) but spans three fundamentally different tax systems. Belgian Bedrijfsvoorheffing, Dutch Loonbelasting, and Luxembourg Retenue d'impôt each have different tariff structures, different allowances, and different social security models. Despite these differences, WT 7010 in each country reports "total tax withheld" and WT 7020/7025 report "social security employee/employer" — making cross-country comparison straightforward.

The Benelux consolidation package (Regulation.Benelux.Consolidation) produces Loonkosten reports across all three countries. Because there's no currency conversion needed, these reports are simpler to verify: the numbers in the consolidation report are the exact EUR amounts from each country's payroll results, summed directly.

Iberia: Spain + Portugal

Iberia consolidation — Spain and Portugal — also operates in a single currency (EUR) with two distinct social security models. Spanish Seguridad Social with its regimes and bases de cotización operates differently from Portuguese Segurança Social with its TSU (Taxa Social Única). The consolidation interface abstracts both into WT 7020 (employee share) and WT 7025 (employer share), enabling direct comparison.

The Iberia consolidation package (Regulation.Iberia.Consolidation) provides Costes Laborales reports covering both countries. A provider expanding from Spain into Portugal gets immediate comparative visibility: "How does total employer cost in Lisbon compare to Madrid for equivalent positions?"

Global Consolidation

Global consolidation is the full scope: all eleven countries, four currencies (EUR, CHF, GBP, USD), one unified report. This is where the WT 7000–7030 interface pays its greatest dividend. Without it, a global consolidation query would need to understand German Lohnsteuer, Dutch Loonbelasting, French PAS, British PAYE, American Federal Income Tax, Swiss Quellensteuer, and five more country-specific tax systems — just for the "tax withholding" column. With the interface, it queries WT 7010 across all tenants and gets a uniform, pre-aggregated result.

Multi-Currency Handling

Four currencies across eleven countries means every global report needs a conversion strategy. PayrollEx uses period-specific exchange rates — the exchange rate applicable for the payroll period being reported, not the current spot rate.

This is a critical distinction for financial reporting. A January payroll run should be reported at January rates regardless of when the report is generated. If you run the Q1 consolidation report in April, the January figures use January rates, February figures use February rates, March figures use March rates. This produces stable, reproducible numbers that reconcile with monthly financial statements.

The exchange rate table is maintained as configuration — updated monthly with the applicable rates for each currency pair. A provider can use ECB reference rates, internal treasury rates, or client-specific agreed rates depending on their contractual requirements.

The Global Query

A global consolidation query looks structurally identical to a single-country query — just applied across more tenants. "Give me WT 7030 for all employees across all countries for FY 2026" hits eleven tenants, retrieves their WT 7030 values, converts non-EUR amounts using period rates, and produces a unified report. No country-specific logic in the query. No conditional paths. No "if Germany do X, if UK do Y" branching.

This uniformity is the architectural payoff of the WT 7000–7030 interface. The country-specific complexity is handled once, inside each country regulation, when the consolidation wage types are calculated. The reporting layer above never sees that complexity — it sees five uniform numbers per employee per period, regardless of country.

Typical Global Consolidation Use Cases

• Annual employer cost comparison — WT 7030 across all countries for FY 2026, converted to EUR. Board-level reporting for global workforce cost.
• Tax burden analysis — WT 7010 as percentage of WT 7000, by country. Identifies high-tax vs. low-tax jurisdictions for workforce planning.
• Social security cost comparison — WT 7025 (employer share) by country. Reveals the true cost differential beyond gross salary — German employer SV costs differ significantly from UK NI employer contributions.
• Headcount and cost trend — Monthly WT 7030 across all countries for the trailing twelve months. Identifies growth trajectories and seasonal patterns.
• Client cost transparency — EOR providers showing their clients the all-in cost per employee per country, broken down into salary, tax, and social security components.

Architecture: Why Separate Tenants

The multi-tenant model — one tenant per country — is the architectural foundation that makes consolidation both possible and safe. It's worth understanding why this approach was chosen over the alternative (a single tenant with multi-country regulations).

Independent Lifecycle

Country regulations have independent update cycles. Germany publishes its annual PAP (Programmablaufplan) in December for January 1 application. The UK announces NIC rate changes in April. France updates PAS brackets in January. Switzerland revises AHV rates on a different schedule entirely. A multi-country tenant would need to coordinate all these updates simultaneously — a single deployment that touches eleven regulatory frameworks. Separate tenants allow each country to update independently: deploy the German 2027 regulation in December without touching UK, France, or any other country.

Independent Data Management

Separate tenants provide independent backup, restore, and deletion capabilities. A provider can:

• Delete a country tenant without affecting others — useful when offboarding a country or resetting test environments
• Backup selectively — the German tenant can have hourly backups while a smaller Luxembourg tenant runs daily backups, optimizing storage costs
• Restore independently — if a German payroll run produces incorrect results due to a data issue, restore the German tenant to its pre-run state without affecting French or Dutch operations running in parallel
• Scale independently — a tenant with 5,000 German employees needs different database sizing than a tenant with 50 Luxembourg employees

Compliance Boundaries

Separate tenants create clean compliance boundaries. German employee data resides in the German tenant. French employee data resides in the French tenant. There's no co-mingling of personal data across jurisdictions within a single database. This simplifies GDPR compliance (data residency), regulatory audits (scope is one country per tenant), and data access controls (a German payroll administrator doesn't need access to the French tenant).

The Consolidation Tenant

Consolidation itself runs on a separate tenant — distinct from any country tenant. This separation serves two purposes:

1. Independent lifecycle — The consolidation layer can be updated, reset, or redeployed without touching any country regulation. Exchange rate tables, report templates, and consolidation configuration change on a different schedule than statutory regulations.
2. Clean deletion — TenantDelete on the consolidation tenant removes all consolidation data and configuration without affecting payroll results in any country tenant. This is useful during development, testing, and environment management.

The Trade-Off

Multi-tenant architecture is not free. More tenants mean more databases, more connection strings, more deployment targets, more monitoring endpoints. A provider running all eleven countries has at minimum twelve tenants (eleven countries plus consolidation) — potentially more if regional consolidation packages run on their own tenants.

This is a deliberate trade-off: more operational infrastructure in exchange for cleaner isolation, safer updates, simpler compliance, and independent scaling. For a provider running payroll as a business — where correctness and compliance are existential requirements — the isolation benefits outweigh the infrastructure cost.

The result is a system where cross-country reporting doesn't compromise single-country correctness. A German employee's payslip is calculated entirely within the German tenant, using German regulations, against German statutory values. That result is authoritative and immutable. The consolidation layer reads it, converts it if needed, and aggregates it — but never modifies it. One query, eleven countries, four currencies, zero compromise on country-level precision.