Create Salesforce Joined Reports (in 10 Steps)
A Joined Report in Salesforce combines two reports that have different report types, within a single view. Salesforce Joined Reports appear like one single report so that you can get a more holistic data view.
In the Lightning Report Builder, you add report blocks, and then define the filters for each block. You can use both standard and/or custom report types as report blocks.
When Should You Use a Joined Report?
Remember that a Joined Report is designed to show two unrelated reports in one view. If you think about the Salesforce data model, there are many objects that don’t naturally ‘gel’ together (not even custom report types can solve it).
Take Opportunities and Cases as an example. There’s no way you would be able to see both an Account’s related Opportunities and related Cases in one view without Joined Reports.
In this Salesforce Joined Reports tutorial, we will be creating a report that shows:
- Block 1: Account report
- Block 2: Cases related to the Account
- Block 3: Opportunities related to the Account
This would be used by sales reps who want to know which Accounts have Cases (especially the unresolved ones!) so that they don’t annoy already frustrated customers with conversations about renewals.
Note: This example is only possible because there is a common relationship – you can’t just jam together completely unrelated objects and hope a Joined Report as the outcome!
- Cases are related to the Account
- Opportunities are related to the Account
Create a Joined Report
Let’s first create a Salesforce report, then we’ll transform it into a Joined Report format type.
1. Go the Reports tab, click New Report.
2. Select the Accounts Report type.
3. Change the Report to a Joined Report format type:
4. Add or amend the filters for the Account report. This is your baseline and will determine what data the other report blocks will pull in.
Add a Report Block
5. Select the Cases report type, click Add Block.
6. Add or amend the filters for the Cases block. Perhaps “Status” = “Open” is a good option, given our use case.
7. Add another report block for Opportunities. Again, add or amend the filters to perhaps include only the open Opportunities (“Opportunity Status” = “Open”).
Add a Grouping
Groups in Salesforce reports group rows together – which can make Joined Reports much easier for users to understand.
8. You can group rows by “Account Name”, for example, which then aligns all of the Account’s Cases and Opportunities, so anyone scanning the report can understand the data faster:
9. Run the report to see where Cases and Opportunities overlap (by Account).
Add a Report Chart
Report charts help users to understand reports even more. These visualizations can speak a thousand words! There are some limitations, which I will circle back to shortly.
Chart example: View the number of Cases by Account.
10. Click Add Chart, then select the chart type that will best represent your data. Select the appropriate Y-Axis value. You will notice these include the report block name.
Salesforce Joined Reports: What to Keep In Mind
Keep these things in mind when designing Joined Reports in Lightning:
- Report performance: The more blocks your report contains, the longer it will take to load. If you have more than two blocks, use report filters to keep the report view only to the data required (or have users frustrated with report load times!).
- Export: Joined Reports are designed to be visually useful inside Salesforce, so they don’t export into a workable format.
- Charts: You can only add one chart to Joined Reports. So, if you have more than two blocks, you have to choose one common factor (or restrict the chart to two objects). In the tutorial, the common relationship was Account name, and the number of Cases was the factor – which is why we could not include Opportunity data in the chart.
Take Joined Reports Further
Now you see how you can view different types of information, side by side. You could use Joined Reports to solve some challenging reporting requirements you receive from your team; think about the opportunities that open up if you create custom report types, then use those custom report types in a Joined Report? You’re opening many data relationships that didn’t previously exist.
How have you used Joined Reports? We’d love to hear your use cases in the comments section below.
Thanks! This was just what I was looking for!
If I have two time series data (my average sales for my accounts per month vs average sale for all accounts per month, can I chart them simultaneously on the same chart?
It was a data issue. Never mind.
This was the perfect intro article for someone trying to use a joint report to solve a problem. Thanks!
Thank you, appreciate that!
This is a great article .. thank you for making it so clear and using screenshots. One issue I found was that my org didn’t have the dropdown arrow next to Reports. I found that my profile (Sys Admin) didn’t have this box selected: Report Builder (Lightning Experience). Once that box was checked, the dropdown arrow appeared next to Reports .. just like your screenshot shows 😀
Thank you Anet, I am sure others will find that snippet of advice useful!
This is really good info. I am stuck at one point. Trying to build a report that shows open and closed cases by month. While I am able to build the two blocks 1. Filter based on Opened Date -Range. 2. Filter based on Closed Date – Range. Which field do I use to group by so that I can plot for. e.g. In Jan – We had x cases opened and y cases closed.? Any help here will be appreciated.
If I group by opened date only then it will only show the closed count of cases that were opened in that month. What I want to see is total cases opened in Jan vs total cases closed in Jan irrespective of when they were opened.
i am not able to see the opportunity data in joint report why
Can you join two or more already created reports or do you have to recreate from the report type to make the blocks work?
Thanks Anet! My sys admin profile was not checked either
can’t we not ‘subscribe’ a joined report in Salesforce CPQ to be delivered to our outlook everyday?
How do I enable joined reports/ cross filters in the first place?? They are greyed out and I cannot select the option to do so in the first place.
Hello Samantha! Would you mind clarifying please where are they greyed out? And also, it would be great if you can share the requirement you’re trying to use joined reports for as I would very much like to replicate the issue you’re facing if possible.
We created a joined report with a parent block and 2 additional related blocks.
The 2 related blocks are Applications and Registrations. When an applicant is accepted a registration is created.
There are Applications without Registrations and Registrations without Applications (staff need not apply, only register).
Is there a way to align a joined report so that there is a connection between the 2nd and 3rd block.
Program X Applicant Registrant
The applicant and registrant records are aligned and matched where both records exist.
Thanks in advance!
Thank you for reading this post! The first thing I would try is to group across blocks by one of the available common denominators – there should be a connection not only between the 2nd and 3rd block, but also the first one in this case.
Also, would you mind sharing more details about the first block and the report types used? If your focus is only on the 2nd and 3rd blocks to be aligned, maybe it’s worth attempting a joined report only with the two of them (my guess based on your message above is that the first block is the Program, so potentially the Program Name is something available on both Applicants and Registrants since both these objects have a relationship to the Program). The problem with this approach is that while it would work for the table you shared above with blanks on column B only, it wouldn’t work if we can have blanks on both sides as you explained it may happen but isn’t accounted for in the example table (hence the need for block 1).
Based on the fact that you mentioned that there are Applications without Registrations and Registrations without Applications, couldn’t your your report also have blanks on column A and not only column B? Also, do both 2nd and 3rd block have a relationship to the first one?
Program X Applicant Registrant
I understand that it is not possible to subscribe to nor export Joined Reports in Salesforce but, is there a workaround for this?