Introduction
[top]
In this tutorial, we will walk through setting up an append sub query in order to compare two years worth of figures.
A good way to think about Appends is - use them if you need to add a column to a report that requires filters different to the rest of the report.
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 the append.
Master Query
The Master Query is going to 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 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.
Initialise
[top]
1. Click on the Create link and select Report to begin building your report.
2. You should now be on the Initialise Report page. Select the Drag and Drop Builder as the build tool.
3. Select Ski Team as the View.
Master Query
[top]
4. Drag and drop the 
Athelte Country dimension and 
Invoiced Amount metric into the Columns list.
5. Drag and drop the Year dimension into the Filters list.
Append Query
[top]
Create Append
6. Click on the + button in the Sub Query panel to the left of the Data step to add a Sub Query.
7. Set the Type to Append and Style to Basic.
8. Click Ok to set up the query.
Join Append
The first thing you will need to define, when setting up a sub query, is the join type, and join fields.
9. Ensure the join type is Left Outer Join.
10. 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.
11. Drag the Athlete Country field in to the *Sub Query Fields box in order to join it to the Master Query.
Filters
12. Add the Year field to the Sub Query Filters box. This filter will be used to restrict results of the sub query only.
Name & Save
13. Change the name of the Sub Query to Previous Year. This will later help identify the sub query, especially useful if you have multiple sub queries.
14. Save the Sub Query.
Add Column
15. Drag and drop the Invoiced Amount metric into the Columns list.
Edit Settings
16. If you need to make changes to your sub query setup at all, click the Edit Settings link.
Apply Filters
17. Once your sub query is ready, navigate back to the Master Query by clicking on it in the sub query panel on the left of the page.
18. Click on the Settings link in the Filters panel. This will allow you to configure logic and values for all filters.
Master Filter
19. Set the Master Query filter to be Year Equal to 2014 by clicking on the funnel icon.
20. Select 2014 and click submit
Sub Query Filter
21. Navigate to the sub query filters by clicking on the Previous Year tab.
22. Click on the x on the filter panel to close it. Be sure not to click on the X to close the report builder.
Formatting
[top]
23. Click on the first Sum Invoiced Amount field's drop down list and select Format.
24. Change the Display name to Invoiced 2014. It's important to do this to provide the user with some context to help differentiate between the queries.
25. While still in the Column Formatting menu, select the Sum Invoiced Amount field from the list on the left.
26. Set the name to Invoiced 2013 and close the menu.
Save
[top]
27. Click Report > Save to activate your report.
28. Set the Name of your report to Append Sub Query Tutorial.
29. Set the Description to This report was written using the Sub Query Tutorial.
30. Select Tutorial as the Category.
31. Select Training as the Sub Category.
32. Click Activate to finish.
33. You now have an active Append Sub Query report.
[top]