Using ParentGroupVal in Salesforce reporting formulas

By adminBlog, Report Formulas, Reports & Dashboards, Sales Analytics, Salesforce-How To

The ParentGroupVal function is used in Salesforce reports to group the values of a field by the value of a parent field.

For instance, assume you have a report on Accounts, and you want to group the accounts by the value of the Sector field. You can use the ParentGroupVal function to do this by adding the Sector field as a “Group By” field in your report.

Then use the ParentGroupVal function as the value for the group. The resulting report will show the accounts grouped by their Sector, with the Sector value displayed as the group label.

Here are some examples:

  1. Sum of group values: SUM(Group_of_Sales_Figures__c)
  2. Average of group values: AVG(Group_of_Test_Scores__c)
  3. Count of group values: COUNT(Group_of_Employees__c)
  4. Maximum value of group: MAX(Group_of_Product_Prices__c)
  5. Minimum value of group: MIN(Group_of_Product_Prices__c)
  6. Weighted average of group values: SUM(Group_of_Test_Scores__c * Weight__c) / SUM(Weight__c)

In the above formulas, “Group_of_Sales_Figures__c”, “Group_of_Test_Scores__c”, “Group_of_Employees__c” and “Group_of_Product_Prices__c” are the names of the specific fields in your Salesforce object that contain the values you want to report on. You can replace them with the actual field names in your Salesforce object.

Here’s an example of how to use the ParentGroupVal function to compare pipeline opportunities in a report:

  1. Create a new report using the Opportunities object and add the fields you want to include in the report, such as name, stage, amount, and close date.
  2. Drag and drop the ParentGroupVal function from the “Functions” section of the report builder into the “Group By” section.
  3. Select the field that you want to use for grouping, for example “stage”
  4. Drag and drop the “Amount” field into the “Summarize” section and select “Sum” as the summary type.
  5. Run the report to view the grouped data. It will show you the total amount of opportunities grouped by the stage and will give you a better comparison of how the pipeline is performing in each stage.
  6. You can also add filters to the report to further refine the data, such as only showing opportunities that have a close date in a specific range.

By using this method, you can easily compare the pipeline opportunities in your organization and track the performance of each stage. You can also use this method to group by other fields like close date, owner, or product.

Another example: let’s say you have a report that groups Opportunities by Account, and you want to calculate the total revenue for each Account. You could create a formula field called “Account Revenue” with the following formula:

ParentGroupVal(Opportunity.Amount, “Account”)

This formula would calculate the total revenue for each Account by summing up the Amount field of all Opportunities that are grouped under that Account. The resulting “Account Revenue” field would show the total revenue for each Account in the report.

Zaheer Ismail