Tag: Summary Report

The Basics of Using PREVGROUPVAL

This post covers the basics of using the PREVGROUPVAL summary formulas in reports, and how the requirements change in matrix reports.

The PREVGROUPVAL function in Salesforce is fortunately a little easier to understand than its sibling, PARENTGROUPVAL. If you haven’t read that article yet and don’t yet understand Summary Formulas yet, I recommend starting there for a primer and then coming back!

Basic PREVGROUPVAL Summary Formula (Previous Quarter’s Total Opportunity Amount)

We’ll start here with a report that simply demonstrates how the PREVGROUPVAL formula works, and then we’ll build off that to show something more interesting in useful. What we’re building here simply displays the total Opportunity amount from the previous Opportunity group.

Let’s start first with a very basic Opportunity Report, with a configuration something like this:

Report Builder Sidebar (Outline tab) from a Salesforce Opportunity report, with 'Fiscal Period' field in the Group Rows section and 'Account Name', 'Opportunity Name', 'Stage', 'Close Date', and 'Amount' in the Columns section
Group Rows: Fiscal Period
Columns: Account Name, Opportunity Name, Stage, Close Date, Amount

Then we’ll create a Summary Formula.

Click the Caret next to Columns, then click Add Summary Formula

Selecting the 'Add Summary Formula' option from the caret menu in the Columns section of the Report Builder sidebar on the Outline tab

Let’s add a new Summary Formula which looks like this:

Let’s name our column Previous Quarter Total, set the output type to Currency, and Decimal Points to 2.

For the Formula, just put the following code, then click Validate:

PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)

This sums the Amount field within the previous group, and then displays it.

When you try to hit Save or Validate, you’ll see a warning like this:

You must select a grouping context to use any report summary function.

To fix this, go into the Display tab, set the formula to apply to Specific Groups, and then in this case, Row Group should automatically be set to Fiscal Period

On the Display tab Where should this formula be applied?: 'Specific Groups' Row Group: 'Fiscal Period'

Now we can click Apply.

Now let’s do one more thing before reviewing our work:

Clicking the Caret next to Amount reveals additional field options in the report. Choosing Summarize, and then Sum adds data to the subtotal rows in the report that adds the value of that column together at each grouping level.

Go up to the Amount field, click the caret, hover over Summarize, and click Sum.

Now let’s take a look:

Report showing the total $ Amount for each Fiscal Quarter, and our Summary Formula displaying the previous Quarter's total $ Amount.

Notice how the the Previous Quarter Total field for Q1-2023 contains the same value as the Amount Subtotal for Q4-2022. Likewise the Previous Quarter Total field for Q4-2022 contains the same value as the Amount Subtotal for Q3-2022. This is how PREVGROUPVAL works: It allows us to compare our summary in the current grouping to a previous grouping in our report.

1 more thing to check out: You see that the Previous Quarter Total field for Q3-2022 is blank? That’s because there’s no previous group to compare against. Our report only contains data back to Q3-2022, so there’s no Q2-2022 to compare with.

How to Skip Groups

It’s not uncommon to need to skip groups when you’re summarizing data. Maybe you want to compare this fiscal quarter against the same one last year? Or the number of text messages sent each day versus the same day 1 week ago. Fortunately both of these use cases are supported, and pretty simply at that!

Let’s re-open the Previous Quarter Total summary formula again by double-clicking it, and we’ll make 1 small change:

Updating the Previous Quarter Total summary formula to add a 3rd parameter, which specifies how far back PREVGROUPVAL should look.

See the change? We added a new parameter, and set the value 2:

PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER, 2)

Go ahead and click Apply.

Report view showing that by setting a value of 2 in the 3rd parameter of PREVGROUPVAL, we we're now showing the total $ Amount from 2 quarters prior instead of 1

You can see now that Previous Quarter Total field for Q1-2023 now matches the Amount Subtotal for Q3-2022. It skipped Q4-2022 entirely! If we could fit Q2-2023 on-screen, we’d see it now reflecting the value in the Amount Subtotal for Q4-2022.

Additionally, notice that the values for Previous Quarter Total field for both Q3-2022 and Q4-2022 are blank. Once again there’s no data available to compare against, so the values are blank.

Basic PREVGROUPVAL Summary Formula (% Change in Amount by Quarter)

Let’s move on to something a little more useful. Let’s take compare opportunity amounts by quarter to see how they change:

