Salesforce: The Power of One in Action

A few years ago at Dreamforce, there was a session on reporting that discussed "The Power of One".  In summary, you create a number formula that always evaluates to 1.  In theory, you add this as a summarized column in your report and you can get record counts.

But why do you need this, when record count is available as a standard field?  Let me attempt to explain it, and then we'll see it in action.

The standard record count is fine if you are reporting on a single object.  In other words, your report type is based on a single object, say Accounts, or Opportunities.  Record count is the number of Opportunities in your report.

Now if you use Opportunity Products, which is a child object of Opportunities, and run a report of Opportunities with Products, your record count is actually the number of opportunity product lines, not just the number of opportunities.

You may recall high school mathematics concepts of Cartesian products and unions in Set Theory, but I won't go there.

Let's see this in action:

I have 3 opportunities, and each with 2 product lines.  If they are all in one report, the report record count will show 6.

But management wants to know how many opportunities there are, besides how much and what is product mix. In a recent example, a manager tasked with selling a specific family of products asked how much business is in the pipeline for his product family, and how many opportunities are there?


So, first create a field called "Opportunity Count", which is a number formula:

And then you can add this to any Opportunity report based on a standard report type.  If you have a custom report type already in use, you will need to add this field.

Summarize the count as a sum, and you can create a chart that looks like this, which tells you how much value is open and how many are open:

As you can see in the report itself, the standard record count is 30, but the sum of Opp Count is only 29.  I have one opportunity that has two line items.

It's pretty straightforward once the field is there.  Maybe in a future posting we can explore what else we can do with this Count field.