Using PrevGroupVal formula in Salesforce Reports

Use the Report Summary Formula PrevGroupVal to compare metrics between peer groups. For instance use it to compare won amounts over successive periods such as month-on-month comparisons.

To do this create a Report grouped by Close Date. The dates must be grouped by the period you require such as by Month or Quarter. Or you could use Close Month, Fiscal Period, etc.

Order the Groups in ascending date order so that comparison formula makes sense.

Create a Report summary field using PREVGROUPVAL which shows the amount variance compared to the previous period as a percentage. PREVGROUPVAL uses these parameters:

PREVGROUPVAL(summary_field, grouping_level [, increment]) where the

Summary_Field is the name of the grouped row or column (like AMOUNT:SUM). Grouping_Level is the summary level (such as CLOSE_DATE)

The formula is:

(AMOUNT:SUM – PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)) / PREVGROUPVAL(AMOUNT:SUM, CLOSE_DATE)

Set the Display level to specific groups

Run the report which will now include the month-on-month variance as a percentage.

Add a Chart with a line chart to represent the variance to appreciate the full effect of the comparison.

Identifying sales pipeline leaks

When assessing lost opportunities it’s instructive to review where in the pipeline are opportunities being lost. Put another way we want to know at what sales stages do we experience pipeline leakages.

While all sales pipelines will naturally leak (after all you can’t win every opportunity) it’s important to analyse where and why opportunities are lost. Lost deals point to problems in the sales process depending on where they leak from. These problems could include no emotional connection, information overload, undefined needs, poor on boarding, etc.

Opportunities should also not linger in the pipeline for too long. After a certain period the chances of closing an opportunity decrease dramatically and should exit the pipeline anyway.

Using standard Salesforce reports we can develop a view of where in the sales process deal are lost. To do this we will start by creating an Opportunity History Report.

  • Switch to Report Edit mode and select the Filter section
  • Update the Opportunity Close Date filter to reflect the period under review.
  • Add a To Stage filter and set it to Closed Lost as we want to analyse lost opportunities
  • Switch to the Outline section and add the From Stage and To Stage fields (in that order) under Group Rows
  • Then add a chart to the Report and select the Funnel chart type
  • For readability select the Show Values and Show Percentage chart options
  • This will render a funnel chart showing the stages where opportunities were lost as well as the number of opportunities and the percentage of lost opportunities they represent.

There you have it! You can now drill down to the underlying opportunities to assess where in the pipeline the opportunities leak.

Spring 22 release

Spring 22 is upon us. Some highlights:

Revenue Intelligence provides sales leaders with actionable insights throughout all customer touch points — from clinching deals, to setting forecasts, and to generating pipelines.

Pipeline inspection which provides AI insights on opportunity progress, and engagement and activity details. You can also view pipeline changes in a flow chart view and edit opportunity fields inline. See full list of Sales enhancements here.

Orchestrator is now generally available to customers. It enables customers to automate and optimise complex and multi-user business processes. This can include internal and external users .

Mobile enhancements for Lightning Web Components which adds native device capabilities such as camera GPS and geofencing.

Dashboard and reporting enhancements including making it easier to select report types when creating a new report (much needed), editing multiple fields inline in report run mode (beta) and dynamically updating gauge chart values (GA in this release).

Full list here.

App Builder Winter 22 maintenance

Here’s a look at the features covered in the maintenance module.

Multi-factor Authentication (MFA)
From February 2022, MFA will be required – there may be some exceptions – for all customers. This is to provide increased user account security in the face of the global threat landscape. The added security outweighs the inconvenience. Salesforce provides an MFA rollout pack.

Interactive Lightning Pages with Dynamic Interactions
Increases interactivity of lightning pages. Devs create a component and define the behaviour i.e. the events which are exposed. Admins hook up the component on the Lightning page by creating interactions between the source and target components.

Grant access based on Activated User Sessions for Permission Set Groups
Enhances the ability of session-based access control. Previously, this had to be managed at the permission set level but with this enhancement it can be done at the permission set group level. The hands-on activity relates to this feature.

Additional options when debugging a Flow
A number of options to manage how Flow debug messages are displayed.

Lightning Page performance analysis
Use this tool to get insights on the performance of custom Lightning components. This tool includes suggestions on how to improve performance after it detects possible issues.

Salesforce Winter ’22 Release

Some great new features released. Here are a few of our favourites.

Dynamic Gauge Charts

Dynamically update gauge target performance values with values from the underlying report. Without this feature the target performance value had to be manually updated. Details here.

Control access to sensitive data

Previously some record visibility was exclusively governed by the parent record visibility. For example, a Task or Event’s visibility is based on the related Account’s visibility. If the Account publicly visible so were the child tasks and events.

This update allows for a further level of restrictions to be applied. Details here.

Org-specific performance analysis of lightning page performance

Lightning page performance has been a user bugbear from day 1. This tool gives you Org-specific metrics on page performance during the last 90 days. This highlights the worst and best performing pages. Details here.

Dynamic interactions

Javascript developers will know about dynamic interactions in which an action in one part of a page will cause a different but related part to change. This can now be in Salesforce. Developers can write LWC which can then be exposed in the Lightning App Builder. Admins can then configure these in the page builder. Details here.

Restriction rules

Restriction rules enable better visibility control over some objects. For example, tasks and events inherit permissions from the parent object they are related to, such as an Account record. If the Account is publicly visible so will be the child events and tasks. By using restrictions rules visibility can be limited in various ways such as limiting permissions to only the task record owners. Details here.

Flows

There is a lot to unpack. Not least of which is all things Flow. Flow is set to be the future for workflow automation and will completely supplant Process Builder at some point.

Full release details here.

Issue with role hierarchy reporting in Lightning

If an Org has two nodes immediately below the prime node there is an issue with including all records owned by users in these two nodes.

Under the prime node CDV there is a CEO and a Co CEO node.

role hierarchy

When defining the scope of records in a report filter the prime node, CDZ, cannot be selected.

report role selection

A choice has to be made between one or the other CEO node.

report two nodes

This is not a problem in a list view since all opportunities can be selected in the record scope without having to choose a node other than the prime node as is the case for reports.

The workaround is to either use a list view or create the report in Classic where all opportunities can be selected. Note that if you edit the report in Lightning after it has been created in Classic it will assume the above behaviour and the report will be limited to the first CEO node.

Update (4th Nov 2018): This is a known issue and will be fixed in a Winter ’19 patch