We're updating the field name to '% Change From Last Quarter' and updating the formula to: ( AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER) ) / PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)

We’ll first change the Column Name to something like % Change From Last Quarter, then change the Formula Output Type to Percent. Then we’ll enter this as our formula:

(
    AMOUNT:SUM - PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)
) / PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER)

AMOUNT:SUM represents the current period’s total Amount

PREVGROUPVAL(AMOUNT:SUM, FISCAL_QUARTER) represents the previous period’s total Amount

So written more simply, we’re calculating the following:

(CURRENT_PERIOD_AMOUNT - LAST_PERIOD_AMOUNT) / LAST_PERIOD_AMOUNT

This should give us a result where from quarter to quarter, if the total mount increases, we see a positive value representing how much of an increase we see. Likewise if the total amount decreases from quarter to quarter, we should see a negative value representing how much the total amount went down.

Click Apply.

Report view showing that the '% Change From Last Quarter' field now describes the percentage increase from the previous quarter, such as an increase from $865,000.00 to $1,495,000 being represented as 72.83%

You should now see that the % Change From Last Quarter field reflects the percentage change from period to period.

We also see decreases from quarter to quarter handled intuitively:

Another example from the report which demonstrates how the report handles decreases Quarter-over-Quarter as negative numbers, such as $1,495,000 to $1,430,000 being represented as -4.35%

Handling Multiple Group Rows

PREVGROUPVAL fortunately handles multiple row groupings fairly intuitively. If you have a summary formula for Fiscal Period and add another row grouping beneath it for Account Name, it handles this without issue.

The situation you do need to be mindful of is if you add a grouping level above a grouping level for an existing summary formula. This splits up your records and means that instead of your formula representing the change from 1 quarter to the next for all accounts, it would now represent the change from 1 quarter for a given account, to the next quarter for the same account:

  • % Change in Amount by Quarter
  • % Change in Amount by Quarter (subdivided by Account)

This is probably not what we want. So just be mindful that Salesforce won’t throw an error if you were to make a change like this, even though it changes the meaning of the data.

Basic PREVGROUPVAL Summary Formula (…, Matrix Report)

Let’s up the ante one more time and look at using PREVGROUPVAL in a Matrix report. Let’s go ahead and move Account Name down into the Group Columns:

In the Report Outline sidebar, we're moving 'Account Name' down from Group Rows into Group Columns

By converting this to a matrix report, we pack a LOT of data into a small space and once again our formula works! Just like in the double-group-rows example, the % Change From Previous Quarter field is demonstrating the % change for a given account instead of the quarter as a whole.:

Matrix Report demonstrating how the '% Change From Previous Quarter' field now represents the % change in Amount for a given Account. Accounts with consecutive $0 quarters may display a '#Error!'

Do note however that if you move Fiscal Period down to the Group Columns section instead, that % Change From Previous Quarter disappears:

When we try to move our existing 'Fiscal Period' Row Group down to a Column Group, our summary formula is removed since technically its configuration is no longer valid.

If you try to add it back, you’ll notice something different:

The 'Display' tab of the 'Summary Formula Editor' now gives an extra selector for 'Column Group'. Both of the 'Row Group' and 'Column Group' selectors default to 'Grand Total Only'

When you try to set formula groups on the Display tab, you now have options for both Row Group and Column Group. Respectively, Row Group and Column Group each only provide options for Grand Total Only and whichever fields you have in the Group Rows and Group Columns.

  • Column Group Options: Grant Total Only and Fiscal Period
  • Row Group Options: Grant Total Only and Account Name

If we Select Grand Total Only for Row Group and Fiscal Period for Column Group…

Updating the 'Display' tab of the 'Summary Formula Editor' such that 'Row Group' is set to 'Grand Total Only' and 'Column Group' is set to 'Fiscal Period'.

… and hit apply, we get… an interesting result:

Report sample demonstrating that while '% Change From Previous Quarter' appears in each row, it is only populated with data in the 'Total' row. It now represents the change across all accounts.

% Change From Previous Quarter is back, but only in the Total row. Notice that these results match what we were seeing in the second example, where we only had 1 grouping level, and it was telling us the % change for all accounts by quarter.

What happens if we change Row Group to Account Name?:

Report sample demonstrating how '% Change From Previous Quarter' now populates in each row and represents the change from quarter to quarter for a given Account.

