Combining Data from Multiple Datasources

Show data from more than one datasource in a single visual — by joining or stacking visuals you already have, or by writing one query that reaches several datasources.

A visual normally reads from one datasource. When the data you need is split across several — two databases on different servers, or a database and an uploaded file — there are five ways to bring it together.

You want to…	Use	Needs SQL?
Match rows from two or more visuals on a shared column	Join	No
Put the rows of several visuals one under another	Merge Results	No
Combine visuals with your own logic	Merge with Python	Python
Fetch parts of one query from different datasources	Tagged CTEs `[{id}/name]`	Yes
Join tables of several imported datasources directly	Table references `[{id}/table]`	Yes
Run a whole query on a different datasource	`:USE_DS:`	Yes

Combining Visuals in the Report Builder

Join, Merge Results and Merge with Python build a new visual out of visuals already on the report. Each source visual keeps its own datasource, so the sources can come from anywhere.

Build and save each source visual. Unsaved visuals can't be combined — you'll see Save the visualizations before adding them to a multi-query visualization.
Create a new, empty tile for the result and click it to select it.
Hold Ctrl (⌘ on Mac) and click each source visual. Once two or more are selected, the combine options appear on the tile you selected first.
Choose Join, Merge Results or Merge with Python.

Filters on the combined visual are passed on to every source visual.

Join

Join matches rows across visuals, like a SQL join. Choosing it opens Configure JOIN:

Join conditions — click New Relationship and pick an object (a source visual) and attribute on each side. Two conditions between the same pair of visuals must both match.

Show all values from — decides which rows survive:

Option	Rows kept	SQL equivalent
Matching in all visualizations (default)	Only rows that match in every visual	Inner join
All values from all visualizations	Every row from every visual, matched where possible	Full outer join
A visual's name	Every row of that visual, plus matches from the others	Left join from that visual

Select columns to display — tick the columns to keep from each visual. All are ticked at first.

Click Save. The joined columns are named after the visual they come from, so two visuals can each contribute a column with the same name.

Merge Results

Merge Results stacks the rows of each source visual one after another, the way a SQL UNION ALL does. Columns with the same name line up. A column that only some visuals have is left blank for the rest.

This works best when the source visuals report the same thing for different sources — for example, the same output figures from two production lines that live on two servers.

Merge with Python

Merge with Python opens the Python editor with the source visuals' data instead of a single table. In the script, data is a dictionary of pandas DataFrames, one per source visual, keyed by each visual's saved id. Combine them however you like and return a DataFrame:

Python

frames = list(data.values())
merged = frames[0].merge(frames[1], on='LINEID', how='left')

result['type'] = 'DATA'
result['res'] = merged

Parameters and drill level on the combined visual are passed on to every source visual.

Query: CTEs on Other Datasources

In a query-based visual, tag a CTE with a datasource id to run that part of the query there:

SQL

WITH [{262}/scans] AS (
    SELECT LINEID, SERIAL_NO, CreateTime
    FROM WIP_SN_COLLECTION_INFO
    WHERE SITE_NAME = 'Welding layer'
),
[{115}/targets] AS (
    SELECT LINEID, ShiftTarget FROM LINE_TARGETS
)
SELECT s.LINEID, COUNT(DISTINCT s.SERIAL_NO) AS Output, MAX(t.ShiftTarget) AS Target
FROM scans s
JOIN targets t ON t.LINEID = s.LINEID
GROUP BY s.LINEID

Here scans runs on datasource 262 and targets on datasource 115. Write each one in its own database's SQL. Refer to them by name only (scans, not [{262}/scans]).

The tagged CTEs run first, in the order written, and their results are held by PersivX. A later CTE can use an earlier one's result, even across datasources.

⚠️ The Final SELECT Runs Inside PersivX, Not on Your Database Everything after the last tagged CTE — the final SELECT and any untagged CTE written after the tagged ones — runs in PersivX's own engine (DuckDB). Use standard SQL there: database-specific functions like GETDATE() or DATEADD() belong inside the tagged CTEs.

Rules and Limits

The query must start with WITH. A tag only counts in the form [{id}/name] AS (.
An untagged CTE written before the last tagged one runs on the visual's own datasource.
Don't put a nested WITH inside a tagged CTE. Split it into several tagged CTEs on the same datasource instead.
Each tagged CTE can return at most 1,000,000 rows.
When a later tagged CTE on a database uses an earlier CTE's result, that result is sent to the database written out row by row. Keep such intermediate results small.
Query hints (:USE_PARAM:, what-if parameters, filter hints) work anywhere in the query, including inside tags: [{:USE_PARAM:[Params].[line]:l3(262):l4(263):}/scans] picks the datasource from a parameter.

⚠️ Data Governance Data governance rules (masking and row-level rules) are not applied to queries that use tagged CTEs. Don't use them on data that relies on governance to restrict what readers see.

Query: Tables of Other Imported Datasources

When the visual's own datasource is an imported datasource (files, API, or a workspace), its query already refers to tables as [table]. Tables of another imported datasource can be used in the same way, with that datasource's id in front:

SQL

SELECT o.region, SUM(o.amount) AS sales, MAX(t.target) AS target
FROM [orders] o
JOIN [{262}/targets] t ON t.region = o.region
GROUP BY o.region

Everything runs in one go inside PersivX, so there are no row limits beyond the data itself. This only works between imported datasources; for a live database, use tagged CTEs.

Running a Whole Query Elsewhere

If every table the query needs is in one other datasource, you don't need to blend at all. Add :USE_DS: to run the entire query on that datasource in its own SQL dialect, optionally choosing the datasource with a parameter. See Running the Query on Another Datasource.

💡 Which One? Prefer the builder options (Join, Merge Results) when you already have the source visuals — they need no SQL and each part keeps its own filters. Reach for tagged CTEs when the combination has to happen before aggregation, or the logic doesn't fit a single join.