Blog

Have you ever come across the need to do actual vs target reporting in Salesforce? It could be to report on if Salespeople are reaching their targets for the value of closed opportunities. It could be whether the number of orders has reached the goal for the month. Another example could be the number of qualified leads created. While it is easy to create reports that summarize actual data, how can we report on that data vs a target?

In this post we will be looking at how to use Custom Summary Formula fields as a means to generate a simple Target vs. Actuals Lead inflow report. Custom Summary Formula fields are an often under estimated tool in Salesforce reporting. Custom Summary Formulas are a powerful reporting technique for summary and matrix reports to calculate additional totals based on the numeric fields available in the report.

An organization may have customer acquisition targets, which are measured in terms of the number of leads brought in per agent. While you can build a simple summary report to represent the number of leads brought in per month – to view this against the total quota for the month, you need to put together a solution involving a few components. Let’s see how we achieve this use case using custom summary formula fields as the central part of the solution.

Firstly, we need somewhere to hold the quota definition. We would like to make the quota data managable and not hardcode it in the actual report, so that it can be updated and extended each year easily. To achieve this, we will use a custom object to hold quota definitions for each month. Consider a simple design with one record representing a year, and 12 fields within the records holding quota targets for each month.

Lightning-1

In order to report on the leads and the quota data, we need to associate each lead record  with the appropriate quota record (based on the Lead created data year) when the lead is created. This association could be done with a lookup field on the Lead record, and populated with an automation tool.

Now, a simple custom report type, exposes all Lead fields, along with the lookup fields from the quota object – which are the individual monthly quota values. The advantage of this solution is that the Quota field values are read when the report is run, so it will always use the latest values. This report type is used to create a regular lead summary report grouped by Calendar Months.

Classic-3

Adding a bar chart at this point would display all record counts, per month. Now we need to produce an additional column alongside each of the monthly record counts.

This is where the custom summary formula field comes in.

Classic-4

In the custom summary formula shown above, a custom formula field “Lead.Month__c” is used to obtain monthly quota values. This field represents the month numeral for created date; and could be obtained through the simple formula:

MONTH (CreatedDate)

Also, since the lead record is linked to a “quota” record, and we are using a Custom Report Type that inherits fields from the associated Quota record; the custom summary formula field can be created as above.

Now, adding a chart with multiple data axis, will achieve the desired Lead Inflow Actuals vs Target representation.

Lightning-2

What Certification are you studying for now?

Focus on Force currently provides practice exams and study guides for sixteen certifications

Target Reports using Custom Summary Formula Fields

Have you ever come across the need to do actual vs target reporting in Salesforce? It could be to report on if Salespeople are reaching their targets for the value of closed opportunities. It could be whether the number of orders has reached the goal for the month. Another example could be the number of qualified leads created. While it is easy to create reports that summarize actual data, how… Read More

Target Reports using Custom Summary Formula Fields

Passion + Non Stop Learning = Salesforce MVP in 2 Years

Salesforce has been in the market for 17 years now, and it has not only helped companies grow but it has also provided countless job opportunities to IT professionals. Vamsi Krishna is one of those who have benefited from the competitive advantage offered by Salesforce in terms of skills enhancement and professional development. He shares how he got started with Salesforce, his successes, learnings, challenges encountered, and his career journey… Read More

Passion + Non Stop Learning = Salesforce MVP in 2 Years

Merge Fields in Salesforce Tasks

Using Workflow to create followup tasks or reminders is a common use case. It is also simple to setup, just create a workflow rule with a rule trigger date a number of days in the future. Say we wanted to have a followup task generated 5 days before the Opportunity close date. This is easy, but standard workflow comes with a limitation, merge fields cannot be used for field values…. Read More

Merge Fields in Salesforce Tasks

Single vs Multiple Sales Processes in Salesforce

  How Many Salesforce Sales Processes Do You Need?Are you contemplating whether to have one or set up multiple sales processes in your Salesforce Org? Prior to determining whether you need a single Sales Process or multiple Sales Process it is worthwhile to look at the definition of a Sales Process without reference to Salesforce:“A defined set of repeatable, interrelated activities from market awareness through servicing customers that allows communication of progress… Read More

Single vs Multiple Sales Processes in Salesforce

Getting a Salesforce Job

