Joined Reports: Don’t Be Intimidated [Part 1]

The Spring ’12 release of Salesforce bore tremendous benefit in reporting functionality with the launch of Enterprise Analytics. Available to Developer, Enterprise and Unlimited Edition customers at no additional charge, the Enterprise Analytics module provides enhanced features within the Report Builder permitting users to take Salesforce reporting to a whole new level. The 3 primary features included in Enterprise Analytics are:

• Joined Reports
• Buckets
• Cross Filters

Even though all 3 are extremely useful and it is more than likely I will address all of these and more in the near term, this post focuses on the basics of Joined Reports.

What is a Joined Report?

A Joined Report gives you the ability to build one report with multiple sections or “Blocks” based on differing filter criteria and/or source data objects. For example,

Quarterly Opportunity Wins Analysis:

Opportunity Wins $$ and Win Rate for QTD for each Opportunity Owner

Traditional reporting techniques made this report a challenge because Custom formula fields configured on the Opportunity were required to show a tally of Won Opportunity $$ and Win Rate metrics in the same report. Here’s why …

• The Won Opportunity $$ can be achieved via a filter on Status = Closed Won
• The Win Rate requires a filter on all Closed Opportunities (Won or Lost) for the denominator of the Win Rate formula
• These two filter requirements conflict with each other
• There are workarounds to this by adding formula fields to the Opportunity object that can then be used in report metrics, but wouldn’t we rather build our report without asking our System Admin to create formula fields?

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Leveraging the Joined Report type allows us to create a separate “Block” for each piece of this report:

• Block 1 = Opportunity Wins $$ QTD
• Block 2 = Win Rate QTD

In this example, both blocks are based upon the same report type (Opportunity) but they have different filter criteria. Follow the instructions in the sections below and you can create this sample report in your own environment. In an upcoming post, we will explore more advanced types of joined reports that leverage different Report Types for each block… so STAY TUNED!!

Getting Started:

1. Click the New Report button from the Reports tab
2. Select the Report Type you want to use for Block 1 of your report. In our case, this is just “Opportunity”. Then click the “Create” button in the bottom right corner of the screen.
3. Change the Type to Joined Report (see final considerations below for tips on how to enable this feature)

Building Block 1 (Opportunity Wins $$ QTD):

The first Block is always automatically added for you, but you need to make some modifications:

1. Rename the block “Opportunity Wins $$ QTD”
2. Remove unwanted fields by dragging and dropping them into the Field Explorer pane on the left
3. Add the fields you want to display for Block 1 by dragging and dropping them into their desired location in the report preview pane
4. Add the necessary filters for Block 1. In this case, we need Opportunity Status = Closed Won and Close Date = Current CQ. We also need to change the “View” filter to “My Team’s Opportunities”
5. Apply a Sum on the Opportunity Amount to get the total for the Wins.
6. Save your Report!!!

When you’re done, Block 1 would look something like this:

Building Block 2 (Win Rate QTD):

1. Drag and Drop an Opportunity field you will use in Block 2 (doesn’t matter which one) into the whitespace of the preview pane to the right of Block 2. Perhaps the “Won” checkbox field?
2. Rename the Block “Win Rate QTD”
3. Show/hide the fields you want to display for Block 2 by dragging and dropping. For this one you might only want the Won checkbox and the Closed checkbox, or the Stage.
4. Add the necessary filters for Block 2. In this case, we need Opportunity Status = Closed and Close Date = Current Calendar Quarter. We also need to change the “View” filter to “My Team’s Opportunities”
5. Before we can add the Win Rate formula field to the Report, we need to setup a Grouping that is common across all Blocks. Drag the Opportunity Owner field from the Field Explorer’s Common Fields section to the Grouping bar.
6. We need to create a Report Summary Formula for the Win Rate. You must have a grouping in your report in order to do this! (see step 5.)
   1. Drag “Add Formula” option from the top of the Field Explorer to an empty slot in the Win Rate QTD block
   2. Win Rate = Won / Closed (Note: Won and Closed are a Boolean fields included in the standard Opportunity configuration. Whether or not a Stage is considered to be Closed or Won is based on your Stage configuration settings.
7. Save your Report!!

The report would now look something like this:

And when you click the Run Report button, here’s the final product!!

Final Considerations for Joined Reports:

• In order to have access to setup a Joined Report:
  • The Joined Report type must be enabled by your System Administrator
  • Your Profile must have the Report Builder permission
• You can add up to 5 blocks to a Joined Report
• At this time you cannot Export the data or launch the report in a Printable View.
• Joined reports cannot be the source report of an Analytic Snapshot or a Dashboard Component

In my next post, I will focus on Joined Reports that require different Report Types as well as unravel some advanced concepts and “gotchas”. Until then, good luck trying out these basics!