Join views across sources#

A joined view combines the rows of other views of the same data model into one view. Its inputs are usually views of different sources: a forecast from a planning cube beside actuals from the ERP, or the sales reps in a CRM export beside the employees in an HR workbook. Report authors use a joined view exactly like any other view.

A joined view does not query a source itself. Each input view runs on its own, as last saved, and then Reportworq matches the rows. That is why a joined view can combine sources that could never be joined in one SQL statement.

Joining tables of one SQL database? Use the view designer instead. It joins tables of one source inside the database, which is faster. See Build a view with the designer and Table relationships.

How a joined view works#

A few rules shape everything else on this page:

• A joined view reads only views of its own data model. A data model is self-contained: anyone who can see the model can report on it, so a joined view never reaches into another model.
• The views are related once, on the model. A relationship between views says which fields match and how. It is stored on the data model, and every joined view that joins the same two views uses it. You create, edit, and delete these relationships where you use them, in a joined view's editor.
• Inputs run as last saved. Save a change to an input view before you rely on it in a joined view.
• An exact match reads less. When a match is exact, Reportworq asks the related view's source for only the rows whose keys it has already found. The matching options that make a match fuzzy (ignoring case or surrounding spaces, or letting empty values match), and turning off Fetch only rows that can match, make Reportworq read every row of the related view and compare the rows itself.
• A joined view must be saved to run unattended. A scheduled job or an API call runs a joined view only once it is saved in the data model.

Before you begin#

• A data model with at least two views to join, saved. See Data models.
• You have permission to author data models in the workspace.

Build a joined view#

1. Open the data model. In the rail, select + Add, then select Joined view. You can also select the plus sign beside the Joined views folder in the rail.

   A new view named Joined view opens on its canvas.

2. Under Pick a view…, select the view to start from. Its rows are the ones every join hangs off, for example the sales journal when each row of the result is one sale.

3. On the starting view's card, select Join a related view, then select the view to join. The list shows the views the model already relates to this one, grouped by source.

4. If the view you want is not listed, select Relate another view…. The relationship page opens with this view filled in. Relate the two views (see Relate two views), select Save and Close, and the view joins.

5. On each card, select the check boxes of the fields to carry into the joined view. A card's key fields are always carried.

6. Repeat steps 3 to 5 on any card to join more views.

7. Select Save Changes.

A joined card's strip shows the keys it joins on and a pencil that opens the relationship. A connector line runs from the starting card to the field the card is joined on. A strip can also carry a short tag, described in the following table.

Relate two views#

A relationship between views says how a row of one view finds rows of another. Every joined view that joins those two views uses it, so set it up once and check it on real rows before you save.

The relationship opens as a full page in the data model editor, from Relate another view… or from the pencil on a joined card.

1. Under View and Related view, check the two views.
2. Under Match on, select a field of each view that holds the same value, for example Sales Rep ID and Employee ID. For a key made of several fields, select Add another pair.
3. Under Keep, select which rows the joined view keeps (see the table below).
4. Under Each row finds, select At most one match when the related view is a lookup (one row per key), or Many matches when a row can match several. With At most one match, Test match warns when the key repeats in the related view, which would double-count totals.
5. Under Matching, select only the options the data needs (see the table below).
6. Optional: check the match with Test match, or let Reportworq find the key with Discover relationship (see Check a relationship on real rows).
7. Select Save and Close, then select Save Changes to save the data model.

The Keep options are the following.

The Matching options are the following. The first three are marked less efficient: each one makes the match fuzzy, so Reportworq reads every row of the related view instead of asking its source for only the rows that can match. The page says so under the options when that applies.

To delete a relationship, open it from a joined card's pencil and select Delete this relationship at the bottom of the page. The page says what else the delete changes: the related view (and anything joined to it) leaves this joined view, and any other joined view that uses the relationship stops working until the views are related again.

Line up values written differently#

Two systems often write the same thing differently: the ERP has a customer called Northern Brothers and the CRM calls it Northern Bros.. A value mapping lines the two up for one key pair, so those rows match. It belongs to the relationship, so every joined view that joins the two views follows it. Values that are not in the mapping match as they are.

1. On the relationship page, under the key pair, select Value mapping.
2. Add the mapping in any of these ways:
   • Select Add a line, then enter the value as the first view writes it and as the related view writes it.
   • Select Suggest from the data. Reportworq reads the first 10,000 rows of each view, finds the values that have no match, and lists the value each most likely means, with how alike the two are and how many rows it affects. Select Add on a suggestion, or Add all. Check each suggestion first: a close spelling is not always the same thing. Values that still have no match are listed so you can map them by hand.
   • Select Paste to replace the mapping with two columns copied from Excel or a text editor. Copy copies the mapping out the same way, so you can keep it in a workbook. If the browser does not allow Reportworq to use the clipboard, a text box opens for you to paste into or copy from by hand.
