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.