Overview

This forum post presents an Opportunity Report that helps provide visibility of when the Stage field of Opportunities is being updated, which helps you understand if opportunities are advancing through the sales stages / pipeline, or if they’re stuck (stage hasn’t changed for a period of time).  The report has a Details view (which is intentionally disabled), plus 2 views that display:

• Days since last Stage movement
  • The Days since last stage movement is colour coded, and sorted in descending order to highlight opportunities that haven’t been updated recently.
  • This view bands Opportunities into three bands, which is shown in the Age Band column:
    • Updated within last 30 days
    • Check Progress (>30-90)
    • Update Overdue Progress (>30-90)

• Updated recently?
  • A summarised view, grouped by salesperson and split by age band.
  • There are two split columns which provide monetary totals, and also a count of the number of records in each age band.

The report is available as a template report, named “Template - Sales - Pipeline Stage Duration (Opportunities)”.

• The rightmost column, “No of Stages Moved” highlights any opportunities that have reverted to an earlier stage with a red background.
• The same column also shows the number of stages a deal has advanced by, or stayed at the same stage

How do I build it?

We’ve provided a Template Report (Template - Sales - Pipeline Stage Duration (Opportunities)).  All you need to do is use the template as a starting point.

• Start > New > Report > select “Create a report from a template report”
• Select the “Template - Sales - Pipeline Stage Duration (Opportunities)” template
• Give the report a suitable name and description on the Main tab of the report editor and click Save.

How does it work?

The Details view of the report uses criteria to ensure the report only returns open Opportunities, and Opportunity Audit Data of changes to the Opportunity Stage field.

The “Days since last Stage movement” Summary View used calculated columns and formulae to calculate the number of days since the each opportunity’s stage was last updated.  The view is grouped by Opportunity Object Reference, which means only 1 row is returned per opportunity.  This is essential, otherwise, you are likely to have multiple rows of data, with each row representing each change to the Opportunity’s Stage.

As mentioned earlier, the report also allocates each opportunity into a band via a Calculated Column, which uses a SQL CASE statement.  

Here’s what the code from the “Age Band” Calculated Column.  The CASE statement organises the data into a band, and outputs one of 3 suitable text values e.g. “Updated within last 30 days”.  You can amend or add to the number of lines / bands in the CASE statement:

CASE
     WHEN DATEDIFF(NOW(),MAX(PARENT('Date'))) > 90 THEN 'Update Overdue (>90)'
     WHEN DATEDIFF(NOW(),MAX(PARENT('Date'))) > 30 THEN 'Check Progress (>30-90)'
     ELSE 'Updated within last 30 days'
END

The “Updated Recently” view is a summary view based on the “Days since last Stage movement” view.  It summarises the data by:

• Grouping the data to display one row per salesperson (grouped by Assigned to)
• Splitting the data by Age band, and subtotalling the monetary value (subtotal Amount)
• Splitting the data by Age band, and counting the number of opportunities (counts each opportunity)