Looking back to the days of SharePoint 2007 through to SharePoint 2016, you’d have multiple options available to you when you needed to display external data in SharePoint. Back in SharePoint 2007, you could choose to use the Data View web part which is something that you could easily configure within SharePoint Designer to connect to external data such as SQL Databases, XML Files, or even SharePoint Lists. There was also the Business Data Catalog (BDC) in SharePoint 2007 which would allow you to configure a connection to external data sources such as SQL Server, Oracle, DB2, and much more. BDC went well beyond simply displaying data in a web part. There was a suite of web parts allowing for relational databases to be displayed in a one-to-many view as long as an association was created between entities. The external data could also be searched via SharePoint, and used in Lookup columns for document libraries. Of course, external data isn’t always relational databases, but could be data simply stored in Excel documents. Excel Services, introduced in Microsoft Office SharePoint Server 2007, provided dynamic web parts in SharePoint pages to display Excel Tables and Charts within your SharePoint Page. In SharePoint 2010 onwards, The Business Data Catalog became Business Connectivity Services (BCS). BCS had evolved over BDC, and now offered External Lists. The External Lists provided a list type view of your data, and allowed data to be updated or inserted right from the External List.
SharePoint Online has evolved tremendously over SharePoint On-Premises in many ways, but almost every option for connecting to external data has either been deprecated or just not shown any attention for many years. The Modern experience in SharePoint Online is a huge improvement over the classic experience especially when it comes to lists. However, The BCS External List is still classic, which is not a great experience for your organization’s users. Excel Services was retired, as was SharePoint Designer, which of course meant the Data View Web Part too.
In Microsoft 365, there are other ways to display data in various formats. Using the Power Platform, Power Apps, Power BI, Dataverse, and Power Automate all allow you to work with external data in some way. However, when there is a requirement to surface business data within your SharePoint pages, allowing visitors to view those pages to get quick updates on how your business is performing, the choices are limited. Power BI does offer SharePoint web parts, but sometimes it’s not just data visualizations that are required. It’s still necessary to be able to display external data in SharePoint Online with powerful data grids offering grouping, conditional formatting, data formatting, summary functions, and charts, while still maintaining ease of use, and a responsive, modern design. Furthermore, having the same options in Microsoft Teams (where users spend the majority of their time) is also a requirement.
The Lightning Tools Data Viewer was originally developed for SharePoint On-Premises. The requirement back then was to offer a web part to replace the Data View Web Part (DVWP) that had been deprecated along with SharePoint Designer. As we’ve evolved, the Data Viewer Web Part (now a modern SPFx Client Side Web Part) offers an easy way to configure Data Views and Charts, and display that External Data in SharePoint Online.
The Data Viewer SPFx Client Side Web Part for SharePoint Online & SharePoint 2019 provides Site Owners the ability to quickly and easily configure connections to external data. External Data types include:
- SQL Azure Relational Database
- OData
- Microsoft Excel Workbooks
- Business Connectivity Services External Content Types
- SharePoint Lists
- Other Web Parts
Once you’ve configured a connection to your data source, you can begin styling your data views or charts from within the same web part configuration panel. The screenshot below shows displaying external data from a relational database. There are three tables: Customers, Orders, and Order Details. The Data Viewer Web Part enables this relational data to be displayed on a Modern SharePoint page and allows navigation of the relational data. For example, when I select one or more customers, I’ll see the orders for those customers. When I select an Order, I will see the order details for that order. We could go on to see more data such as the Product information for the products that were on the order etc.
In addition to displaying the data in a grid view, the Data Viewer also offers a simple way to display charts and other visualizations that are dynamic, and can be refreshed as much as you like!
Below is a screenshot of a chart that you could create with the Data Viewer connected to the same database as the above screenshot.
To configure the Data Viewer to connect to External Data in SharePoint Online, you simply need to walk through a four-step display panel. The above examples took in total about two minutes to configure. So, let’s see how:
Step 1 – Decide what you’re connecting to, and how to display it
The first step of the configuration is to decide on what you are connecting to, and how you want to display the data. The Data Source Provider offers a choice of:
- SharePoint List – This option allows you to connect to any SharePoint list in your SharePoint Online tenant as long as you have permission to access it. The benefit is that lists such as ‘Contacts’ could be displayed on multiple sites via the Data Viewer. That way you can maintain one list and avoid duplicating the content.
- Business Connectivity Services – As we explained above, the BCS External List has a “classic” UI. Therefore, you can use the Modern, Responsive Data Viewer to connect to a BCS External Content Type and modernise BCS for your users!
- OData Service – OData offers a great way to connect to External Data that remains on-premises. Building an OData Service to surface the data is the first step. You can then consume that OData Service with the Data Viewer.
- SQL Azure – Connect to a SQL Azure Database to display tables and views from the relational database.
- Microsoft Excel – Connect to a Microsoft Excel Workbook and display the data in grid views or charts.
- Current Page Datasource – Connect to another web part such as the Lightning Conductor Web Part, allowing for aggregated content to be displayed in a chart.
The next question is whether you would like to display the data as a grid of data, or a chart.
- Grid View – The Grid View offers the ability to select columns, add grouping, specify column aliases, and configure data formatting, conditional formatting, and summary functions (all without any code).
- Chart View – Configure Pie, Bar, Column, Scatter, or Area Charts, or TreeMaps and Gauges.
Step 2 – Configure the Data Source
The Data Source tab will differ based on the Data Source Provider selected in Step 1. Note that the Data Source tab below is expecting a SQL Azure database. You can specify the Server, credentials, and select the Database/Table/View. Other Data Sources will offer a similar interface specific to the type of data source.
Step 3 – Select your columns
When you move to the third step, you’ll be able to select the columns that you wish to display. You can apply the column order using drag and drop, apply sorting on each column, add filters, and create calculated columns.
Step 4 – Configure your View
Depending on your choice of Display provider, you’ll either be configuring a grid view or a chart in Step 4.
If you opted for the Grid View, you’ll have the ability to set the data format, set column alignment, add grouping (to multiple levels), select colours and other formatting (either set in stone or conditional). Other properties allow you to set pagination, column header formatting, and enabling or disabling the CRUD (Create, Read, Update, Delete) operations.
If you opted for the Chart display provider, you’ll be able to select the type of chart, and then configure the chart series, grouping, aggregate functions, and chart formatting.
On the 26th January 2023, we’ll be offering a webinar where you can see how to configure the Data Viewer from start to finish for multiple types of data source. Register below to attend or receive the recording:
