Reporting Accurate Record Counts

If you’ve created a report in Salesforce you’ve no doubt noticed that sometimes the record count that is shown doesn’t quite jibe with the one you were expecting.

In this example, let’s say you wanted to know how many Accounts there were on the report, the Grand Total shows 7 Records. But wait, if you look closely there are only 3 accounts and 7 contacts. So how can you report on the TRUE count of Accounts?

For a small report like this you could hide the contacts and count the Accounts by hand or you could export it to Excel but I have a better solution.

Simply add a custom formula field to each object with the value of 1 and sum that field. I know, I know, it sounds crazy. I personally scoffed at this idea the first time I heard about it at Dreamforce a few years ago. But trust me, it works. Add the formula and voila! you get this instead on your reports…

Look how nicely it counted the Accounts for me. She’s a thing of beauty.

Here is what the formula looks like. The value of the formula is “1”–yes, literally the number 1 in the formula field. Salesforce gurus call this “The Power of One“.

Pro tip: When creating the formula there’s no need to add it to a page layout since it is for reports only.

The Salesforce Power of One Formula is Super Easy to Create

If you’re like me you’re probably still a bit skeptical but give it a go anyway, I know that once you try it you’ll be using it all the time and adding it to additional objects. Trust me, you’ll love it.

>>ST