Microsoft Dynamics 365 makes it hard to report on the time an Opportunity (or other entity) sits in particular Sales Stage within a Business Process Flow (BPF). Open an Opportunity in Microsoft Dynamics 365 and you can see straight away how long the Opportunity has been ‘stuck’ in its current process stage but try to report on this via a System View or Advanced Find. Not easy. This picture shows the data we are trying to retrieve:
Provide visibility on how long an Opportunity has been ‘stuck’ in a particular sales stage and report on Open Opportunities (neither Won, Lost or Cancelled) that have stalled for a particular period of time as well as how long the previous stage took.
- Bottlenecks can be identified in a business where previous stages are taking more time than expected
- Current Opportunities that have stalled can quickly be identified
- Alerts can then be put in place via workflows to identify Opportunities that require additional attention which are sent to the relevant person.
The Opportunity system views offer nothing that indicates a view to provide current Opportunity active stage duration:
‘Advanced Find’ is not helping either:
The closest ‘Advanced Find’ comes to helping with providing BPF duration is the ‘Active Stage Started On’ field. What we are looking for is a ‘Previous Stage End Date’ or ‘Previous Stage Duration’ field.
If you follow the simple steps below, you will find out how easy is to get visibility and report on an ‘Opportunities’ active (current) sales stage without code.
1. Create a custom activity entity and fields to record the duration of each Opportunity Sales stage. Activity entities are ideal as they already have a lot of the required fields created already and provide visibility to each stage activity record within the related Opportunity in the Social Pane:
- Call this Activity Entity ‘Stage Duration’ or similar
- Create new fields to capture additional data including ‘Closed Stage Duration’ and ‘Active Stage Duration’. The ‘Active Stage Duration’ captures the current time (the time the users hits save) and compares it to the Activity ‘Actual Start’ field. ‘Active Closed Duration’ compares the ‘Actual Start’ and ‘Actual End’ date/time stamp
- Optional fields can include the Activity ‘Total Opp Duration’ or the related Opportunities ‘Current Stage Name’
2. Use existing activity fields and custom fields to build a workflow that creates and updates ‘stage duration’ activities upon Opportunity create or edit.
- The workflow process triggers against the Opportunity Sales Process entity in the background (upon save) to first create an activity and again once the Opportunity Sales Stage is updated or the Opportunity Status is updated as Won or Lost.
- Each activity is completed as another one for the new active stage is created.
The results are:
- An activity record that can be viewed and queried:
- System Views (or personal views if required) can be created for the Stage Duration Activity that highlight
> An example of Open Opportunities with their current Active Stage durations. The duration field column clearly highlights the Opportunities where the current Active stage duration has stalled for longer periods of time (days vs hours):
> An example of Completed Stage Duration activities from both Active and Closed Opportunities which can be used to report on bottlenecks in an internal business process:
These fields could then be displayed on an Opportunity as a Roll-up field or listed as a related field in an Opportunity view. Activity views could form the basis of charts and reports that provide further statistics against particular internal Users, Accounts or Contacts.
I hope this helps.
Please do not hesitate to reach out to me should you have any other doubts about this.