We’ve now lost the total-level % change, but now have % Change From Previous Quarter grouping by Account Name again.

We can actually still have both versions in the the report, we just have to create another Summary Formula Column with the same formula, but with different Row Groups (just make sure to name them intuitively)

Report Example demonstrating that by having 2 '% Change From Previous Quarter' fields that utilize different Row Group selections, we can display both the change from quarter to quarter by account, as well as across all accounts.

That’s it! You now understand the core concepts around working with the PREVGROUPVAL formula in reporting. If you’d like more practice, I recommend working through this exercise on Trailhead:

Compare Groups with PREVGROUPVAL() and PARENTGROUPVAL()

or for an extra challenge, take on the Lightning Experience Reports & Dashboards Specialist Superbadge (what a mouthful!).

Lastly, if you haven’t already, check out my other article on The Basics of Using PARENTGROUPVAL.

The Basics of Using PARENTGROUPVAL

This post covers the basics of using the PARENTGROUPVAL summary formulas in reports, and how the requirements change in matrix reports.

In my current admin role, it’s not in my job description to do reporting, but somehow I always find myself embroiled in emergency requests to draft reports for executives at my company. Maybe it’s just me, but I’ve always found the PREVGROUPVAL and PARENTGROUPVAL functions in Salesforce to be far more confusing than almost anything else. I think this comes from the weirdly sparse documentation around these two functions as the fact that their behavior is so wildly different from anything else we’re used to.

When we look at Salesforce’s documentation for these functions, it’s… rough… They give you a high-level explanation of how the functions work, but don’t give you much in the way of how to use them beyond a simple example:

PARENTGROUPVAL and PREVGROUPVAL on Salesforce Help

In addition to this, they allude that there is a 3rd grouping level that has to be applied on matrix reports, but then don’t tell you much about what’s actually supposed to go there. So let’s try to answer that question and break down some examples. For this to make sense, we need to try to understand how summary formulas work, since that’s how we get our column groupings. Let’s do that first!

Basic Summary Formula (Count Records in a Group)

First, let’s start with a very basic Opportunity Report, with a configuration like this:

Report Builder Sidebar (Outline tab) from a Salesforce Opportunity report, with 'Account Name' field in the Group Rows section and 'Opportunity Owner', 'Opportunity Name', 'Stage', 'Amount', and 'Fiscal Period' in the Columns section
Group Rows: Account Name
Columns: Opportunity Owner, Opportunity Name, Stage, Amount, Fiscal Period

Then we’ll create a Summary Formula.

Click the Caret next to Columns, then click Add Summary Formula

Selecting the 'Add Summary Formula' option from the caret menu in the Columns section of the Report Builder sidebar on the Outline tab

Next, let’s name our column Row Count, set the output type to Number, and Decimal Points to 0.

For the Formula, just put RowCount, then Click Apply.

Column Name: 'Opportunity Count' Formula Output Type: 'Number' Decimal Points: '0' Formula (General tab): 'RowCount'

That will give you a page that looks something like this:

Report preview showing the total number of rows at each grouping level (Account Name), as well as at the grand total level

You’ll now have your new Opportunity Count column, and on the Subtotal Rows for Account Name (our group column), you’ll have the number of records within each grouping, as well as the total number of records in the report in the Total Row (or Grand Total)

This takes us to an important facet of summary formulas. You can choose which groups they apply to, as well as whether it should be calculated for the Grand Total. This becomes important once we try to do something like calculate the percentage of Opportunities held by each Account. In fact… let’s do that!

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Summary Report)

We’ll add a new summary formula that looks like this:

Column Name: '% Records' Formula Output Type: 'Percent' Decimal Points: '0' Formula (General tab): 'RowCount / PARENTGROUPVAL(RowCount, GRAND_SUMMARY)'

We’re setting the Column Name to % Records, a Formula Output Type of Percent, and 0 Decimal Points.

For the Formula, we’re going to use:

RowCount / PARENTGROUPVAL(RowCount, GRAND_SUMMARY)

This is taking the total count of the rows in each group, and dividing it by the row count at the Grand Total level.

When you try to hit save, you’ll probably see this warning now:

You must select a grouping context to use any report summary function.

To fix this, go into the Display tab, set the formula to apply to Specific Groups, and then in this case, Row Group should automatically be set to Account Name

