Index queries can now show values from a second, related query as extra columns. For example, a list of participants can show how many points credits each person has received, and the total points, without building a custom report.

You choose the related query and how the two relate, and its numbers appear as columns in the results table and the CSV export.

How it works

Every row in your query gets one value from the related query. The related query supplies those values through an aggregation that groups its records by the field linking them to your rows. For example, the Points received query below groups points credits by the person they were issued to. Each participant row then shows the numbers from their own group.

Because the related query is an ordinary index query, its own filters decide which records it counts. To count only unexpired module statuses, for example, put that filter on the related query.

Before you start: give the related query an aggregation

Open the query you want to pull values from and click Aggregations. You need an aggregation with:

• Aggregation type set to terms
• Field set to the field that links to the records in your main query, for example Issued To Profile Id to link points to profiles

The aggregation gives you a Count column. Each sub aggregation (sum, average or count of another field) becomes an extra column. Here, Total points sums the points in each group.

1 Add a relation

Open the query that should show the extra columns. Above the results table, click Columns, then Add more fields. In the Related queries panel, click Add a relation.

2 Choose the query and how they relate

In Query to join, pick the related query. Only queries whose records are related to this query's records are listed.

Under How they relate, choose the link to use. Some records can relate in more than one way. Points, for example, have the person who issued them and the person they were issued to. When there is only one way, it is selected for you. Click OK.

3 Add the columns

The relation now appears in the Related queries panel, showing how many of its columns you have added. Its columns are listed at the top of the field list, tagged with the name of the related query. Click the green + next to each one you want.

4 Rename and format

Click Back to selected fields. Joined columns are listed after your other columns and marked joined. Use the pencil to give a column a custom title or number format, just like any other column. Use the × to remove it.

The result

Joined columns appear at the right of the results table, shaded and labelled with the related query's name. They are also included in the CSV export.

Good to know

• Date range: if the related query filters on the reporting date range, joined values only count records inside the range you are viewing. A 0 means there were no matching related records, either at all or in that range.
• Sorting: joined columns cannot be sorted, and they always appear after the query's own columns.
• Different filters side by side: filters belong to a query, so to show, say, active and expired counts next to each other, create two related queries with different filters and add both.
• No columns offered: if the related query has no aggregation grouped on the linking field, the relation is saved but offers no columns. The panel says so and links to that query so you can add one.
• One level only: a query that has related queries of its own cannot be used as a related query.
• Access: people only see joined values if they have access to the related query.
• Editing and removing: in the Related queries panel, use the pencil to change a relation, or the × to remove it together with all its columns.