Tutorial - Append Sub Queries

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.

4. Now click the button to proceed.

5. Tick the checkbox next to Sub Queries to enable them. There should now be a Sub Query link at the top of the page.

6. Drag and drop the Athelte Country dimension and Invoiced Amount metric into the Column Fields area.

7. Drag and drop the Year dimension into the Filters area.

8. Click on the + Sub Query link. This will open the Sub Query Type menu.

9. Set the Type to Append, leaving the Style set to Basic for now.

10. Click Submit.

11. Now drag in the same fields as the master query. The difference between the two queries will be set up using filter values.

1. Click on the Refresh link in the Joins area (on the sub query tab) to load the child fields you just added.

2. Set both the Master and Child fields to Athlete Country, select a join operand, and click the Add Join button to save.

3. You should now have a join that looks like the one pictured.

4. Click on the at the top of the page to progress to the next step (filters).

1. To set the Master Query Filter click on the Filter Prompt image. This will load a list of filter values.

2. In the Filter Values window select 2010 and click on the Submit link to load the filter.

3. You should now have the Master Query Filter set, as shown here.

4. To set the Sub Query Filter, follow the same steps as above, this time selecting 2009 as the value.

5. You can now click on the at the top of the page to view your report.

1. You should now have a report that looks like the one pictured here. To make it easier to read you are going add some formatting to it.

2. Click on the Columns link at the top of the page.

3. Select the Sum Invoiced Amount Column from the dropdown.

4. Click on the Display Menu.

5. Change the Display Name to Invoiced 2010.

6. Click on Close.

7. Repeat these steps for the Sub Query: Sum Invoiced Amount column, this time setting the Display Name to Invoiced 2009.

8. You should now have a report that looks like the one pictured here. The columns are clearly labelled to make it easier to read the data.

9. You are now finished with this report. Click on the Report Menu and select Save.

10. Set the Name of your report to Append Sub Query Tutorial.

11. Set the Description to This report was written using the Sub Query Tutorial.

12. Select Tutorial as the Category.

13. Select Advanced Reporting as the Sub Category.

14. Click Save to finish.