We can create bucket columns in salesforce reports to categorize data without creating a formula. When we create a bucket column, we should define multiple categories (buckets) used to group report values. Like any other column in a report, we can sort, filter, and group by bucket columns. This article explains how to Categorize Data in Salesforce reports with Bucket Columns. The concept can be simply understood with the help of an example.
The use case
In a mortgage firm, Salesforce uses the custom ‘Loan’ object to store the details of loans. Loan types are categorized as FHA, VA, Conventional, HELOC etc. We need to create a report which shows the number of loans under each of these categories owned by each loan officer. Also, a bucket named ‘Size’ is created in which each loan will be categorized into ‘Small’, ‘Medium’ and ‘Large’ buckets based on the range of values defined in the ‘Loan Amount’ field.
Create report with bucket columns
- Navigate to ‘Reports’ and click ‘New Report’
- Select the report type ‘Loans’ and click ‘Start Report’ (Loan is the custom object on which reporting is enabled).
- In the ‘Outline’ section, add relevant fields in the ‘Columns’. The fields selected are Borrower name, Loan name, Loan Owner name, Loan type.
- In ‘Groups’, under Group Rows Add Loan:Owner Name as the first field to group the data. In this example, there is only a single owner. For orgs with many owners/Loan officers, this is very helpful to gain better insights into the data.
- The next field which is to be selected to group data is the ‘Loan type’. This is meant to get a quick view of the number of loans under each loan type owned by a single loan officer.
- Now click on the drop down arrow on the right of the Columns section and click ‘Add Bucket Column’.
- A new window will be opened to enter the bucket details.
- Choose Loan amount in the fields section.
- Type in ‘Size’ as the ‘Bucket name’
- Now we have to categorize the buckets as ‘Small’, ‘Medium’ and ‘Large’ by defining the range of values in each bucket based on the loan amount field we selected.
- Click ‘Apply’
The ‘Outline’ will look like this.
In ‘Filters’, add the filtering criteria based on your requirements. In the current example, it is given as below.
- Now Save the report and click ‘Run’
- You can see a bucket column is added in the report where you can see the number of loans under each loan type for each user categorized based on the loan amount.
- The report data can be visualized by adding it as the component in the Loan data dashboard.
- It is displayed as horizontal bar chart in colour customized dark theme.
To read more about reports and dashboards in salesforce, click Reports and Dashboards (salesforce.com)
Take Five Consulting is a technology company, based in Virginia U.S., that specializes in the Mortgage Banking vertical especially LOS implementation and application development. Take Five Consulting creates and implement mortgage technology and software specifically for Mortgage Industry.