3. Select Save and Close.

The Value mapping heading shows how many values are mapped. Test match reports how many rows match only through the mapping.

Check a relationship on real rows#

The relationship page has two checks beside the form, under Check it on real rows. Both read each view the way a report would, with its fields, filters, and default variable values.

Test match#

Test match runs the match on the first 10,000 rows of each view, without building the joined rows, and reports:

• How many rows of the first view find a match, and the percentage.
• How many rows of the related view are matched by something.
• How many rows of the first view find more than one match and so repeat, and how many rows come out as a result. If each row should find one match, the key is not unique in the related view.
• How many rows match only through the value mapping.
• How many rows have an empty key and so never match.

A table then compares the four matching combinations side by side (exact, ignoring spaces, ignoring case, and both), marks the current one, and offers Use on the others. Advice under the table says when a fuzzy option changes nothing (exact matching finds the same rows and is faster), when another combination finds more matches, or when the current fuzzy option finds matches that exact matching misses.

Discover relationship#

Discover relationship finds the key for you. It reads the first 1,000 rows of each view and tries every pair of fields, one from each view. It skips yes/no fields and fields that share only a few repeated values, such as a region or a currency code, because matching on those repeats rows.

It shows the four best pairs, each with its match rate, whether the field is unique in the related view, and whether it needs a fuzzy option. Select Use this on a pair to fill in the key, the matching options, and Each row finds, and Reportworq then runs Test match on the larger sample.

If no pair of fields looks like a key, the two views may not share one, or the key may be in a field one of them does not output.

Read the Explain plan#

Explain on a joined view's canvas runs the joins, with the starting view capped at 100 rows, and shows How this runs: each numbered step, what it read, and what it matched. Each finding is graded with a mark, and the summary at the top counts the findings of each grade:

• Helping (a check mark): the step works in your favor, for example only the matching rows were read, or every row found a match.
• To check (a warning triangle): worth a look, though not necessarily something you did, for example rows that found no match, or empty keys.
• Hurting (a cross): the step costs you, for example rows repeated by a key that matched more than once (watch totals), or a fuzzy matching option that made Reportworq read every row and changed no match.

Use Explain after you build a joined view and whenever a report on it runs slowly.

Variables of a joined view#

A joined view lists the variables its input views use, under From the joined views on its Variables tab. Four rules apply:

• One name is one variable. When two inputs use a variable with the same name, the joined view has one variable, and one value drives every input that uses it. The line under the variable names the inputs: Used by view for one, One value for view, view for several.
• Only the variable's existence is inherited. Its default, Required, caption, help text, and choices belong to the joined view, set on its own Variables tab. Nothing is copied from the inputs, and inside a joined view an input never falls back to its own default.
• Use another view's settings when only one input uses the variable. Select Use input view's settings to apply that input's settings (its default, Required, and prompt) instead of your own. The row then reads, for example, Uses Sales Journal's settings: default 2024. The option is available only while exactly one input uses the variable, because the settings of several inputs could disagree.
• If a second input starts using the variable later, the variable has no default. This happens even when the second input is edited elsewhere in the model. The variable then has no default, and is required only if an input pastes it into its query text. The joined view's own default and prompt that were hidden behind Use input view's settings do not come back, so set them again on the Variables tab if you want them.
• A variable used in a query's text is always required. When any input pastes the variable into its query text (a {{Token}} in its SQL, MDX, or DAX), the joined view shows it as Required and cannot make it optional, because a blank value would break that query.

A joined view never asks for sample values. Its fields come from its inputs: each variable takes the joined view's own default, or else the sample or default of an input that uses it. A variable with none of these is reported by name when the fields are read.

For defaults, choices, and the rest of the Variables tab, see Data model variables.

Limits#

A joined view holds its inputs in the server's memory while it matches them, so four limits keep one mistaken join from exhausting the server. When a run goes past one, it stops with a message that names the input and the limit, so the author can filter the input or carry fewer fields.

The limits and their defaults are the following.

An administrator sets these for every data model on the server, in Settings > Configuration > Performance > Data Models. A value of 0 turns that limit off. See Server configuration.

When a joined view hits a limit:

• Too many rows from one input. Filter the input view, give it a variable, or join a smaller view.
• Past the memory budget. Remove fields you do not need from the input views, or filter the inputs.
• Too many rows returned. Check Each row finds and run Test match: a key that is not unique in the related view repeats rows.

Related topics#

• Data models
• Data model variables
• Build a view with the designer
• Table relationships