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:
- Athlete Country
- Sum Invoiced Amount
- 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:
- Join: Athlete Country
- Sum Invoiced Amount
- 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
- Create a new Report by clicking the Create button
- 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
- Using your mouse, drag the Athlete Country dimension and Invoiced Amount metric into the Columns field
- Using your mouse, drag the Year dimension into the Filters list
Append query
Creating an Append sub query
- Click on the button in the Sub Query panel to the left of the Data step to add a Sub Query
- 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.
- Make sure the join type is set to Left Outer Join
- 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 - 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
- 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
- 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
- Using your mouse, drag and drop the Invoiced Amount metric into the Columns field
Editing settings
- Click the Edit Settings link to make any changes to your sub query
Your sub query is now ready
Applying filters to your queries
- Navigate to the Master Query by clicking on it in the sub query panel on the left of the page
- 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
- Set the Master Query filter to be Year Equal to 2014 by clicking on the funnel icon
- Select 2014 and click the Submit button
Configuring the sub query filter
- Click the Previous Year tab to navigate to the sub query filters
- Click on the X to close the filter panel — ensure you don't click the X on the Report Builder
Formatting your report
- Navigate to the Master Query by clicking on it in the sub query panel on the left of the page
- Click on the dropdown of the Sum Invoiced Amount and select Format
- Click on the Display field to change the title of the column to Invoiced 2014
This helps you differentiate between queries
- While still in the Column Formatting menu, select the Sum Invoiced Amount field from the list on the left
- Click on the Display field to change the title of the column to Invoiced 2013
- Click on the X to close the Column Formatting menu
Saving your report
- Click Report and then click Save to activate your report
- Click on the name field and rename your report to Append Sub Query Tutorial
- Enter a description for this report
- Click the category dropdown list on the left side and select Tutorial
- Click the sub category dropdown list on the right-hand side and select Training
- Click Save to publish your report
Your Append Sub Query Report is now published