Salesforce Jobs I recently sat down and had a chat with Stuart Smith and Bradley Bilinsky, two business managers at Talentforce, an Australian recruitment company that specialize in Software as a Service roles, including Salesforce. One point of difference that Talentforce has from other recruiters is that they also run the Sydney Salesforce User Group, so they are active in the community. Stuart and Brad shared their thoughts on the… Read More

Getting a Salesforce Job

Powering your Salesforce reports using the PARENTGROUPVAL function

  How to Use the PARENTGROUPVAL functionThe ParentGroupVal function is one of the lesser known features of Salesforce reporting. In this article you will learn how to create some key metrics for summary reports by using the PARENTGROUPVAL function within a summary, joined or a matrix report. The function obtains the summary value from a parent group, which is a grouping that is a level above the current grouping. The syntax… Read More

Powering your Salesforce reports using the PARENTGROUPVAL function

Salesforce Custom Related List using jQuery

In the previous article we created a custom account interface by using Visualforce, in this article we’ll extend the functionality by creating a fully custom Opportunity related list. The Opportunity related list will be replaced with a custom table, and it will support: ✓ Pagination ✓ Filtering ✓ Sorting ✓ Quick filtering open Opportunities In order to implement this we need four steps: STEP 1: Create an Apex controller extension… Read More

Salesforce Custom Related List using jQuery

Focus on People: Danielle Tonkin — From Admin to Consultant

Although most Salesforce Administrators are quite happy with their job, I’m sure there are a number who have an interest and aspiration to become a Salesforce consultant. Danielle Tonkin is one of those that made the transition from Admin to functional consultant. Here she shares with us what it is like to work as a Salesforce consultant, the challenges, successes and her tips for those interested in following her Salesforce… Read More

Focus on People: Danielle Tonkin — From Admin to Consultant

Visualforce Tabbed Interface

Using Visualforce to build a tabbed user interface In this example we will override a standard view with a few lines of Visualforce to build a tabbed interface. Use case: Customize the interface for the Account object using Visualforce tabs. The following tabs should be available: Detail – Display general information about record. Inline edit feature should be supported; Contacts – Related Contacts list should be displayed in this section;… Read More

Visualforce Tabbed Interface

Salesforce Global Picklists

Global Picklists One of the most voted ideas in the Salesforce IdeaExchange forum was to be able to create a picklist that can be shared across objects. Shared Picklist Idea Salesforce delivered the idea in pilot as of Winter ’16, and is now in Beta for Spring ’16. If you look at the release notes, you would find it by the name ‘Global Picklists”. It is also referred to as… Read More

Salesforce Global Picklists

Adding CPQ and Billing Functionality to Salesforce by acquiring SteelBrick

On December 23 2015, just before the holidays, Salesforce and SteelBrick announced an agreement for Salesforce to acquire Steelbrick for $360 million. Salesforce has a long history of acquiring companies that extend and complement Salesforce functionality and this is in the mid range of the scale, with ExactTarget ($2.5 billion in 2013) and Buddy Media ($745 million in 2012) at the high end and comparable with the more recent purchase… Read More

Adding CPQ and Billing Functionality to Salesforce by acquiring SteelBrick

Winter 16 Enhancements for Process Builder

Process Builder was one of the most awaited features released by Salesforce as an upgrade to the possibilties of the Workflow functionality and as part of its effort to strengthen what can be done declaratively. In Winter 16 Salesforce continues to add further enhancements to the Process Builder. Schedule Multiple Actions Prior to the Winter ’16 release, it was only possible to define 1 scheduled action per criteria node or… Read More

Winter 16 Enhancements for Process Builder

Salesforce List Buttons Javascript Example

List buttons can be used on list pages, search results and any related list for an object. They allow us to take actions on a group of selected records. In this article we are going to show some examples of how Javascript can be used to work with one or more records in a list view or related list. These are examples for Salesforce Classic, if you want to know… Read More

Salesforce List Buttons Javascript Example

Routing work with Salesforce Omni Channel

In the Winter 16 release, Salesforce has introduced a new feature – the Omni-Channel, which is based on a principle of getting work to the right person at the right time. It helps put rules in place to allow the system to determine the importance of work items, and pass it on the right person who is available.  Salesforce Omni Channel is built on 4 Functional Blocks: Image Courtesy: Salesforce… Read More

Routing work with Salesforce Omni Channel