Creating the Union Sub Query
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. Drag and drop the Athlete Country dimension and Invoiced Amount metric into the Column Fields area.
6. Tick the checkbox next to Sub Queries to enable them. There should now be a Sub Query link at the top of the page.
7. Click on the Sub Query link. This will open the Sub Query Type menu.
8. Set the Type to Union.
9. Click Submit.
10. You should now have a Master Query Tab and a Sub Query Tab. Click on the Master Query tab to setup the query.
Creating a Field Label
11. Click on the Add Calculated Fields Link at the bottom of the page to begin creating a calculated field.
12. Type Invoiced Amount in the textbox as shown.
13. Click on the button to add the text to your formula.
14. ‘Invoiced Amount’ should now appear in the large text area. This will mean that the calculated field’s value will be constant, used as a label in the report.
15. Set the Calculated Field Name to Label.
16. Click the Save link to finish.
17. Drag and drop the Label Calculated Field into the Column Fields area.
18. Click on the Union tab.
19. Drag and drop the Athlete Country dimension and Profit Margin metric into the Child Fields area as shown. It is important to make sure the order matches up to the Master Fields so that the union works.
20. Click on the Add Calculated Field link as before. This time type Profit Margin and click to add the text.
21. Set the Calculated Field Name to Label..
22. Click the Save link to finish.
23. Drag and drop the Label Calculated Field into the Child Fields area as shown.
24. You can now click on the at the top of the page to progress to the next step.
1. You should now have a report that looks like the one pictured here.
2. To start, click on the Label column header and drag it so that it is moved between the Athlete County and Sum Invoiced Amount columns.
3. Your report should now appear as pictured. Reordering the columns should make the information easier to read. Formatting will also help.
4. Click on the Columns link at the top of the page.
5. Select the Sum Invoiced Amount Column from the dropdown.
6. Click on the Display Menu.
7. Change the Display Name to Amount.
8. Click on Close.
9. You should now have a report that looks like the one pictured here.
10. You are now finished with this report. Click on the Save Menu.
11. Set the Name of your report to Union Sub Query Tutorial.
12. Set the Description to This report was written using the Sub Query Tutorial.
13. Select Tutorial as the Category.
14. Select Advanced Reporting as the Sub Category.
15. Click Activate to finish.
See Formatting for more information.