Connecting to SharePoint Lists and Excel Services

Contents[Hide]

1. Overview

This article shows you how to create a data connector for SharePoint and Microsoft 365 Lists and for SharePoint Excel Services.

2. Connect to a SharePoint List

When creating a new data connector, set the Data Provider dropdown to the SharePoint List option that corresponds with your SharePoint site as follows.

2.1. SharePoint Lists

Choose SharePoint Lists for an on-premise SharePoint server or when using basic authentication in SharePoint Online.

Choose an authentication method. You will typically need to use specified or impersonated Windows credentials, or for SharePoint Online use Online credentials (this account should not use multi-factor authentication, or you can use the SharePoint Online List provider instead).

Next, set the SharePoint Site URL.

SharePoint List settings
SharePoint List settings

2.2. SharePoint Online List

Set Data Provider to SharePoint Online List in Dundas BI version 23.2 or higher to use modern authentication options with SharePoint Online. This requires setup by an Office 365 admin user:

You can also follow a more detailed walkthrough that includes examples for creating a self-signed certificate and registering it along with SharePoint API permissions as part of Granting access via Azure AD App-Only in the SharePoint documentation.

In the Dundas BI data connector, enter the Application ID and Directory ID which can be found on the overview page of the Azure AD App, then under Certificate upload the X.509 certificate and enter the accompanying Certificate Password used when creating it. Enter the SharePoint URL for the site you want to access lists from.

3. Connect to SharePoint Excel Services

Excel Services is a service application that enables you to load and display live Microsoft Excel workbook data from on-premise SharePoint servers.

Tip
To connect to live Excel data from SharePoint Online, consider syncing those files to a folder or other location accessible to the Dundas BI server so you can use the Microsoft Excel data provider.

The following walkthrough shows you how to connect to an Excel file in SharePoint using the Excel Services provider. If you are not sure whether Excel Services is enabled on your SharePoint server, contact your SharePoint administrator.

First, create a new data connector from the main menu, then set a Name for it and set the Data Provider dropdown to SharePoint Excel Services.

Choose an authentication method. You will typically need to use Specified Windows credentials, or for SharePoint Online (e.g., Office 365) use Online credentials, and then enter the credentials.

Next, set the SharePoint Site URL and Excel File Name.

SharePoint Excel Services settings
SharePoint Excel Services settings

Click the submit button at the bottom of the dialog to begin discovering the available data. If you encounter a connection error such as We noticed that you haven't been interacting with this workbook, so we paused your session try selecting the Relay Credentials option in your settings.

Now you can access the data in a new metric set or in a view such as a dashboard.

Locate and expand the data connector in the Explore window to see the discovered sheets and drag one to the canvas to see its data.

View Excel data
View Excel data

4. Define structure

You may want to define, or modify, the data structure for the created data connector. For example, you can change the data type of a column.

When editing the data connector, click Define structure. The steps are similar to connecting to a regular Excel file.

5. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri