Overview
A Union All Report stacks rows from at least two reports into a single report. For example, you might have two Shopify Stores (USA and AUS) and you want all of your sales in a single report. You could use SQL to accomplish this, but we've made it easier with the Union All Report.
Add a Union All Report
To add a Union All Report to the Metric or Dataset Builder:
1. Once you have more than one report in the builder, hit the "+Add Report" button on the far right
2. Then select the "Union All" option at the bottom of the modal that pops up
Please note you must have at least two reports in the builder to add a Union All Report.
Upon adding a Union All Report to the Dataset or Metric Builder, you will notice the report will be added as the n+1 report in the builder (where n is the number of reports that exist in the builder prior to adding in the Union All Report).
How to use the Union All Report
To begin stacking your tables together, add the first report. It is important to note that the first report selected defines the structure of the outputted table. The structure of the table includes the following:
- Column name
- Column order
- Column data type
Upon selecting the first report, you will notice all of the columns and data types populate in the UI.
Next, select the second report you wish to stack. Upon selecting the second report, the columns are automatically added to the UI where column names and data types match, regardless of the order of the columns in each report.
If you have columns in each report that should be stacked but are named differently (for example "deal amount" and "sales"), simply select the column in the dropdown to add it into the table.
To ensure good data practices, Grow's Union All operator requires that stacked columns must be the same data type. You will not be able to add a column from the dropdown that doesn't match the data type of the column in the same position of first report selected. If you wish to stack columns together but their data types differ, you will need to change the data type in Data Settings of the source report.
Upon running the Union All operator successfully, you will notice a source_report column added as the first column in the resulting Data Table. This column is to help you identify which report each row came from. You can also use this column in subsequent transforms, for example, you could use the Tag Transform to identify "report1" as "USA Store" and "report2" as the "AUS Store."