Sub Query Basics
Sub Queries allow you to build more complex reports, specifically when you find you are unable to include all of the relevant fields within the master query. This may be because:
- you need to include a field that's only available in a different view to the view you're using
- you need to include data with different filters applied to those used in your master query
- you need to include additional results that can't be shown in the master query, or require different fields
- you feel like being extra fancy with your report writing (not really).
Each sub query is a bit like building an additional report that is related to the master query/report.
| Info | ||
|---|---|---|
| ||
The master query is the main part of the report - the bit you build first before adding any sub queries (if any at all). |
Adding a sub query
- Click on the plus sign in the top left of your report builder screen
2. You'll now be presented with the following Sub Query options.
Sub Query Type
| Append: Use this to add more columns to your report (or rows for a row-based report). Union: Use this to add more results to your report. Minus: Use this to eliminate certain rows from your report - i.e. if the minus query returns it, it won't show in the report's results. Intersect: Use this to return results that only appear in both your master and your sub query. |
You are likely to only have to use the append type occasionally and the union type rarely, and when you find you're unable to get the required results from your master query alone. The minus and intersect would only be used for very specific circumstances.
Sub Query Style
| Basic: Your sub query will use the same view as your master query. |
| Advanced: You need to use a different view for your sub query. For example, you may be using view 01 (Job Requisition and Application Data) but need to add data that is only available in view 02 (Process Data) or view 04 (Assessment History) You will likely use the same Data Source, but you can select from the views using that source. |
Append Configuration
This is the most common type of sub query - you'll use it to add on additional columns to your report. When you select an Append sub query you'll need to complete the following configuration.
Joins
- Specify the type of join - which results to you want to include?
|
|
2. Specify the fields used to join queries - how will results from one query be matched to the master query?
a) Choose the master query field(s) that will be matched to the same data in the sub query. Common fields are JobID when you're reporting on a list of jobs in the master query, or ApplicationID to match application details.
b) Drag the same field (or the field with matching answers) from your sub query folders to the sub query fields box
c) Add more field joins (if required) by clicking the plus icon (remove extra joins using the minus).
Filters
3. Drag any fields that need to be used as filters in the sub query to this box.
Name and Description
4. Edit the name and description to make it clear what this sub query is for.
Click Save to confirm your settings (or delete to remove the sub query).
You will now return to the report building screen to add any desired fields.
- Fields from the query you're working in will have a blue column header, whereas fields from other queries will be greyed out or faded.
- Switch between the Master and any Sub-Queries by toggling between them on the far left hand side
| Tip | ||
|---|---|---|
| ||
You might need to use an append sub query when:
...and there will be many more. Essentially, an append sub query will allow you to get data from a different view, get data from a different field in the same column, or with additional or different filters applied to the existing view or data. |
Union Configuration
The key difference with a Union sub query to an append is instead of adding more fields as columns (in a column report), you add more rows (or results) to the report underneath the existing columns. When you select a Union sub query you'll need to complete the following configuration.
Sub Query Fields
- Drag the fields from your sub-query that will be used in the same columns as the master query's fields. They may be the same field, or they may be a different field.
For example, you may return jobs in your master query that uses one field, but the results in the union sub query are for a different type of job that uses a different field:
The field type (dynamic vs metric) needs to be the same, Any calculations or aggregations applied to master query fields will also be applied to the matching sub query field.
Filters
2. Drag any fields that need to be used as filters in the sub query to this box.
Name and Description
3. Edit the name and description to make it clear what this sub query is for.
Click Save to confirm your settings (or delete to remove the sub query) and update the settings for any filters that you added above.
| Tip | ||
|---|---|---|
| ||
You might need to use a union sub query when:
...and there may be others. Essentially, you'll only need a union sub query when you can't get all of the required results from your master query and need to get additional results from a different view or using different filters. |
Minus
and Intersect Configurations
The likelihood you'll ever need to use a
Minus or Intersect sub query are very low due to the way your views are configured to contain different groupings of data (rather than different sets of results of the same types of data). But if you ever do, here's what you'll need to configure.
Sub Query Join
1. Choose the field from the master query that also exists in the sub query (e.g. JobID)
Filters
2. Drag any fields that need to be used as filters in the sub query to this box.
Any results based on these filters will then be matched to results in Master Query via the field selected above, and for:
- Minus sub queries, removed from the report's results.
- Intersect sub queries, only results that exist in both queries will be returned.
Name and Description
3. Edit the name and description to make it clear what this sub query is for.
Click Save to confirm your settings (or delete to remove the sub query) and update the settings for any filters that you added above.