This tutorial will give a quick overview of how you can merge multiple reports and data sources into a single report.
Note: This functionality is NOT available on the Essentials Plan.
Sometimes it's simply not possible to get the report you need from a single data source. For example you might have a weekly sales by customer report, but you also want to add some additional information for each customer, such as their average lifetime order size.
The problem is that the sales report should only show the sales for the last few months, but the additional information needs to aggregate the sales for all time. It's simply logically impossible to run a single report with 2 date filters.
The solution is to create 3 reports. We create the main sales report, and a second report which has the additional information per customer. We can then combine these two into a final third report which merges (or joins) the two together.
Let's then go ahead and create a report for exactly this scenario.
Step 1 - Create source reports
We'll create 2 base reports to be merged from the Demo data source.
The first report aggregates the total sales per customer per day (there is another tutorial on how to create aggregation reports):
The second report lists each customer with their average sale amount over all time along with some extra columns such as city and email:
It is VERY important that the columns you want to join on has to have the exact same name in both reports. We want to join on the common Customer Name, so that column is in both reports and is spelled exactly the same.
Step 2 - Turn them into data sources
For each of the 2 source reports, enable the "Is Data Source" option in the Report section of the designer:
Save both reports.
You should now see both of these reports in your report list, tagged as data sources.
Step 3 - Create new Merged report
From your report list create a new report by clicking on "Design a New Report" and select the "Merge Reports" data source:
Step 4 - Select data sources
In the "Report" section of the designer you'll see options to select which sources to merge. Let's select both our base sources we created before:
You will notice that the merge type defaults to "Left Join". This will generally be what you want, but you can learn more in our article about Join Types.
Step 5 - Add columns to report
At this stage we can now edit and create this report just like any other one. Let's go to the Layout section and add all the columns:
And in the preview we can now see that it has merged in the correct details for the corresponding "Customer Name" fields:
