Salesforce Report Formulas: When to Use PARENTGROUPVAL vs PREVGROUPVAL

In Salesforce.com Reporting, PARENTGROUPVAL and PREVGROUPVAL are functions used in custom summary formulas within reports to calculate and compare summary values based on grouping levels. These functions are particularly useful in matrix and summary reports, where data is grouped and summarized by different dimensions.

PARENTGROUPVAL

PARENTGROUPVAL is used to reference summary values from a parent grouping level. This function allows you to retrieve the value of a summary field from a parent group relative to the current grouping level.

Syntax:

PARENTGROUPVAL(summary_field, parent_grouping_level)

• summary_field: The summary field you want to reference (e.g., SUM of Amount, COUNT of Opportunities).
• parent_grouping_level: The parent grouping level relative to the current level.

Example:

Let's say you have a report summarizing sales data by Region and then by Country. You want to calculate the percentage of sales each country contributes to its parent region's total sales.

1. Report Grouping Structure:
   • Parent Group: Region
   • Child Group: Country
2. Summary Field: SUM of Amount
3. Custom Summary Formula:SUM(Amount) / PARENTGROUPVAL(SUM(Amount), REGION)

In this example:

• SUM(Amount) calculates the total sales for each country.
• PARENTGROUPVAL(SUM(Amount), REGION) retrieves the total sales for the parent region.
• The formula then calculates the percentage of the total regional sales that each country contributes.

PREVGROUPVAL

PREVGROUPVAL is used to reference the value of a summary field from the previous group value at the same grouping level. This function is helpful when you want to compare the current group's value with the previous group's value within the same level.

Syntax:

PREVGROUPVAL(summary_field, grouping_level[, increment])

• summary_field: The summary field you want to reference (e.g., SUM of Amount, COUNT of Opportunities).
• grouping_level: The grouping level at which to calculate the previous value.
• increment (optional): An integer indicating how many previous groups to skip (default is 1).

Example:

Assume you have a report summarizing monthly sales data and you want to calculate the month-over-month change in sales.

1. Report Grouping Structure:
   • Grouping Level: Month
2. Summary Field: SUM of Amount
3. Custom Summary Formula:SUM(Amount) - PREVGROUPVAL(SUM(Amount), MONTH)

In this example:

• SUM(Amount) calculates the total sales for the current month.
• PREVGROUPVAL(SUM(Amount), MONTH) retrieves the total sales for the previous month.
• The formula calculates the difference in sales between the current month and the previous month.

Key Differences:

• PARENTGROUPVAL: Used to reference values from a parent grouping level. It's useful for comparing values between different levels of grouping, such as comparing a subgroup's value to its parent group's total.
• PREVGROUPVAL: Used to reference values from the previous group at the same grouping level. It's useful for sequential comparisons within the same level, such as month-over-month or quarter-over-quarter comparisons.

Practical Scenarios:

1. Using PARENTGROUPVAL:
   • Scenario: You have a sales report grouped by Region and then by Salesperson. You want to calculate each salesperson’s contribution to their respective region’s total sales.
   • Formula:SUM(Amount) / PARENTGROUPVAL(SUM(Amount), REGION)
   • Result: This formula will show what percentage of the region's total sales each salesperson is responsible for.
2. Using PREVGROUPVAL:
   • Scenario: You have a sales report grouped by Month. You want to compare this month’s sales to last month’s sales.
   • Formula:SUM(Amount) - PREVGROUPVAL(SUM(Amount), MONTH)
   • Result: This formula will show the difference in sales between the current month and the previous month.

These functions are powerful tools for gaining deeper insights into your data by allowing for sophisticated comparisons and calculations within Salesforce reports.