This Article covers a customization which allows you to Report on combined data when a Staff member is listed as Sales Rep 1 on some Appointments and listed as Sales Rep 2 on other Appointments. This customization involves custom formula fields, new custom report types, building a Joined Report and using Cross Block formulas. If you are unable to setup this customization on your own, or would like our help, our team can build this for you, for a one-time fee of $500.
Admins: Be ready to work with a Joined Reports to join Sales Rep 1 and Sales Rep 2 data and create Cross Block formulas to capture the Sold Price when the person is Rep 1 and Rep 2, and the Sum of Canceled Amount as both Rep 1 and Rep 2.
Create Custom Formula Fields
First, we will create Formula fields on the Appointment and Sale objects to split the number of Issued/Run/Demo/Sold Appointments and Split the Sold Price and Canceled Amount between two Sales Reps. In total we will create 4 custom fields on the Appointment object and 4 custom fields on the Sale object.
Create a Formula field labeled Issue Split. Choose an output type of Number with only 1 decimal place.
If the Sales Rep 2 Field is not Blank, give a value of 0.5, else give a value of 1. You can copy this formula for the Issue Split:
IF (ISBLANK(text(i360__Result__c)), 0, IF( ISPICKVAL(i360__Result__c, "Not Covered") ||
ISPICKVAL(i360__Result__c,"Not Confirmed") || ISPICKVAL(i360__Result__c, "Canceled") ||
i360__Disregard_In_Statistics__c =true && (NOT(ISBLANK ( i360__Sales_Rep_2__c ))), 0.5,
IF (ISBLANK(text(i360__Result__c)), 0, IF( ISPICKVAL(i360__Result__c, "Not Covered") ||
ISPICKVAL(i360__Result__c,"Not Confirmed") || ISPICKVAL(i360__Result__c, "Canceled") ||
i360__Disregard_In_Statistics__c =true && (ISBLANK ( i360__Sales_Rep_2__c )), 0, 1
))))
Be sure to treat Blank fields as Zeros so they can be Summed in a Report.
Next we'll create the Demo Split formula field. Choose an output type of Number with only 1 decimal place. You can copy this formula for the Demo Split:
IF ((i360__Demoed_Not_Sold__c = 1 || i360__Follow_up__c = 1 || i360__Sold__c = 1 ) &&
i360__Disregard_In_Statistics__c = false
&& (NOT(ISBLANK( i360__Sales_Rep_2__c ))),0.5,
IF ((i360__Demoed_Not_Sold__c = 1 || i360__Follow_up__c = 1 || i360__Sold__c = 1 ) &&
i360__Disregard_In_Statistics__c = false
&& (ISBLANK( i360__Sales_Rep_2__c )),1,0))
Be sure to treat Blank fields as Zeros so they can be Summed in a Report.
Next we'll create the Run Split formula field. Choose an output type of Number with only 1 decimal place. You can copy this formula for the Run Split:
IF ((i360__One_Leg__c = 1 || i360__No_Show__c = 1 || i360__Sit__c = 1) && i360__Disregard_In_Statistics__c =FALSE && NOT(ISBLANK( i360__Sales_Rep_2__c )),0.5,
IF ((i360__One_Leg__c = 1 || i360__No_Show__c = 1 || i360__Sit__c = 1) && i360__Disregard_In_Statistics__c =FALSE && (ISBLANK( i360__Sales_Rep_2__c )), 1, 0))
Be sure to treat Blank fields as Zeros so they can be Summed in a Report.
Next we'll create the Sold Split formula field. Choose an output type of Number with only 1 decimal place. You can copy this formula for the Sold Split:
IF ( TEXT( i360__Result__c) = "Sold" && i360__Disregard_In_Statistics__c =FALSE
&& NOT(ISBLANK( i360__Sales_Rep_2__c )), 0.5,
IF ( TEXT( i360__Result__c) = "Sold" && i360__Disregard_In_Statistics__c =FALSE &&
ISBLANK( i360__Sales_Rep_2__c ), 1, 0))
Be sure to treat Blank fields as Zeros so they can be Summed in a Report.
Next, we need to get the Canceled Amount and Canceled Sale split fields on the Sale object.
Navigate to Setup > Objects > Sale
Thankfully the system has out-of-the-box Rep 1 split and Rep 2 split fields. For this formula we will say if the Canceled Date field is not Blank then give me the Rep 1 Split value, else give a value of 0.
Next we'll create the Canceled (Rep 1) formula field. Choose an output type of Number with only 1 decimal place. You can copy this formula for the Canceled (Rep 1):
IF(NOT(ISBLANK( i360__Canceled_Date__c )), i360__Rep_Split__c ,0)
Next we'll create the Canceled (Rep 2) formula field. Choose an output type of Number with only 1 decimal place. You can copy this formula for the Canceled (Rep 2):
IF(NOT(ISBLANK( i360__Canceled_Date__c )), i360__Rep_Split_rep_2__c ,0)
Be sure to treat Blank fields as Zeros in both these formula fields.
Be sure you have created two number fields: a Canceled (Rep 1) and Canceled (Rep 2) field so that they can be Added together using a Joined Report and a Cross Block Formula.
Next, create two (2) custom currency fields to calculate the Canceled Amount based on the Rep Split.
In this case, we can use the out of the box Rep Split Rep 1 and Rep Split Rep 2 fields to calculate the Canceled Amount for each Sales Rep, based on the Rep Split values.
Choose an output type of Currency with 2 decimal places. You can copy this formula for the Canceled Amt (Rep 1):
i360__Canceled_Amt__c * i360__Rep_Split__c
On the second formula field, again choose an output type of Currency with 2 decimal places. You can copy this formula for the Canceled Amt (Rep 2):
i360__Canceled_Amt__c * i360__Rep_Split_rep_2__c
Be sure to treat Blank fields as Zeros in both these formula fields.
Once these fields have been created, you can move on to the next step, creating Report Types.
Create 2 new Custom Report Types
Next, you'll need to create the following new Report Types:
- Staff with Appointments as Sales Rep 1 with or without Sales
- Staff with Appointments as Sales Rep 2 with or without Sales
Navigate to Setup > Report Types > click New Custom Report Type
Choose Staff as the Primary Object
Give the Report Type a Label of: Staff w Appointments as Sales Rep 1 w/wo Sales
Store in the Other Reports Category
Be sure to check Deployed
The Report Type will look like this:
Be sure to select that Staff records must have related Appointment records with that Staff member as Sales Rep 1. This ensures that the Report filters out Staff who were never assigned an Appointment.
Create another Report Type for Appointments as Sales Rep 2.
Choose Staff as the Primary Object
Give the Report Type a Label of: Staff w Appointments as Sales Rep 2 w/wo Sales
Store in the Other Reports Category
Be sure to check Deployed
That Report Type will look like this:
Once you've created these two Report Types you can continue and create the new Custom Report.
Create new Custom Joined Report
Go to the Reports Tab and click New Report
Choose the Report Type you just created of Staff w Appointments as Sales Rep 1 w/wo Sales
Be sure to change your Filters to:
Show: All Staff
Date Field: Start (ALL TIME)
Change your Report Format from a Tabular report to a Joined report.
This will allow you to Add a second Report Type
This time you will select the Staff w Appointments as Sales Rep 2 w/wo Sales report type:
Be sure to adjust the Filters in the second Report block (you may need to drag the pane so it is larger, and you can see both sets of Filters).
Show: All Staff
Date Field: Start (ALL TIME)
Notice that I renamed each Report Block to make Formula writing simpler to read. Click into the Report Blocks to Rename them. I've named the first Block "Rep 1" and the second Block "Rep 2" which means the Data when someone has been assigned as Sales Rep 1 will be visible in the first block and when they've been assigned as Sales Rep 2 the data will show in the second block.
Now you'll Group the Report by the Staff Name
Click the field and drag into the Preview pane where it says "Drop a field here to group across report blocks."
Now your Preview should have the Appointments grouped by Staff member Name:
At this point, we recommend Saving your Report!
Build Report Formulas
Next, we will need to build custom Formulas to Add together the Number of Issued/Run/Demoed/Sold Appointments. Double-click on Add Cross Block Formula
Name the Column "Issue" and choose a Number format with one (1) decimal place.
From here we will need to use the BLANKVALUE function to identify if the Sum of the Issue field is Blank or Null. If it is, give a zero (0) else give the Value from the Field.
Choose the Function menu and select BLANKVALUE, then click Insert
Within the Formula, highlight the word "expression" and click on Summary Fields:
Expand the Rep 1 block to expose the fields from the Rep 1 Block.
Locate the Issue Split field we created in Step 1 and choose Sum
This field will be inserted into the Formula Builder and will look like this:
Next highlight "substitute expression" and replace it with 0. Your Formula should look like this:
Enter a Plus sign or insert an Operator of Add
Insert another BLANKVALUE Function. This time, highlight "expression" and replace it with the Issue Split Sum from the Rep 2 Block.
Next expand the Rep 2 block Fields and choose the Issue Split Sum from the Rep 2 block.
Your completed Formula should look like this:
BLANKVALUE([Rep 1]i360__Appointment__c.Issue_Split__c:SUM, 0)
+
BLANKVALUE([Rep 2]i360__Appointment__c.Issue_Split__c:SUM, 0)
Click OK when finished.
Remember to Save as you work!
We will repeat this process and build Report Formulas which combine the metrics for Run, Demo and Sold using the Run Split, Demo Split and Sold Split fields we created in Step 1. Be sure you are choosing the correct Report Block and adding together the Demo Split for Rep 1 with the Demo Split for Rep 2.
If you renamed your Report Blocks to Rep 1 and Rep 2 you should be able to easily see which Block the Formula is pulling its data from:
Next, we will create the Canceled formula, Sold Price formulas, and Canceled Amt sum formulas so we can see how many Sales Canceled for each Rep, and what the Total Canceled Amount is.
For this we will also need to use a BLANKVALUE function and use the Summary Fields from the Sale record.
For Sold Price there are out of the box fields which split the Sold Price between Rep 1 and 2. We can use these Sold Price (rep 1 split) and Sold Price (rep 2 split) out-of-the-box fields for the next Formula:
Be sure the Sold Price Formula is set to Currency and has two decimal places:
You should be able to easily see where the data is being pulled from. Rep 1 data is pulling from the Rep 1 block and uses Rep 1 Split Sum while the second half is coming from the Rep 2 block and the Rep 2 Split Sum. Note: if either Sum are blank, the system will use a Zero in its place.
BLANKVALUE(
[Rep 1]i360__Sale__c.i360__Sold_Price_rep_1_split__c:SUM,0)
+
BLANKVALUE(
[Rep 2]i360__Sale__c.i360__Sold_Price_rep_2_split__c:SUM,0)
Next, we'll create the Canceled Amt formula to combine any Canceled Sales if the Staff member was either Rep 1 or Rep 2.
The completed Formula will look like this:
BLANKVALUE(
[Rep 1]i360__Sale__c.Canceled_Amt_Rep_1__c:SUM,0) +
BLANKVALUE(
[Rep 2]i360__Sale__c.Canceled_Amt_Rep_2__c:SUM,0)
Click OK when finished.
Remember to Save often!
At this point, you should have the following Formulas in the Report Builder:
Lastly, we will need to create a formula to deduct any Canceled Sales from the Sum of the Sold Price in order to see the Total Sales volume for each Staff member.
Remember to Save as you go!
The Total Sales formula is a Currency formula with 2 Decimal places. If you have named your Fields exactly as described in this Article, and you've renamed your Report Blocks to "Rep 1" and "Rep 2", you should be able to simply copy/paste this formula:
(BLANKVALUE([Rep 1]i360__Sale__c.i360__Sold_Price_rep_1_split__c:SUM,0)
+
BLANKVALUE([Rep 2]i360__Sale__c.i360__Sold_Price_rep_2_split__c:SUM,0))
-
(BLANKVALUE([Rep 1]i360__Sale__c.Canceled_Amt_Rep_1__c:SUM,0)
+
BLANKVALUE([Rep 2]i360__Sale__c.Canceled_Amt_Rep_2__c:SUM,0))
Now let's pull all of our Formulas into the Report!
Click and Drag the Formulas into the Report Preview pane.
Once you've pulled all the formulas into the Report, click the Show menu and Hide Details.
Remember to Save your changes often!
Next, you'll want to remove the Record Count column from both Blocks.
Click Run Report and the Report should look like this:
NOTE: Because this is a Joined Report, if you want to Adjust the Date Range, you will need to click Customize and add Date Filters to BOTH Report Blocks.
Customize the Date Range of a Joined Report
If I wanted to see the metrics only for the LAST 90 DAYS worth of Appointments, I would need to change the Date Range Filter in both Report Blocks, so that we see Appointments run as Sales Rep 1 for the Last 90 Days, and Appointments run as Sales Rep 2 also for the Last 90 Days.
Click the Customize button and be sure to drag the Preview pane down so you can see both Filter blocks.
Be sure the Blocks are set to the same Date Range and use the same Date Field in the Filter!
Department Filter Considerations
If you notice the Sales Rep 1 field is used for multiple Departments (like Sales and Service) Use the Staff's Position Title as a Field Filter so the Staff object can be filtered to show only those Staff Members in Sales.
REMEMBER: You'll need to add the Same Field Filter to BOTH Report Blocks to ensure consistency of the data displayed between Rep 1 data and Rep 2 data. Here is a screen shot to help with this concept:
Notice how this Filter has impacted my Report. Here is the Report without the Position Title Filter:
And here it is After applying the Filter to show only Staff with "Sales" in their Position Title.
If you are unsure what the Values are in the Position Title field, go to the Staff Tab and add the Position Title to your Active Staff list view. Click Edit if you need to add the field to this list.