In this post I’ll demonstrate how to use Power BI for Reports against CLM Tasks (Contract Lifecycle Management) stored in SharePoint. You’ll also see an important use of Visual Level Filters as well as a demonstration of the Colorblind theme.

This is a fresh spin on a previous post which showed how to report, slice, and evaluate SharePoint CLM tasks’ status using Microsoft Excel. If you haven’t read the previous posts and want context, as well as step-by-step instructions on how to build the CLM system, please have a look at the Contract Lifecycle Management category posts.

Import SharePoint CLM Tasks into Power BI Desktop

First, you’ll want to connect to the Sales Contract Lifecycle Tasks list in SharePoint Online using Power BI Desktop. Then import the content, and delete unnecessary fields.

Step 1 – Specify the Data Source Type using Get Data

Step 2 – Import the Data

Step 3 – Use Power Query Editor to Delete Unwanted Columns

Delete all of the unwanted columns.

Step 4 – Apply Transformations

Trim down the “Title” column to contain only the Sales Contract Title value. Also, create a new column “Time to Approve” that will be used to help track the amount of time it took for an approval to be executed. Note: this is a demo system, so the times are unrealistic for an actual approval business process to be carried out. The upcoming report examples will still be relevant though and demonstrate when a Visual Level Filter is critical to report accuracy.

There are actually a few things being done:

• Stripping out unwanted text on the Sales Contract Title
• Converting Text to Date/Time so that date calculations can be performed
• Adding a column that is the total time to approve (where applicable)
• Normalizing total time to approve to be of the same duration measure in time – seconds.

Create Power BI CLM Task Reports

Let’s now look at building the reports using the Colorblind Theme (thank you for the themes pointer @PowerBIGuy).

Approval Decision by Stage – Count by Stage.

This will let business drivers quickly see throughput of approvals by stage to measure process efficiency at given stages of the lifecycle.

Total Count by Stage

This let’s stakeholders get a very quick view of overall task activity by stage, regardless of whether or not the approval is completed. That is, determining overall task churn.

Time to Approve by Stage – Average Time

Possibly the most useful report of the three in a live/production system. This report will make it easy to identify process bottlenecks by identifying where approvals are getting hung-up. Take note of how the report is built, even if we have an unrealistic data sample. The key to this report being successful, and accurate, is to add the Visual Level Filter. Without it, items that have a 0-second time to approve, and which are not yet actually approved, would have been included in the report and thrown off the average.

Here’s the final screen shot in the Power BI service with more to come from Sales , HR, and other departments.

Categories: Business, Business Management, Contract Lifecycle Management, Office 365 and O365, Power BI, SharePoint

Tags: Business Management, Office 365 and O365, Power BI, SharePoint