Now that the Data Viewer is installed, we can provide a good overview of the Data Viewer product within SharePoint Online and Microsoft Teams. We’ll configure the Data Viewer to display external data within a grid view using data bars and conditional formatting, use the web part connections between multiple instances of the Data Viewer, and build multiple series charts based upon an Excel Workbook.
Configuring the Data Viewer within Microsoft SharePoint Online
The Lightning Tools Data Viewer Client Side Web Part is a modern web part that can be used within SharePoint Online or Microsoft Teams tabs to display grids of data or charts from SharePoint lists or external data sources such as Excel, SQL Azure databases, OData Services, Business Connectivity Services or other Web Parts using web part connections.
In this walkthrough, we’ll initially connect to some OData services and display supplier and product information within two separate instances of the Data Viewer. Using web part connections, we will join the web parts together, which will allow you to select a supplier and view the products that the supplier supplies. Following that, we’ll configure a new instance of the Data Viewer to display a Microsoft Excel workbook as a dynamic chart within SharePoint Online and Microsoft Teams.
Displaying Data within SharePoint Online
Once you have added the Data Viewer to a SharePoint page, follow the below steps:
-
Click Configure to open the Configuration Dialog.
- The configuration dialog box will open. You will see four tabs on the left hand side; Web Part, Data Source, Columns, and Display. The tabs are explained below:
- Web Part – The Web Part tab is the initial tab where you can select the type of data that you will connect to and whether you want to display it as a Grid View or a Chart View.
- Data Source – The Data Source tab is where you will configure the connection to the data source. The page will vary depending on the data source type you are connecting to.
- Columns Tab – The columns tab enable you to select the columns that you want to display in the grid or make available to the chart. You can also create calculated columns or apply persistent filters on the columns tab.
-
Display—The Display tab allows you to refine the display, such as data formatting, colour formatting, pagination options, or configure the display of your chart.
- On the web part tab, under Data Source Provider, select OData Service.
- Leave the default values for the Display Provider, Limit Number of Items, and Data Refresh Interval.
- On the Data Source Tab, copy and paste the following service URL into the OData Service URL field. https://services.odata.org/V3/Northwind/Northwind.svc/
- Click Load Entities to retrieve the list of Tables.
- Select “Suppliers” from the list of available entities.
- Click the Columns tab, and select all of the columns by clicking the column selector above the column names as shown below:
Note: On the columns tab, you can add calculated columns and filters. We will apply filters and calculated columns to the next Data Viewer instance that we will add shortly.
- On the Display Tab, you’ll see that you can apply data formatting, column alignment, grouping, colours and formatting. Note that clicking the ellipses next to the columns will bring further attributes. You can also refine options at the bottom of the dialog such as pagination, column header display. Turn on Allow Selecting Rows and then choose Save.
- You will now see you data returned from the OData Service. Next, we will add another instance of the web part.
- Add Another instance of the Data Viewer by clicking the + and choosing Data Viewer as shown below.
- On the Web Part Tab, select OData Service again as the Data Source.
- On the Data Source Tab, paste the same OData Service URL as you did in Step 5, and click Load Entities.
- Select Products.
- On the columns tab, select all columns.
- Click the Filter Icon for the SupplierID column.
- Click the connect icon as shown below to obtain the filter value from the first Data Viewer instance.
- Select Lightning Data Viewer as the Data Source, Selected Items, and SupplierID as shown below.
- Click Save.
- On the Display Tab, set the Unit Price format to currency
- Click Save.
- Test your connection by selecting a supplier from the Suppliers Data Viewer instance. You should then see the products that the selected suppliers supply.
Next, we will add some conditional formatting and data bards to the Products list.
- Within the Products list, select the dropdown on the Units in Stock column and choose Column Settings, Formatting.
- Click the trash can icon to remove the condition on the conditional formatting dialog.
- Click Data bar, and set the maximum value to 150.
- Set the Show Value to hide Value as shown below.
- Click Save.
- Click the drop-down for the Discontinued column, and choose Column Settings, Formatting.
- Set the condition to be Discontinued = False
- Add a new conditional style, and set the condition to be Discontinued = True.
-
As shown below, select a red icon for the first condition and a green icon for the second condition.
The Conditional Formatting will now be applied to the Products list so that you can clearly see the products are are discontinued. Next, we will use the Data View to build a chart based upon an Excel Workbook.
Build a Multi Series Chart within the Data Viewer
In this walkthrough, we’ll create a simple Excel Workbook within the SharePoint site document library and then configure a dynamic chart based on it.
- Navigate to the Document Library within the current site that you are working in.
- Create a New Excel Document within the Excel workbook.
- Create the following Excel content
-
Highlight your range of data, and choose Format as Table.
- Ensure that “My table has headers” is checked and click ok.
- Ensure your Excel workbook is saved in the Document library.
- Add a new instance of the Data Viewer to your SharePoint page.
- Click Configure to launch the configuration dialog.
- In the Data Source Provider field, select Microsoft Excel.
- In the Display Provider field, select Charts view.
- On the Data Source tab, expand the current site and then documents and select the document library containing the Excel Workbook you created.
-
You will be prompted to select an Excel workbook, a sheet, and a range. The range will be detected automatically.
-
Select all four columns; Month, Sales Revenue, Cost of Sales, and Profit.
- Click the Display Tab.
- You will see a chart based on your data.
- Click the Cog icon next to Sales Revenue
- Choose Column as the Category Type, and select Theme Color for the colour of the columns.
- Click Save
- Click the Cog Icon next to Cost of Sales and change the colour to bright green.
- Click the Cog icon next to Profit, and change the category type to area, and the colour to turquoise.
- Click Save to complete your chart.
Using the Lightning Data Viewer in Microsoft Teams
The Lightning Tools Data Viewer can be enjoyed in Microsoft Teams Tabs as well as SharePoint Online. To add the Data Viewer to Microsoft Teams:
- Click Add a Tab in Microsoft Teams
- Select the Data Viewer
- Click Save
- Click Configure to get going! You can repeat some of the steps above but within Microsoft Teams.
Note: If the Data Viewer is not available, you may need to navigate to the SharePoint App Catalog, and choose Sync to Teams for the Data Viewer package.