Skip to main content The new Salesforce certifications experience is live! Visit Trailhead Academy to explore your new certifications homepage.

Tips & Tricks: Getting more out of Opportunity History Reports

 Opportunity History Reports are f*cking awesome, they allow you to see the complete lifecycle of the Opportunity tracking changes to the "core" Opportunity Metrics: Amount, Stage, Close Date, Probability%, Forecast Category. And unlike Field History Reports they're available right out of the box and immediately have access to historical data from "Day 1" of your SFDC org.  In other words: they work retroactively.  They also have more robust Field Filtering, and access to Cross-Filters (

they also don't look like they fell out of the top of the Ugly Tree and hit every branch on the way down).

  

 However Opportunity History Reports do have an Achilles’ Heel:  You can’t easily see or filter by the current Stage, Amount, Probability%, Close Date, Forecast Category.  But you can do this (and whole lot more!) just by adding a few simple Formula Fields to the Opportunity Object.  Now some folks are probably saying: 

 

“I don’t want to add more formula fields, I don’t want my Opportunity to look like a Race Car at the Daytona 500!”. 

 Here’s the thing; you don’t need to add the new fields to the Opportunity Page Layout, you just need to give the Users/Profiles “Read” access to the Data in the fields.  So you can keep your precious Opportunity Page layouts as pure as the driven snow in an Ansel Adams landscape.

  

 Anyway, it's just an idea that I came up with.  I’m like Leonardo DaVinci, my mind is full of ideas...  although most of them are about as useless as a Wooden Helicopter powered by ropes and pulleys. 

  

 And now without further ado here are the fields:

  

Current Stage

 Datatype = Formula

 Result = Text

 Formula = 

TEXT(StageName)

  

Current Close Date

 Datatype = Formula

 Result = Date

 Formula = 

CloseDate

  

Current Probability%

 Datatype = Formula

 Result = Percent 0 decimals

 Formula = 

Probability

  

Current Amount

 Datatype = Formula

 Result = Currency

 Formula = 

Amount

  

Current Forecast Category

 Datatype = Formula

 Result = Text

 Formula = 

TEXT(ForecastCategoryName)  

 

*** Summer'23 UPDATE***

Current Stage Duration

 Datatype = Formula

 Result = Number Formula = 

NOW() - BLANKVALUE( LastStageChangeDate , CreatedDate )

User-added image

109 answers
Loading
  1. May 27, 2016, 2:55 AM
    Hi Nate,

     

    If you need a report that shows the current Stage, Close Date, $Amount, Probability% and the historical changes to those fields in 1 Report.    

     

    For eaxmple:  Yuo need a Report of all Opportunities that are currently in the Negotiation/Review Stage with the complete History of Stages, $Amount, Probability, Close Date, etc.  

     

    With a standard Opportunity History Report if you Filter the Report by "To Stage" [equals] Negotiation/Review you'll get any Opportunity that was ever in the Negotiation/Review stage regardless of what Stage they are currently in. 
0/9000