It’s a fairly common problem – you want to have a true record count in a Salesforce report.
But let’s say you have 4 accounts, each with 3 opportunities, then your record count is 12.
But hey, you want to know how many accounts are listed, not the number of records listed on the page, right?
I’ll admit it’s not such a big problem when you’re viewing 12 accounts but I’m often viewing many hundreds. Then one day, I came across The Power of One, and I’ve never looked back.
Here’s a step by step guide to creating it yourself.
There are 2 accounts listed below, but the Grand Total is 4. How do I summarize the number of unique account records?
Go to App Setup (you’ll need to be an admin) and select Accounts and then Fields
Click ‘New’ under Account Custom Fields section
Select Data Type as ‘Formula’ and click Next
Give your new field a name, select ‘Number’ and then press Next
Enter the figure ‘1’ in the formula field and click Next
Unselect the Add to page layout option and hit Save
Go to “customize” your accounts report. Add your new field (AccNo) as a column. Select the “Summarize this Field” option as shown below.
Select “Sum” and then “Apply”
Run your report and hey presto, the number of unique account records is shown at the foot of the column
I’ve created a custom formula field that displays the Account AccNo field shown above on the Opportunity object. That allows me to create an Opportunity report, put AccNo as a column, summarize that column and see the true Account record count when I run Opportunity reports.
It’s worth mentioning you can use the Power of One on any object.
PS – See our Ebsta for Salesforce Administrators page for more information on how Ebsta can revolutionise your Salesforce.