On the Display tab Where should this formula be applied?: 'Specific Groups' Row Group: 'Account Name'

Now we can click apply.

Report preview showing the percent of all opportunities that each group (Account Name) represents

Looking back, that requirement kind of makes sense. If we tried to do this calculation at the Grand total level, we’d get 100%, so it wouldn’t tell us much. Let’s make this more difficult though and turn this into a matrix report to really show off how these summary formulas work, as well as PARENTGROUPVAL.

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Double-Summary Rows Report)

So what happens when we use two different grouping levels? Now we have to chose which groupings our column will apply to. To test this out, let’s move Fiscal Period into the Group Rows under Account Name.

Move the 'Fiscal Period' field into the Group Rows section of the Report Builder Sidebar, under 'Account Name'

That gives us a report that looks something like this:

Report preview showing records now grouped first by 'Account Name', then by 'Fiscal Quarter'

Notice that the summary formulas still apply! Let’s re-open % Records and go back to the Display tab.

We now have two options for our Row Group— one for each of our group rows.

On the Display tab, we can configure which grouping we want to apply the formula to ('Account Name' or 'Fiscal Period')

Let’s take a look at both to compare them:

  • Account Name Summary (Percent Opportunities per Account)
  • Fiscal Period Summary (Percent Opportunities Per Quarter Per Account)

You can see above how our percentage calculation changes depending on which group we select. You’ll also notice that Opportunity Count doesn’t seem to care which is selected because we previously selected All Summary Levels for that summary field.

Now that we can see how those summary levels work, let’s go bigger and apply this to a matrix report.

Basic PARENTGROUPVAL Summary Formula (% Records in Group vs Total, Matrix Report)

If you’re still following along from above, let’s turn this into a matrix report by moving Fiscal Period down into the Group Columns.

Move 'Fiscal Period' field down from Group Rows into Group Columns

When you try to make this change, you’ll get an error like this:

Salesforce Error Message, saying: Incorrect number of parameters for function 'PARENTGROUPVAL()'. Expected 3, received 2
Incorrect number of parameters for function ‘PARENTGROUPVAL()’. Expected 3, received 2

This is because your PARENTGROUPVAL function now requires you to also choose which column grouping it should use. This is unfortunately not well documented. The documentation linked above shows that it’s needed, but doesn’t tell you what goes there. Let’s fix that:

Back in the summary formula editor, we must add a 'Column Grand Summary': RowCount / PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, COLUMN_GRAND_SUMMARY)

First, we need to update the PARENTGROUPVAL formula to use ROW_GRAND_SUMMARY and COLUMN_GRAND_SUMMARY (These are not in the Salesforce documentation at time of writing):

RowCount / PARENTGROUPVAL(RowCount, ROW_GRAND_SUMMARY, COLUMN_GRAND_SUMMARY)

Next, we need to go into the Display tab and set our Row Group and Column Group. We’ll set Row Group to Account Name and Column Group to Fiscal Period

On the Display tab, we can now apply 'Account Name' as the Row Group and 'Fiscal Period' as the Column Group

Now let’s hit Apply.

Report preview now showing that '% Records' now sums across all grouping combinations to 100%, and splitting the Row Group (Account Name) by each Column Group (Fiscal Period), and displaying the '% Records' for each subgrouping
Row Group: Account Name, Column Group: Fiscal Period (% Records Per Account Per Quarter)

This gave us a breakdown of the percentage of opportunities within each account, per each quarter.

Let’s look at the other options:

  • Row Group: Account Name, Column Group: Fiscal Period (% Records Per Account Per Quarter)
  • Row Group: Grand Total, Column Group: Fiscal Period (% Records Per Quarter)
  • Row Group: Account Name, Column Group: Grand Total (% Records Per Account)
  • Row Group: Grand Total, Column Group: Grand Total (% Records Per… Table?)

As you can see, the double-Grand Total option isn’t particularly useful, but the other options, which summarize by Account, by Fiscal Quarter, and by both can provide a valuable breakdown of our Opportunity data.

Those are the basics on how summary formulas and PARENTGROUPVAL work. In another post, we’ll break open some more of the possibilities with the function, complete with more examples and use-cases!

In the meantime, try it out yourself with this exercise on Trailhead!:

Compare Groups with PREVGROUPVAL() and PARENTGROUPVAL()

or for an extra challenge, take on the Lightning Experience Reports & Dashboards Specialist Superbadge.