Connecting to SharePoint Lists and Excel Services
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.
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.
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.
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.
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.