Joined reports

In this section, we will take a close look at joined reports.

As we’ve already mentioned, the joined reports format allows us to merge different report types into a single report so that we get a single report that contains data from different sources.

We can merge up to five different report types (standard or custom), called blocks, where each block can return up to 2,000 records. The only limitation is that they must share common fields (for example, Account Name).

With joined reports, we can, for instance, show all the opportunities for a given account, along with different details (for example, new opportunities, closed opportunities, and lost ones).

In this configuration, the first report type that’s added is the principal report type. This report type controls how common fields are named (sometimes, different report types on the same account fields hold different labels).

Joined reports are available on both Lightning Experience and Salesforce Classic. In Lightning Experience, create a new report, choose the first report type you wish to create (which becomes the principal), and then click on the REPORT quick menu at the top left of the Report Builder:

Creating a joined report on Lightning Experience

Now, click the Add Block button next to the report name to add new blocks (I’m adding the Opportunities report type twice, one for won and one for lost opportunities):

Adding blocks to the joined report

If we run the report at this step, we will get three reports, one next to the other, without any correlation.

Let’s add a grouping from the OUTLINE section by choosing the Account Name field (which represents the account lookup; you can add further groupings if needed, but only up to three):

Block groupings on a joined report

Now, the report correctly groups the results by the same account. However, you can see all the opportunities in both blocks since we haven’t set up a filter yet.

Let’s filter the opportunity reports to get won opportunities displayed first and lost opportunities last (filtering is configured in the FILTERS section):

Filtering blocks on joined reports

You can filter with a different stage value for the won opportunities and lost opportunities blocks.

After all this hard work, you will see the following output:

Joined report example completed

Now, we have a bird’s-eye view of which opportunities have been lost and which have been won for each account. As you can see, you can customize each block name, add a filter or column, as well as subtotals and even summary formulas.