Microsoft deprecated the out-of-the-box SharePoint Chart Web Part prior to the release of SharePoint 2013, and in SharePoint 2016, Excel Services has also been deprecated. Instead you will need to use Excel Online in Office Online Server. So what if you would like to add a quick chart to your SharePoint Team Site? Lightning Tools offer a SharePoint 2016 Chart Web Part that enables you to build pretty cool charts on lots of different data sources including Business Connectivity Services (BCS), Excel Workbooks (Even without Excel Services), SharePoint lists (from anywhere in the farm), Microsoft SQL, Oracle, any ODBC data source and via a web part connection (any other web part providing a table of data).
You don’t need to be an expert in business intelligence to create a nice looking chart either. The SharePoint 2016 Chart Web Part can be configured within just a few minutes, and all you need is at least Design Permission Level to the SharePoint Team Site, and some knowledge of the data source that you are connecting to.
The Lightning Tools SharePoint 2016 Chart Web Part is easy to install via a WSP, and then activated as a Site Collection Feature. You can add the SharePoint 2016 Chart Web Part to any web part page using the below steps:
1. Choose Page, Edit using the Page ribbon
2. Choose Insert, Web Part.
3. Select Lightning Tools Web Parts –> SharePoint Chart Web Part
4. Click Add.
4. To get started, select the drop down in the SharePoint 2016 Chart web part and choose Edit This Chart
Below, we will go through a few different scenarios for a few different data sources which will include different types of chart such as Bar, Pie, Stacked, and Multi-Series.
Our first Chart in this post is going to be a Multi Series Chart based upon an Excel workbook. The Excel workbook is stored within a standard SharePoint Document Library, and contains a simple Table showing profit and loss as per below:
We will build a simple multi-series chart on this table using the steps outlined below:
1. Select Microsoft Office Excel File from the Select Data Provider drop down box.
2. Either paste the URL to the Excel File or browse to the file using the Magnifying Glass icon
3. Click Load, and then set the Sheet name and Named Range/Sheet Range.
4. Click Connect.
5. Click Next.
6. On the Select Fields page, select all of the columns that you wish to include.
7. After Clicking Next, you will be able to filter your data (optional)
8. In this example, I am going to skip the Group Data and Top ‘N’ Records, but we will come back to them in another example below.
9. From the Chart Type section, select a Multi Series Chart that takes your fancy.
10. Click Next. Provide a Chart Title and have some fun playing with the cosmetics before clicking Finish.
11. Your SharePoint 2016 Chart will display on the page as below
Next, we will build a single series Chart based on an External Content Type using Business Connectivity Services.
If you need to build a SharePoint 2016 Chart on an external data source such as SQL, or Oracle, BCS is a good way to go since not only could you use the external data within your chart but also within SharePoint External lists, and other BCS features.
In order to build the External Content Type, I used BCS Meta Man which is a tool that Lightning Tools offers to build BCS External Content Types for over 30 external data sources.
The Chart we will build in this post is based on the Orders table from the Northwind Sample Database.
1. After starting the SharePoint Chart wizard, select Business Data Connectivity as the Data Provider.
2. Select the External System that you wish to work with and the Entity.
3. On the Select Fields section, select the columns that you wish to base your chart upon. In my example, I am going to display the Order ID which is a primary key, and the Order Date. Since every order has to have an order ID, am going to group by the Data, and count the orders by performing the count aggregation function on the Order ID column.
4. On the Grouping section, Group by the Order Date Column, and set the grouping level to Quarter.
5. Set the Order ID to Count in Step 3 of the Grouping section.
6. Set the Chart Type to a 3D column or something that you prefer:
7. Set the Drill Down option in the Drill Down Section to ‘Show a new chart with a subset of data, when a data point is clicked’
8. Click Finish and try out the drill down by clicking a bar within the chart.
There are multiple other types of SharePoint 2016 Charts that you can display with other data sources. The SharePoint 2016 Chart Web Part is available to download now for SharePoint 2016, SharePoint 2013, SharePoint 2010 and SharePoint 2007.
Download the Chart Web Part from here: https://lightningtools.com/products/sharepoint-chart-web-part/
See the Chart Web Part in action using our tutorials: https://lightningtools.com/lightning-tools-tutorials/sharepoint-chart-tutorials/
