Ask Search:
Steve MolisSteve Molis 

Tips & Tricks: Getting more out of Opportunity History Reports

User-added image

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)  

User-added image
Best Answer chosen by Miglena (Salesforce.com) 
Steve MolisSteve Molis
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. 

All Answers

Robert StrunkRobert Strunk
Looking for the "Best Question" button but I don't see it.  Must be a permission thing :-)
Dannielle GivensDannielle Givens
Hey Robert, I had the same issue. I noticed it was with my browser. IE didn't allow me to choose the "Best answer". Try Chrome if you haven't already.
Brian MakasBrian Makas
Really cool, thanks.
Kathy BakalisKathy Bakalis
Thanks Steve. This worked perfectly!
Nathan LindstromNathan Lindstrom
Can you post some examples of how those fields would be useful and what you can do with these reports?
Steve MolisSteve Molis
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. 
This was selected as the best answer
Nathan LindstromNathan Lindstrom
Thanks I can see how those fields could be useful to have!
Steve MolisSteve Molis
No problem Nate

"And if you don't know, now yah know..."
Rachel BolenRachel Bolen
Is there a way to get the Opportunity Quantity field in the Opportunity history report? It seems weird that a standard field is automatically apart of the report type.
Steve MolisSteve Molis
Hi Rachel,
Unfortunately the Quantity field is not cataloged in the Opportunity History Object, and the object is locked (custom fields can't be added to it).  
What are you trying to do with the Quantitiy?  You might be able to use an Opportunity Historical Trending Report.  It's not exacly the same, but it might get you where you wanna be.
Rachel BolenRachel Bolen
We are trying to create reports and dashboards comparing previous month or quarter data to current data. We don't use the Amount field on the opportunity - only the quantity (we track units not $). I didn't see Quantity on the Historical Trending Report either.
Steve MolisSteve Molis
Damn it!  You're right...  I dunno why they would not include it.  I can't think of a Technological Wall since Quantity basically behaves exactly the same way as $Amount (it's a Hybrid Editable/Roll-Up Summary Field).  It's not even included in the standard Opportunity Trends report type. 

The only think I can think of would be to create a custom Number field that "mirrors" the standard Quantity field and is updated via a Workflow Rule or the Process Builder.  
 
Otherwise you're off to the Wonderful World of Report Snapshots...
Rachel BolenRachel Bolen
I was thinking the same thing. I have tried Reporting Snapshots but I can't seem to figure out how to report with the historical data and current within the same report. 
Kim McPeekKim McPeek
We have 3 fields in our opportunity that capture dollar amounts.  Projected amount ($ value when opp is created); Quoted Amount ($ value when quote is sent), and PO Value.  The first 2 get factored by the probabilty and are reported as a weighed dollar value.  I created a custom field called "Report Expected Value" that looks at all 3 fields, does the math, then displays the weighed value.  This is so that there is one field to show on the reports.  This field is on the page layout and visable to all users.  I trield to set it for Opportunity field history - but the field is not included in the list of fields?  Did I miss something as i see all of our other custom fields?
Steve MolisSteve Molis
Hi Kim, can you provide more details?  

Are you referring to Formula fields here => "Projected amount ($ value when opp is created); Quoted Amount ($ value when quote is sent), and PO Value.  The first 2 get factored by the probabilty and are reported as a weighed dollar value.  I created a custom field called "Report Expected Value" that looks at all 3 fields, does the math, then displays the weighed value"

When you say "I tried to set it for Opportunity field history" do you mean you tried to Enable Field History Tracking on the custom field(s)? 

PS.  
This is kinda getting away from my Original Post.  I was simply sharing a way that I had come up with that allowed me to compare the current Stage, CloseDate, $Amount, etc to the Historical Data that is captured in the standard Opportunity History Report.  Custom Field are not cataloged in the standard Opportunity History.


 
Kim McPeekKim McPeek
Hi Steve  Thank you and I am sorry to sway the post from the original topic but this is the closet thread I could find to the issue. 

Yes, this is a formula field.  The formula is "Decimal Places2  
(BLANKVALUE( PO_Amount__c , BLANKVALUE( Quote_Amount__c, Projected_Amount__c )) ) * ( Probability )"

As I am just starting to work with histotical reports, I wnt to Customize > Opportunities > Fields > Set History Tracking (this is where I don't see the field)

Ultimately, I need to capture the value in that field (and a few others) at the beginning of the month then again at the end of the month to show how they change.  If I am coming at this the wrong way please correct me.
Steve MolisSteve Molis
No problem Kim,
Unfortunately Formula Fields can't be selected for Field History Tracking or Historical Trending Reports 

#SorryBabyIhadtoCrashThatHonda 
https://www.youtube.com/watch?v=5lL1ypndnWA&feature=youtu.be&t=47s
Kim McPeekKim McPeek
Awsome movie!!

Plan B:  Is there a way I can push the number from that formula field into a custom field that can be tracked?  I tried to do that via a workflow but I wasn't successful there either.
Steve MolisSteve Molis
What are the PO_Amount__c,  Quote_Amount__c, Projected_Amount__c fields?  Are any of them Formulas?  Basically you'd need to create Non-Formula field that are updated by a Workflow or Process that is triggered any time the key fields are changed
Kim McPeekKim McPeek
Steve - thank you for your help!

No, the 3 fields are not formulas they are currenty

So I created a field that is not a formula called "Expected PO Value".
I created a workflow that triggers if the Report extected value is new or changed (screen shot below)
When I tried to create the New Field Update I don't see/know hoe to complete this - apparently all my current workflows are with picklists.  (see screen shot below for where I am stuck)


User-added image


I'm stuck here

User-added image

 
Steve MolisSteve Molis
Click "Use a formula to set the new value"

You're basically gonna be "converting" your Formula Fields into Fields that are Updated using your existing Formula Logic
User-added image
Kim McPeekKim McPeek
Holly Crap!!! Thank you!  Use a formula to trigger it, a formula to fill it in BUT it isn't a formula so it can be tracked AND trended!!!!!!
Audra KempAudra Kemp

Hi Steve,

I have the Opportunity History Report in place, and it's awesome! Current problem is that if I filter by 'Stage Change = True', no Opportunities still sitting in the first stage are pulled in. Have you solved for this yet? I am already utilizing the 3 extra fields to track current stage duration and have pulled that into the report.

Best,

Audra

Steve MolisSteve Molis
Hi Audra,

Can you post a screenshot of the Report, and the Groupings, Settings, etc, that you're currently using and the results you're getting and create a mock-up of what you want your Report to look like? For questions related to Reports those are really helpful.  

Without being able to see the Report and Filters, it "sounds" like what you want is a Stage History Report that includes both Opportunities that have moved from one Stage to another, and also Opportunties that have never moved out of the initial Stage.  Is that correct, or are you looking for something else?
Audra KempAudra Kemp
Steve,

Thanks for the quick response! Yes, that is exactly what I need: both Opportunities that have moved through stages as well as though that have never moved from the initial stage. 

Here is my current Opportunity History Report (filtered for 1 record type). It only shows 5 records but there are 31 total in the system.

User-added image
Audra KempAudra Kemp
those* that have never moved
 
Steve MolisSteve Molis
Okay, do all Opportunties begin in the same Stage?  Like you have a Validation Rule or something in place that requires all Opportunties to start at "Stage-1", or can a user create a new Opportunity and select any Stage?  
Audra KempAudra Kemp
The beginning stage will change based on record type. Technically we do not have anything in place that requires starting in a specific stage, so the user has flexibility to start the Opportunity at a later stage.
Steve MolisSteve Molis
Ouch... okay, this just got a little bit tougher
Audra KempAudra Kemp
I'm wondering if I should just create fields for each stage and track the date change outside of Opportunity History... might be easier than trying to hack it!
Steve MolisSteve Molis
Wait, I'm not ready to throw in the towel yet, lemme poke around and see what I can do.
Steve MolisSteve Molis
Audra,
Which edition of Salesforce are you using?  Do you have access to Workflows or Process Builder?  I might something that will work 
Audra KempAudra Kemp
Steve,

Enterprise Edition. Yes, I have access to and am very familiar with using Workflows/PB.
Steve MolisSteve Molis
Okay I haven't test this out yet, but I'm wondering if doing something simple like this might work.

1. Create a custom Checkbox field on the Opportunity set to Default = Unchecked/False.  
2. Then create a Workflow or Process triggered whenever the Opportunity Stage is changed and an Immediate Action (Field Update) that sets the checkbox to Checked/True.

Then use a Report Filter Logic like

(1 OR 2) 
1. Checkbox = FALSE
2. Stage Change = TRUE
Audra KempAudra Kemp
I believe this method would still pull in all other Opportunity History changes as well, but I will still build it out to test!

I like your thought process of using a hidden checkbox and PB... just not sure if we can set it to filter out the other record changes automatically tracked in the Opportunity History report.
Steve MolisSteve Molis
Thanks Audra, I didn't know that I even had a "Thought Process"
Natalie LevyNatalie Levy
Hey Steve!  Any thoughts are deduping the Opp History report?  Ideally I am able to filter on how many Opps moved to Qualified in a given week.  Problem is two-fold:

1) It may dupe capture Opps if a rep moves one back and forth (even though I include logic to only capture forward moving opps)

2) I am not able to filter on when the stage changed to the desired stage so have to either filter on latest update or last stage date, both of which are proxies (last update is a better proxy)

Thanks!!
Steve MolisSteve Molis
Hi Natalie,

I usually use Tom Tobin's "Power of One" technique to get the distinct count of Opportunity records 

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written*
https://success.salesforce.com/answers?id=9063000000048RbAAI
Natalie LevyNatalie Levy
Power of One is good stuff! Can I expose that metric in my dashboard view? Ideally my dashboard view is the Power of One calc per stage change. And any thoughts on the other issue I mention? Where the report ideally would represent the deals that were changed to a specific stage in a given week? Thanks!
Steve MolisSteve Molis
Hi Natalie,
Can you post a screenshot of the Report, and the Groupings, and Dashboard Component Settings that you're currently using and the results you're getting and create a mock-up of what you want your DB Table to look like? For questions related to Reports and Dashboards those are really helpful
Fran HawkesFran Hawkes
Hi Steve, I have managed to get a report of opportunities that have moved from one stage to another, but it is giving me more than one opportunity as the owner had moved it twice, how do I get the report to suppress these moves and show only real moves to the stage?  I have stage change = true?
Steve MolisSteve Molis
Okay for that you'll need to use Tom Tobin's "Power of One" technique 

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written* 
https://success.salesforce.com/answers?id=9063000000048RbAAI
Fran HawkesFran Hawkes
Thank you Steve!  Worked a treat.
Thomas SchwennThomas Schwenn
SF support tells me this cannot be done - but I'm looking to detail the # of days a Split Stage sits in.
I currently have a field/formula for "Days at Current Stage" - which resets every time the Split Stage changes. However, I want to show each the time in EACH Split Stage. Ideally, I'd like to use it to include in a Pipeline Movement report - such as the attached image

Here's the architecture:

Standard Object: Opportunties
Custom Object: Opportunity Splits
Field: Split Stage
Split Stages:
  • On Hold
  • In Discussion
  • Proposal/Pitch in Development
  • Qualified
  • Proposal Preliminary
  • Awaiting Decision
  • Project Awarded
  • pipeline movement report

 
Shirley QuShirley Qu
Hello Steve, Hello all,

I kindly need your mighty mind / experience to help me with some challenges, please. Thanks in advance.

Our Sales Management wanted to change / replace existing Opportunity Stage Picklist Values to a different set of values, and at the same time to maintain Stage History record. I saw there're some discussion around how Opportunity History is not properly capturing New Stages (meaning an old value instead of the new value is showing in report). Have you ever encountered this and is there any workaround that will save me from manually manipulate the data? Here is the details:

Existing Stage Picklist Values
1. New (5%)
2. Discovery (20%)
3. Qualify (50%)
4. Proposal (75%)
5. Gain Approval (90%)
6. Won (100%)

New Stage Picklist Values
1. New (5%)
2. Prospect (10%)
3. Qualify (25%)
4. Solutioning (50%)
5. Validation (70%)
6. Gain Approval (90%)
7. Won (100%)

Appreciate any ideas / advice. Thanks so much
Shirley
 
Steve MolisSteve Molis
Unfortunately there is no way to update the From Stage and To Stage values on the existing History records with your new Stage Values
Shirley QuShirley Qu
Thanks Steve, for your prompt response. Much appreciated. 
Have a good day!
Steve MolisSteve Molis
I've created an Unmanaged Package of some of my favorite custom Formula Fields

SteveMo's Custom Field Mix Tape Version 1.0

The package includes:
  • The Power of One (on all Standard Objects)
  • Opportunity Status
  • Current Stage
  • Current Probability%
  • Current Close Date
  • Current $Amount
  • Plus more...
   
Use this URL to install the package into any organization:
https://login.salesforce.com/packaging/installPackage.apexp?p0=04t4P000002EaaG  (https://login.salesforce.com/packaging/installPackage.apexp?p0=04t4P000002EaaG )

Password = UoweB33R

Note: If you are installing into a sandbox organization you must replace the initial portion of the URL with http://test.salesforce.com

The App is free, but if you really like them and are so inclined here's a link to my PayPal (no obligation)  
http://paypal.me/SteveMoForce

Thank you,

SteveMo
Kyle ScheetzKyle Scheetz
Hi Steve - We are trying to do a simple calc of win rate by stage. Is this doable with basic fields that exist on the opportunity record without formulas on the backend? I am hoping that by having a dataset of opportuntities in a range of stages will be the basis for what we need to achieve this.
Steve MolisSteve Molis
That should be possible using the standard Closed and Won checkbox fields, but having The Power of One and the Current Stage or Status will make things a lot easier (especially building any Report Summary Formulas) 
Aggie McCordAggie McCord
Hey, Steve!  I'm not sure if the thread addresses what I'm trying to do using the Opportunity History Report.  I have been asked to provide a report which shows:
  1. How many opps moved from a specific stage to closed/won and closed/lost
  2. How long does it take to get from this specific stage to closed/won
When I run the report, it isn't clear to me that I'm getting the right results - here's a sample of the report I created.  What am I missing?

User-added image
Steve MolisSteve Molis
Hi Aggie,

I think you should be able to do that using some of the custom fields that I created in this post, plus Tom Tobin's "Power of One" technique =>

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written* 
https://success.salesforce.com/answers?id=9063000000048RbAAI 



Are you looking for something kinda, sorta, like this?
User-added image
Aggie McCordAggie McCord
Hey Steve - thanks for responding!  I believe the chart you provided answers the first requirement which is fantastic.  I just want to make sure I clearly understand what the report shows.  When an opp reaches Value Prop stage, the win rate is 73% while loss rate is 26.7%, correct?

I'll read through the thread to get the setup deets!
Aggie McCordAggie McCord
Sorry, Steve, but I need more help on this one.  I alread had the PO1 field (I found your thread on that one a few years ago!) and created the fields you laid out; however, I'm struggling w/the summary formulas - do you mind sharing the summary formulas you used for the win rate and loss rate?
Steve MolisSteve Molis
Hi Aggie here are the Formulas and settings that I used (they're actually easier to build in Classic)

1
User-added image
2
User-added image
3
User-added image
Aggie McCordAggie McCord
Ok - this is really helpful but I'm not there yet - sorry, Steve!  Lost doesn't up in my list of fields to choose from - is it a custom field?
Steve MolisSteve Molis
Which field?  

Opportunity.Opportunity__c is the API Name of my Opportunity Power of One field

Can you post a screenshot? 
 
Aggie McCordAggie McCord
  1. Won is a field I can choose from standard fields when adding a column to the report but Lost is not.  I was asking if you created a custom field for Lost.
  2. Did you create bucket fields for Status in order to group by column?
  3. Here's a snapshot of my report so far and it is very far from the report you created.User-added image
Steve MolisSteve Molis
Oh, that's just a custom Formula(Text) field that I created on my Opportunity object called Opportunity Status. 

My org has been around since 2003, way before Report Buckets were even a thing, so it simplify my Reports (and maintain my sanity) I created a Formula(Text) field like this

Fieldname = Status
Type = Formula 
Result = Text 
Formula = 
IF(IsWon = TRUE, 
"Won", 
IF(IsClosed = FALSE, 
"Open",
"Lost"))
I also created custom Formula(Checkbox) fields to get the distinct count of Open and Lost and make wrting Report Summary Formulas easier

Fieldname = Open
Type = Formula 
Result = Checkbox 
Formula = 
IsClosed = FALSE
Fieldname = Lost
Type = Formula 
Result = Checkbox 
Formula = 
AND(
IsClosed = TRUE, 
IsWon = FALSE
)

Otherwise you can do it in a Report Summary Formula by subtractind the WON:SUM from the CLOSED:SUM like this
( CLOSED:SUM - WON:SUM )  /  CLOSED:SUM




 
Aggie McCordAggie McCord
Ok - that makes sense!  I did create a fomula field for lost but I still couldn't get my report to look like yours.  I will update but I know I'll be back for one more question!

I cannot thank you enough!
Steve MolisSteve Molis
No problem Aggie, I'll keep your tab running at @Who owes me a beer
Steve MolisSteve Molis
PS.  For the report that you described, you should be filtering to only resutrn Opportunities that are Closed, so unless your org has mutliple Stagenames for Closed/Won and Closed/Lost you could just group it by my custom Current Stage formula field at the top of this thread 
Aggie McCordAggie McCord
I'm pretty sure it's a year's worth of beer for this!
Steve MolisSteve Molis
I'll keep your tab open at Who owes me a beer ;-D
Aggie McCordAggie McCord
You have quite the list of people who owe you a beer - impressive!
 
Aggie McCordAggie McCord
Ok - I finally got back to the report!  I created test records and then validated the data.  It seems to me that the stats reflect the intial From Stage.  For example, if I'm looking for stats on how many are either created at or reach Stage 3 to closed / won or closed / lost, this report as is won't provide that information.  Is that correct?