Flow approval escalation with analytics

Introduction

In one of my previous post I’ve illustrated how to achieve Flow approval escalations based on the timeout event. I decided to go one step further : providing escalation analytics on top of this. Read that post first if we really to understand the current one 🙂

The basic idea of the flow was that an expense information stored in a SharePoint list had to be approved by the Line manager; if the line manager didn’t react on time, the flow will escalate the approval to the big boss until the big boss approved.

The SharePoint list looks like this and of course the Flow will update the approval status:

Now, on top of this I wanted to know the persons who approves slowly in such way that I can contact them understand why and as such improve the business process.

To achieve it I’ve worked with my partner in crime Isabelle Van Campenhoudt (MVP data platform), both of us use to share our love of PowerBI since the beginning of the product.

Also this flow has been demoed during our ESP17 session “Improve your productivity with Microsoft Flow : Power to the people” (powerpoint here).

The analytics

So in the end I wanted to get the following kind of Insight: (click to zoom)

How can I analyze this dashboard ?

• I have 7 flows, the reaction time of all flows is 52.74, and in average the number of  escalations is 3.
• The expense “Antwerpen” had a first escalation (yellow), followed by 3 re-escalations (red and black).
• The expense Chicago has been approved without any escalation.
• As illustrated in the next picture, if I move the mouse over the escalation blocks I can even see the details: (the reescalate took 1 minute & 2 seconds ) (we had to use very low value in order to debug & test our proof of concept).

At the top of the dashboard I’ve the list of approvers ; so for instance (see next picture) if I select Brian Cox (in black in the next picture), I see that : Brian Cox is involved in 4 workflows approval and his average reaction time is 57.42 : (click to zoom)

Technically this is actually a PowerBI report embedded in a SharePoint online modern page (in the PowerBI web part).

We have many others analytics to show but that is not the point of this post.

The Flow

The logic of the flow is displayed here (click to zoom)

After every approval I log the approval information in a SharePoint list as illustrated below (this is for the proof of concept only, I recommend an Azure SQL database)

 

The logging approval list looks like this (click to zoom):

The workflow instance and the approval are identified by a GUID. (I’ve generated my own workflow Guid, but I know I could have used the internal flow instance id). In a flow I can have several logical approvals, each approval can have several escalations.

I’ve defined several type of approvals :

• NORMAL : approved or rejected without escalation.
• ESCALATE : no reaction (first level)
• RESCALATE : no reaction (second level)
• REACTIONAFTERESCALATION : somebody approved/rejected after an escalation

 

So voilà, I hope that you enjoyed this post. Of course we will improve this example in the new future; we know that the Flow team is implementing some kind of analytics in Flow, but we really wanted to be able to achieve exactly what we wanted. Job done!

I will probably demonstrate the whole flow in a webcast; stay tune !

Also eventough I use a SharePoint list to approve some items, this is not the typical SharePoint content approval where a document is visible to users only if the document has been approved; the SharePoint (?) team is currently implementing the flow actions in order to achieve this.

If you like this post, don’t hesitate to share it on twitter and to mention my twitter id (@sergeluca). And also don’t forget my business partner & partner in crime Isabelle Van Campenhoudt (@the sqlgrrrl).