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.
Master Query
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
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
When will I use this?
You might need to use an append sub query when:
- you have a list of jobs (master query) and you need to count different groups of candidates (sub query or queries)
- you have a list of candidates for one job (master query) and need to show all the other jobs they have applied for (sub query)
- you have a count of jobs by vacancy type, division or another field (master query) and want to show an additional breakdown of these jobs (sub query or queries)
...and there will be many more. Essentially, an append sub query will allow you to get data from a different view, or with additional or different filters applied to the existing view or data.
