Loading
Salesforce から送信されるメールは、承認済ドメインからのみとなります続きを読む

Aggregate the number of records at each grouping level

公開日: Jul 18, 2025
説明
I need to create a report grouped by a picklist but the record count displayed should be aggregated at each level.

For example, if I were to create a report on leads and group them by status, and my lead status are: Open-Not Contacted, Working-Contacted, Closed-Converted, and Closed-Not Converted. 

Let's say I have a total of 100 leads. My lead status report will show something like 20 Open-Not Contacted, 50 Working-Contacted, 20 Closed-Converted, and 10 Closed-Not Converted. 

What I would like to see is: 100 Open-Not Contacted (Everything that entered the system), 80 Working-Contacted, 30 Closed-Converted, and 10 Closed-Not Converted. 
 
解決策

The below is specific to the lead status, but this can apply to any report grouping. However, it works best with single picklist or checkboxes as there are set values that work well with formulas.

Also, if you have more or less than 4 values in your grouping, change the formulas accordingly. 

Step 1: We need to list the Statuses in order. By default, Salesforce sorts them in alphabetical order. The easiest way to do it is to create a bucket column (see image below). 



Step 2: Create as many buckets as you need for your statuses, but name them as shown below, with a number in front of their names:
 

1- Open-Not Contacted
2- Working-Contacted
3- Closed-Converted
4- Closed-Not Converted
 



Step 3: Click on Unbucketed Values bucket, select the first status, click Move to, and select "1-Open-Not Contacted." Repeat for each value and then click Apply. 



Step 4: 

  1. Now, group your report by this bucket.
  2. If the highest value needs to be the one with the smallest count, click on the Bucket column in the report so the order is inverted (the top one will be "4-Closed-Not Converted"); otherwise, leave as-is. 

Step 5: Create a Row-Level Formula by clicking the down arrow in the Columns section and selecting Add Row-Level Formula. 

Add Row-Level Formula.png

Note: 
  • Give it a name and make sure it returns a number without any decimal points. 
  • Enter the below formula. 
Tips: 
  1. If you will reuse this formula in other reports, it would be better to create formula field on the lead object. 
  2. If using a Row-Level Formula, select the STATUS from the left-hand-side panel to get the right spelling. 
CASE(STATUS, "Open - Not Contacted",1, "Working - Contacted",2, "Closed - Converted",3,4)
 


Note: As an example, if you have 5 values, the formula would be:  
CASE(STATUS, "Open - Not Contacted",1,
"Working - Contacted",2,
"Closed - Converted",3,
“Value4”,4,5)
Step 6: Create a Summary Formula by clicking the down arrow in the Columns section and selecting Add Summary Formula. 

Add Summary Formula.png

Step 7: Enter the following formula, returning a number with no decimal points.  
RowCount+
CASE(CDF1:AVG,
4,0,
3,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980),0),
2,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980),0),
BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980,3),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_60270980),0))


Tips: 
  1. Select the necessary fields from the left-hand-side panel to get the right spelling and "Average" in the dropdown. If using a Row-Level Formula, it will show as CDF1:AVG in the formula box. 
  2. Your bucket name will be different to the one in this formula. You will find it on the left hand side panel in the formula by scrolling all the way down and selecting it from the Grouping Fields section. 
  3. As an example, if you have 5 values, the formula would be: 
RowCount+
CASE(CDF1:AVG,5,
0,
4,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257),0),
3,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257),0),
2,BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,3),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257),0),
BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,4),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,3),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257,2),0)+BLANKVALUE(PREVGROUPVAL(RowCount,BucketField_52627257),0))

Step 8: While in the formula window, click the Display tab, select the Specific Groups level, select your bucket column grouping, and click Apply. 



And voila!!!

Here's an example of what your report should look like (with details hidden) with the Row-Level Formula (with averages) shown in green, the record count for each specific status in blue, and the aggregated record count in red: 

Report Result.png
 
ナレッジ記事番号

000393469

 
読み込み中
Salesforce Help | Article