There are many ways to generate reports in SharePoint and Office 365, and of course, those reports will be a key factor in determining the success of the Contract Lifecycle Management (CLM) system. Microsoft Excel has always been a first-class reporting and business intelligence citizen in the Office suite of applications, and that’s definitely true for Office 365 and SharePoint.
We already saw the Sales team convert the home page into a reporting dashboard:
Most fully functional business management solutions include tabular-data reports as shown above, and just as often, they include visual reports that enable business drivers to more quickly assess KPIs and determine the state of the business process. Tabular data has its place, and so do bar charts, line charts, and pie charts with pivot controls, hover data, drill-in capabilities, and so-on. The Sales team knows how to build all of these visual reports in Excel, and in this post we’ll watch how they use SharePoint and Office 365 to build a visual dashboard that accompanies the tabular data available on the home page.
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 SharePoint CLM Tasks List into Excel:
In this example, we’re going to see how to create a report to track counts of Sales Contract Lifecycle Tasks by Sales Contract Stage and the Approved/Pending Task Count for each Sales Contract stage. So, 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 Excel Report with a Pivot Table:
After the export is complete, create their report and save the Microsoft Excel workbook.
Now, upload the Microsoft Excel workbook to the new Sales Report Workbooks library, and also add some groupings to the All Items view in the Sales Contract Lifecycle Tasks list so that you can 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 their new Sales Contract Tasks by Stage and Approval Status report on the reports page created above.
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 room to show the entire report without scrolling.
- Add a quick link to this page
And, here’s the final product:
Remember, these are examples to get you on your way. You should work with the various web part properties and leverage your abilities in Microsoft Excel to build real-world, rich reports. See you in the next post.