Using an Advanced Sub Query to Join to Another View

If you’ve already built a simple report in Damstra BI, you’ll know that part of the procedure involves selecting a ‘View’ - that is, a module within your Damstra Safety system - that you’d like to pull data from.

In some cases, we might want to build a single report that includes data from more than one view. This is achieved using the Damstra BI Sub Query feature, which is explained in the below video. A further example and additional information can be found after the video. (For help with Basic Sub Queries, click here.)

In the following example I will create a report from the events view and join to the employees and additional person types view to add the investigator's employee number as this is not available from the events view.

1. Once the report has been created, ensure to add a unique ID field. In this example, the field is Investigator Internal ID.
This field can be hidden from the table output by clicking the drop-down arrow and selecting 'Hide Field'. 

2. Click the 'Add Sub Query' + in the top left-hand corner.

3. Use the following options and then click Ok. 
- Type: Append
- Style: Advanced
- Data Source: Damstra Safety Data
- View: Employees and Additional Person Types

4. Select the Unique ID field that you added in your original table and drag in the field to the 'Master Query Field'.
From the Employees and Additional Person Type fields, find the corresponding ID and drag this into the 'Sub Query Fields'. 
Name the Join. 
NB: If in your master query you have turned the site filter off, it will also need to be turned off for all subqueries. 

5. From the new Employees and Additional Person Types view you are able to add any fields that relate to the join. 
For this example, I want to add the Employee Number. 

6.  Done!
Publish your report.

If you wish to toggle between Sub Queries, click the tiles in the top left corner. 
The top icon will always be the master query.