Connecting to SharePoint Lists and Excel Services

Contents[Hide]

1. Overview

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

2. Connect to a SharePoint List

Create a new data connector from the main menu and select a Name for it.

Set the Data Provider dropdown to SharePoint Lists.

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.

SharePoint List settings
SharePoint List settings

Click the submit button at the bottom of the dialog to begin the initial discovery of available lists.

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 available lists and drag one over to the canvas to see its data.

View a SharePoint list
View a SharePoint list

3. Connect to SharePoint Excel Services

Excel Services is a service application that enables you to load and display Microsoft Excel workbooks in SharePoint.

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.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

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