Introduction
In this tutorial, we will walk through setting up a Union sub query. A good way to think about Unions is - use them if you need to combine multiple fields into the one column, either from the same or different views or sources.
...
- Camp Region
- Calculated Field: "Cost" text label
- Calculated Field: Sum Cost * -1 (to convert the Cost figures to a negative value)
Initialise
| Section |
|---|
| Column |
|---|
| 1. Click on the Create link and select Report to begin building your report. |
| Column |
|---|
|  Image Modified
|
|
| Section |
|---|
| Column |
|---|
| 2. You should now be on the Initialise Report page. Select the Drag and Drop Builder as the build tool. |
| Column |
|---|
|  Image Modified
|
|
| Section |
|---|
| Column |
|---|
| 3. Select Ski Team as the View. |
| Column |
|---|
|  Image Modified
|
|
Master Query
| Section |
|---|
| Column |
|---|
| 4. Drag and drop the  Image Modified Camp Region dimension and  Image Modified Invoiced Amount metric into the Columns list. |
| Column |
|---|
|  Image Modified
|
|
Label Field
To complete the master query, a label field is required. This will be used to identify the rows from the master query as "Invoiced" rows. There will be a similar calculation in the union query to identify rows as "Cost".
...
| Section |
|---|
| Column |
|---|
| 6. Set the Calculated Field Name to Label
Note: the name for this field can be anything, it will not change the way the union works if it's called something else. 7. Type Invoiced into the text box under the main calculation panel, as shown here, and click + Add to add it to the calculation. |
| Column |
|---|
| 
|
|
...
| Section |
|---|
| Column |
|---|
| 15. Set the Calculated Field Name to Label
Note: the name for this field can be anything, it will not change the way the union works if it's called something else. 16. Type Cost into the text box under the main calculation panel, as shown here, and click + Add to add it to the calculation. 17. Click the Validate button to ensure your calculation is built correctly. 18. Click the Save button to complete your calculation. |
| Column |
|---|
| 
|
|
...
| Section |
|---|
| Column |
|---|
| 19. Click on the + to create a calculated field, in the bottom of the View Fields List panel. 20. Set the Calculated Field Name to Cost
Note: the name for this field can be anything, it will not change the way the union works if it's called something else. 21. Click on the ∑ button. 22. Select the Camp Cost field from the Select Field drop down, and click + Add. 23. Close the bracket by clicking ). 24. Click the * button to multiply the field by a value. 25. Type -1 into the text entry field, and click + Add to put in your calculation. 26. Click the Validate button to ensure your calculation is built correctly. 27. Click the Save button to complete your calculation. |
| Column |
|---|
| 
|
|
...
| Section |
|---|
| Column |
|---|
| 32. If you need to make changes to your sub query setup at all, click the Edit Settings link. |
| Column |
|---|
| 
|
|
Formatting
| Section |
|---|
| Column |
|---|
| 33. Click on the Sun Invoiced Amount field's drop down list and apply a Sum Total |
| Column |
|---|
|  Image Modified
|
|
| Section |
|---|
| Column |
|---|
| 34. Click on the Camp Region field's drop down list and select Format. You could also select the Column Formatting menu from the main navigation bar. 35. Enable the Suppress Duplicates option. This will make the final table easier to read, with the inclusion of sub totals. |
| Column |
|---|
|  Image Modified
|
|
| Section |
|---|
| Column |
|---|
| 36. While still on the Camp Region field, open the Summary settings and enable the Sub Total option. |
| Column |
|---|
|  Image Modified
|
|
| Section |
|---|
| Column |
|---|
| 37. While still in the Column Formatting menu, select the Sum Invoiced Amount field from the list on the left. 36. Set the name to Value. |
| Column |
|---|
|  Image Modified
|
|
| Section |
|---|
| Column |
|---|
| 38. Close the menu. |
| Column |
|---|
|  Image Modified
|
|
Save
| Section |
|---|
| Column |
|---|
| 39. Click Report > Save to activate your report. |
| Column |
|---|
|  Image Modified
|
|
| Section |
|---|
| Column |
|---|
| 40. Set the Name of your report to Union Sub Query Tutorial. 41. Set the Description to This report was written using the Sub Query Tutorial. 42. Select Tutorial as the Category. 43. Select Training as the Sub Category. 44. Click Activate to finish. |
| Column |
|---|
|  Image Modified
|
|
| Section |
|---|
| Column |
|---|
| 45. You now have an active Union Sub Query report. |
| Column |
|---|
|  Image Modified
|
|