Documentation / Experiences

Cross-System Reporting

A concrete walkthrough of building one live report that joins an ERP, a SQL database, and a SaaS tool at query time — how the engine splits the work per source, pushes down what it can, joins the rest in-engine, and returns a single shaped result with no warehouse and no nightly copy.

Cross-system reporting in Nexus lets you query several data sources at once and return a single, shaped result set — for example, joining NetSuite orders to a SQL Server customer table and rolling revenue up by region. This page is for analysts and admins who need a unified view across systems without standing up a data warehouse or copying source data anywhere. It walks a real report from problem to result and is honest about where the boundary between "pushed to the source" and "done in Nexus" actually falls.

The problem this solves

The question is simple and the plumbing is not: "revenue by region this quarter, by our own regional definitions." Orders live in the ERP. The mapping from customer to region lives in a CRM or a SQL Server table finance maintains. Neither system can answer the question alone, and the traditional answer — build a warehouse, copy both systems into it nightly, pre-join them, and report off the copy — means new infrastructure, a day of latency, and an ETL job to own forever.

Nexus answers it by letting each system do the part it is good at and stitching the results together at query time, live, inside your own tenant.

What a report actually is

A Nexus report is a JSON definition describing one or more queries against your configured bridges, optional joins between those queries, group-by aggregation, and final post-processing. The engine runs each query against its source, pushes filtering and aggregation down to the source where the adapter supports it, then combines and shapes the rest in the engine, and returns rows.

Because reports are declarative JSON — not executable code — they are safe to expose to non-admin users. Every report runs as the calling user and is bounded by that user's permissions. Data is read live at query time; nothing is staged or duplicated unless you explicitly choose to.

How the cross-system join works — and its honest limit

Each entry in a report's queries array targets one bridge and one collection. A joins entry combines two query results by their id, mapping a left field to a right field.

When both sides hit the same bridge and the adapter supports it, the engine can push the join into a single SQL statement.
When the two sides live in different systems — NetSuite and SQL Server — a single SQL query cannot span them. The engine runs each query against its own source and joins the two result sets in memory inside Nexus.

That in-engine join is exactly what makes cross-system reporting possible, and it is also the limit to be honest about: the join happens after each source returns its rows, so the join key columns must come back from both sides, and very large un-filtered result sets are joined in memory (bounded by a post-process row budget) rather than by the database. The design move is to filter aggressively at each source so only the rows you need cross the wire. For heavier multi-step shaping, do the combine in a pipeline or stage intermediates in the internal workspace.

The report, end to end

This report pulls sales orders from NetSuite and customer→region records from SQL Server, joins them on customer ID, and aggregates revenue by region.

{
  "report_name": "Revenue by Region (NetSuite + SQL)",
  "description": "ERP orders joined to the CRM customer table, summed by region.",
  "parameters": [
    { "name": "since",   "type": "string", "default": "2026-01-01" },
    { "name": "min_amt", "type": "number", "default": 0 }
  ],
  "queries": [
    {
      "id": "orders",
      "system": "netsuite",
      "bridge_name": "NETSUITE.prod",
      "containers": ["NETSUITE"],
      "collection": "transaction",
      "fields": ["entity", "amount"],
      "where": {
        "type": "SalesOrd",
        "trandate": { "$gte": "{{parameters.since}}" },
        "amount": { "$gt": "{{parameters.min_amt}}" }
      }
    },
    {
      "id": "customers",
      "system": "mssql",
      "bridge_name": "MSSQL.crm",
      "containers": ["dbo"],
      "collection": "customers",
      "fields": ["customer_id", "region"]
    }
  ],
  "joins": [
    { "left": "orders", "right": "customers",
      "type": "inner", "on": { "entity": "customer_id" } }
  ],
  "group_by": {
    "fields": ["region"],
    "aggregations": [
      { "type": "sum",   "field": "amount", "alias": "total_revenue" },
      { "type": "count", "field": "*",      "alias": "n_orders" }
    ]
  },
  "post_processing": {
    "order_by": [{ "field": "total_revenue", "direction": "desc" }],
    "limit": 25
  }
}

What happens when it runs, step by step

orders executes against NetSuite. The type, trandate, and amount filters render into a single SuiteQL WHERE and run server-side. Only matching orders come back.
customers executes against SQL Server. The field list renders into a T-SQL SELECT and pushes down.
Nexus performs the inner join in-engine, pairing each order's entity to a customer's customer_id.
The group_by rolls up in-engine, summing amount and counting orders per region. (Group-by across a cross-system join is always evaluated in the engine, because the joined set only exists in Nexus.)
post_processing sorts and trims to the top 25 regions and returns the rows.

The NetSuite filter pushes down to SuiteQL; the SQL Server query pushes down to T-SQL; the join and roll-up happen in Nexus. Which parts push down is a property of each adapter's declared capabilities — see the capability model — and it is a clean, visible fallback, never a silent wrong answer.

Filtering, grouping, and parameters

Reports use a consistent set of $-operators in where clauses across every adapter — $eq, $gt, $gte, $lt, $in, $between, $contains, $like, and boolean $and/$or/$not. Top-level conditions are ANDed. Pushdown support per source is summarized on the Connectors Overview; the exact operator set and its semantics are in the Operators Reference.

group_by supports sum, avg, count, min, max, and median, with an optional having clause. Final post_processing applies filter, sort, limit, select, and pivot in the engine.

Parameters make one definition serve many views. A parameter that resolves to "*" is dropped from the where clause — that is how you build an optional filter — and callers pass values at run time (region = "EU") which substitute before execution.

Live, not copied

Approach	Nexus cross-system report	Traditional warehouse
Data movement	None — read at query time	Nightly/scheduled copy (ETL)
Freshness	Live	As of last load
Infrastructure	Runs in your own tenant	Separate warehouse to provision
Cross-source join	In-engine, on demand	Pre-joined during load
Governance	Runs as the caller, RBAC-bounded	Depends on warehouse ACLs

When to reach for a pipeline instead

A report is the right tool when the answer is "query, join, aggregate, shape, return." Reach past it when you need to transform in multiple stages, call an external API mid-flow, write results back, or hold intermediate state across steps. Then use a pipeline: read each source through ctx.adapters, stage intermediates in ctx.workspace, and either return the shaped rows or write them somewhere. The reconciliation walkthrough in ERP & Bank Reconciliation is exactly this next step up.

Authoring loop

Validate the definition: uql_validate(kind="report", ...).
Save (upserts and auto-versions): uql_save(kind="report", id="...", ...).
Run: execute_report(report_id, parameters={...}) — as the calling user, bounded by their RBAC.

Saved reports are version-controlled, so you can view history, diff revisions, and restore an earlier definition. Surface the finished report in the Excel add-in with =STINGRAY.REPORT(), on a dashboard, or on a Site — the same governed definition, many front ends.