In my last posting, I outlined some basics regarding the use and setup of basic Joined Reports. In this article, I will focus on some of their more complex capabilities and considerations. Specifically, we will review the following two techniques:
- Creation of a Sales Rep Scorecard Report that combines Opportunity and Activity data into one report
- How to add a Cross Block Formula field to your report which allows you to build a formula field using a combination of fields across report blocks
Let’s get started with our Sales Rep Scorecard Report.
The report is useful in comparing key metric performance across various members of the sales team. In the first 2 blocks, we present some Opportunity metrics. Separate blocks are needed because the filter criteria for each block are different: CQ Wins looks at wins only; YTD Win Rate looks at all Closed Opportunities, won or lost. In the last block we tally Activities in order to look at MTD Completed Meetings.
The key to building this report successfully is to select the correct Report Type for each of the Report Blocks. Specifically, the grouping field for this Scorecard needs to be the name of the Sales Rep. For Opportunities this would be the Opportunity Owner. For Activities this would be the Assigned To. For Joined Reports that have different report types in the blocks, the grouping field must be selected from the set of Common Fields. These are the set fields that are in common across the report types used in the report.
If you setup your report blocks with standard report types based upon Opportunity and Activity, the Common Fields are going to be those from the Account. The field on the Account that would most typically reflect the name of a Sales Rep would be the Account Owner.
Let’s consider the impact of using the Account Owner as the grouping field:
- Account Owner gets “credit” for all Oppties on their Account, regardless of Oppty Owner
- Account Owner gets “credit” for all Meetings with their Account, regardless of Assigned To
These impacts will skew the validity of the Scorecard, since the intent of a Scorecard is to reflect what a Sales Rep did, not what we can infer that they did based on ownership of the Account.
That leaves us with the question, what report types can we select for our blocks that will allow us to group by Opportunity Owner and Activity Assigned To?
In this scenario, the goal is to drive the report based upon User information, so that the Full Name of the User can be selected from the Common Fields.
The approach, therefore, is to configure new custom report types: Users with Opportunities and Users with Activities.
In the process of setting up these new custom Report Types, the System Admin will be prompted to specify what User lookup field from the Opportunity and Activity objects should be used as the join criteria. This is where the Opportunity Owner would be selected for the Users with Opportunities report type, and Assigned To would be selected for the Users with Activities report type.
Once your report types are setup, you would then begin the creation of your report and the blocks therein. After all blocks have been added, you will see the Common Fields are based upon the User object, and you can select the Full Name as your grouping field.
And when you click the Run Report button, here’s the final product:
Now, in order to demonstrate the concept of the Cross Block Formula, let’s add another report block that shows the total Amount in the Rep’s Pipeline set to close for the next 90 days. This will be block 4, and is based upon Users with Opportunities, however the filter criteria looks at Open Opportunities (Closed = False).
To get an approximation of what portion of their pipeline the rep might win based on their historical win rate, let’s build a report formula that multiplies the pipeline Amount to the Win Rate from block 2. Therefore, we need a Cross Block Formula so that we can leverage the filter criteria in block 2 for the Win Rate portion, and the filter criteria in block 4 for the Amount portion.
The formula builder for Cross Block formulas allows us to choose which block’s fields to use for each component of the formula. Keep in mind that the “Win Rate” report summary formula from block 2 will not be available for choosing – only fields from the object configuration appear in the picklist. Instead, just re-enter the formula criteria again (Opportunity.Won/Opportunity.Closed.)
Once built, the Cross Block Formula would have the following setup:
And when you click the Run Report button, here’s the final product!!
