Append Sub Query

Overview

In summary, Appends are used when you need to add a column to a report that requires filters different to the rest of the report. In this tutorial, we will walk through setting up an append sub query to compare two years' worth of figures.

In this scenario our main report will look at invoice figures by country in 2014. The column we want to add is the invoiced figures in 2013. The filters Year = 2014 and Year = 2013 conflict, so the additional column will have to be added through an append.

Master query

For this example, the master query will include:

1. Athlete Country
2. Sum Invoiced Amount
3. Filter: Year = 2014

This will mean that we have Country as a common field for 2014 and 2013 so this will form our join when we add the Append.

Note: sometimes you will have a case where you don't wish to filter the Country list by 2014, this may be because some countries have no results for 2014 yet. In this case you would have Country in the Master Query, and an Append Query for each Invoiced Amount.

Append sub query

The Append Query will include:

1. Join: Athlete Country
2. Sum Invoiced Amount
3. Filter: Year = 2013

This will be joined to the Master Query using the Athlete Country field. The query will be filtered by Year = 2013, and will include a copy of the Sum Invoiced Amount field to be filtered.

Creating your report

1. Create a new Report by clicking the Create button
   
2. Click on the Ski Team view in order to use the fields defined within it for creating the report
   
   The Report Builder will be displayed
   
   

Creating the master query

1. Using your mouse, drag the Athlete Country dimension and Invoiced Amount metric into the Columns field
2. Using your mouse, drag the Year dimension into the Filters list
   

Append query

Creating an Append sub query

1. Click on the button in the Sub Query panel to the left of the Data step to add a Sub Query
2. Set the Type to Append and the Style to Basic, then click the OK button to set up the query
   

Joining an Append sub query

When setting up a sub query, the join type and join fields must be defined.

1. Make sure the join type is set to Left Outer Join
2. Select the Athlete Country field in the Master Query Fields drop down list
   This is the field you wish to join the sub query to
3. Using your mouse, drag the Athlete Country field in to the *Sub Query Fields box to join it to the Master Query
   

Adding a filter

1. Using our mouse, drag the Year field to the Sub Query Filters box
   This filter will be used to restrict the results of the sub query only
   

Naming & saving

1. Change the name of the sub query to Previous Year
   This helps to identify the sub query, especially if there are multiple sub queries
   

Adding a column

1. Using your mouse, drag and drop the Invoiced Amount metric into the Columns field
   

Editing settings

1. Click the Edit Settings link to make any changes to your sub query
   
   
   Your sub query is now ready

Applying filters to your queries

1. Navigate to the Master Query by clicking on it in the sub query panel on the left of the page
   
   
   
2. Click on the Settings link in the Filters panel
   This will allow you to configure logic and values for all filters
   

Configuring the master filter

1. Set the Master Query filter to be Year Equal to 2014 by clicking on the funnel icon
   
   
   
2. Select 2014 and click the Submit button
   
   

Configuring the sub query filter

1. Click the Previous Year tab to navigate to the sub query filters
   
   
   
2. Click on the X to close the filter panel — ensure you don't click the X on the Report Builder
   

Formatting your report

1. Navigate to the Master Query by clicking on it in the sub query panel on the left of the page
   
   
   
2. Click on the dropdown of the Sum Invoiced Amount and select Format
   
   
   
3. Click on the Display field to change the title of the column to Invoiced 2014 
   This helps you differentiate between queries
   
   
   
4. While still in the Column Formatting menu, select the Sum Invoiced Amount field from the list on the left
5. Click on the Display field to change the title of the column to Invoiced 2013
   
   
   
6. Click on the X to close the Column Formatting menu

Saving your report

1. Click Report and then click Save to activate your report
   
   
   
2. Click on the name field and rename your report to Append Sub Query Tutorial
3. Enter a description for this report
4. Click the category dropdown list on the left side and select Tutorial 
5. Click the sub category dropdown list on the right-hand side and select Training
6. Click Save to publish your report
   
   
   Your Append Sub Query Report is now published