The power of 1 â€“ counting many things, one by one.
A lot of reporting is often about counting. But when you use summary reports in salesforce.com and count, youâ€™ll mostly get the same number. For example, if I have 5 Accounts each with 5 Opportunities, and I use the record count in a summary report, I will get 25 as the total of Accounts, and 25 as the sum of the record count of opportunities.
Something obviously isnâ€™t as I'd expectâ€¦ but what?
Well, there are 25 records in the list and you are looking at 25 rows on the screen. The counts are right in that respect - but how to get the counts of the exact number of accounts no matter how many rows are on the screen?
For opportunities â€“ create a formula fields â€œ# of opportunitiesâ€� in the opportunity and make it a number, and enter the number 1 in the formula. Also, make it visible to all roles, and uncheck the box to no place it any page layouts.
(the steps for this are shown here:)
Do the same for the accounts - create a formula field called â€œ# of accountsâ€� in the account, make it a number, and enter the number 1 in the formula.
Make it visible to all users, and donâ€™t add it to anybodyâ€™s page layouts.
Now, letâ€™s do a summary report of opportunities:
Now we have the totals we might expect - 32 opportunities, 10 accounts. Also, if we are exporting to a spreadsheet via the "Printable View" to get the summary information, we will be able to see the right totals of the lines in a separate column.
I should really have set the # of decimals to 0 in the formula creation, but that's an exercise for the reader.
Any enhancements, or use cases where this doesn't work - please comment!