You can create something called ‘Bucket’ field in Salesforce. With Bucket fields you can categorize the records in the report itself and on the fly. You don’t need to create a custom field on the object for this.
For example, let say that if you are creating a report on Opportunity and want to categorize the opportunities in the buckets of ‘small’, ‘medium’ & ‘large’ based on the opportunity amount. You don’t need to define this category as a formula field on the Opportunity object. You can create a bucket field in the report and categorize opportunity records on the fly in report itself.
And then once you have created the bucket field, you can also use this in a formula field in the report. So, if I continue from the example above, now let’s say that I want to calculate what % of these opportunities are small opportunities, what % is medium & what % is large opportunities.
If you were not aware of these capabilities of Salesforce reports, you are in for a pleasant surprise. In this step-by-step guide we will take a look at
- How to add a bucket field to the report
- How to use the bucket field in a formula field
And it won’t take more than 15-20 minutes of your time to understand all this. So let’s dive right in.
References & Useful URLs:
- Help Article ( 5mins) – Categorize Data with Bucket Columns
- Help Article (5 mins) – Bucket Field Limitations
Blog Change Log:
- 27-Apr-2021 – Reviewed & Updated. Added step-by-step guide and option to download the PDF
- 09-Apr-2014 – 1st Published
Super Helpful! Thank you!
My pleasure Tiffany 🙂
hi ashish how can i use the bucket row count and total in a formula field in my report?
I have the same question!
You can also get the API Name from the HTML of the page.
Thanks for the tip Chelsea.
Is there a means to create a formula which returns a sum for only a subset of the buckets at the grand total location?
Pingback: Tricks all Salesforce Admins Need part 4 of 7 – The Power of the Bucket #ForceFriday | Salesforce Quest
Hi Ash – Could you help me with the syntax for the following formula to calculate the Ratio of columns Won/New Opps. I created a bucket field with two values: Won, New which show up as 2 columns on my report. Need help with using column values and not row values.
Bucket Field Won Opps | Bucket Field New Opps | Ratio of Won/New
1 5 .20
Need help with formula syntax below to give me the results above. Thank you so very much for assistance.
RowCount/PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, BucketField_25017921)
How do I follow this step? “So to retrieve the API Name for Bucket Field, open the report definition in IDE (eclipse) “
How do you create a formula based on a specific bucket value though?
I want to be able to show if Bucket value = X display the amount of 10000
Hi Ashis, I can 1up that. If there’s a single bucket field, it will show within the page source of the bucket distribution setup. So no need to fire up the IDE… ugh. Just typing ‘Eclipse’ gives me the creeps. 🙂
Thanks for the tip Rafel !
Not working. getting field does not exist.