Contract Lifecycle Management and Microsoft Excel

There are many ways to generate Contract Lifecycle Management reports in SharePoint and Office 365.

To start, view the backround on how to convert the home page into a reporting dashboard:

Most fully functional business management solutions include tabular-data reports as shown above. They include visual reports which enable business drivers to more quickly assess KPIs and determine the state of the business process. Tabular data has its place, and so do interactive charts with pivot, hover, drill-in, and so-on.

In this post I’ll demonstrate how to use SharePoint and Office 365 to build a visual dashboard for Contract Lifecycle Management with Excel.

Creating the Reporting Page and Document Library in SharePoint:

First, create a new page that will be dedicated to sharing visual reports:

Next, create a Document Library in SharePoint that will be used to house the Microsoft Excel workbooks that contain the reports:

Exporting the Contract Lifecycle Tasks List into Excel:

In this example, we’ll create a report to track counts of Sales Contract Lifecycle Tasks by Sales Contract Stage. The report will also include Approved/Pending Task Count for each Sales Contract stage.

Navigate to the Sales Contract Lifecycle Tasks list, change it to the All Items view so that when you next click “Export to Excel“, the entire list’s contents are exported:

Creating the Contract Lifecycle Excel Report with a Pivot Table:

After the export is complete, create their report and save the Microsoft Excel workbook.

Upload the Microsoft Excel workbook to the new Sales Report Workbooks library. Then, add groupings to the All Items view in the Sales Contract Lifecycle Tasks list in order to validate their Microsoft Excel pivot table counts.

Here’s the side-by-side comparison of values (which are a match):

Using the SharePoint Excel Web Access Web Part to Render the Report:

Finally, use the Excel Web Access web part to surface new Sales Contract Tasks by Stage and Approval Status.

First, adding the web part:

Then, specifying the workbook:

Finally, some cleanup, which you’ve seen in previous posts:

• Edit the web part properties and set the Appearance – Height to 600 pixels to give enough vertical space and no scrolling.
• Add a quick link to this page

And, here’s the final product:

 

See you in the next post.