A client of ours needed to analyze information about the duration of time users spent in the various stages of their Dynamics 365 Lead-to-Opportunity business process. Searching on the web led me to a few related posts. I used information from a post by Steven Foster to start and formulate a solution: How Can I Work Out the Time Spent in Each Stage of a Business Process Flow?
The author’s approach is quite clever. However, I found that I needed to add a little bit more functionality to satisfy our client’s Dynamics 365 requirements. I thought I would share my changes as an ‘add-on’ to Steven’s insights.
Almost there…
I followed Steven’s instructions and created:
- A new ‘Duration’ activity entity which will have a record to represent each new or change of Business Process Stage.
- A new workflow to automatically create a Duration record and then to update it and ‘complete’ it when the user completes the current Stage and opens the next stage.
That all worked well but I found that I still had a few things that needed attention:
- To make it a little easier to produce Dynamics 365 charts and reports about the Duration data I added another field to the Duration entity.
- If the user followed best practices and clicked the ‘Finish’ control, while D365 properly updated the ‘Business Process Flow’ record and inactivated it, the workflow was not set up to complete the Duration record information for the final Stage of the process.
- Additionally, if the user did not follow best practice and simply closed the record (Opportunity in my case), the Duration record representing the final stage was never updated and ‘completed’
The ‘Other’ field
It’s nice to have the ‘Stage Name’ readily available in the Duration record so we can group by it; I added it and updated the workflow to populate it:
I also had the workflow update the ‘Subject’ field with the Stage Name and Date that it was started so that the Activity Pane info would be more descriptive for the users:
Updating and Completing the Final Stage Duration Record
The original workflow example had a single ‘Wait’ condition that contained the logic shown in the final ‘Otherwise’ action below:
That was working well to update and complete the ‘open’ Duration record when the user moved to a new Stage but didn’t take care of the other possible actions by the user.
So, I had to revamp the ‘Wait’ condition by selecting it and then adding two ‘Parallel Wait Branch’ actions as you see above. This allows you to have multiple concurrent conditions that are being ‘waited for’ and gives you ‘OR’ functionality. In my case, I needed to also check for:
- The user clicking ‘Finish’ (which causes D365 to update the ‘Opportunity to Contract’ entity business process record and to ‘inactivate it’) . . . I know they clicked ‘Finish’ if the BPF record’s ‘Completed On’ contains data.
- The user simply closing the Opp – if they do that, there will still be an active Duration record that needs to have its ‘End Date’ updated and it will need to be ‘completed’ as well.
Once I added the new conditions and tested, I would be able to rely on my Duration records being accurate as to content and as to their status.
This allows us to easily create charts like these using ‘Completed’ Duration records:
In addition to Dynamics 365, you can use Power BI, SSRS or any other method to report on the data . . .
Many thanks to Steven for his original post – I hope this helps to make it even more helpful!!
By John Clifton, Dyn365Pros, Microsoft Dynamics Partner, Microsoft Dynamics 365 Application Consultant San Diego, Southern California
