SharePoint List Connection Type (SSRS)

To include data from a Microsoft SharePoint list in your report, you must add or create a dataset that is based on a report data source of type Microsoft SharePoint List. This is a built-in data source type based on the Microsoft SQL Server Reporting Services SharePoint List data extension. Use this data source type to connect to and retrieve list data from SharePoint 2013, SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007 sites.

Use the information in this topic to build a data source. For step-by-step instructions, see Add and Verify a Data Connection or Data Source (Report Builder and SSRS).

In This Article

Connection String

Queries

Parameters

How-To Topics

Related Sections

Connection String

The connection string to a SharePoint list is the URL to the SharePoint site or subsite, for example, http://MySharePointWeb/MySharePointSite or http://MySharePointWeb/MySharePointSite/Subsite.

The query designer automatically displays the SharePoint lists that you have sufficient permissions to access.

For more connection string examples, see Data Connections, Data Sources, and Connection Strings in Report Builder.

Credentials

Credentials are required to run queries, to preview the report locally, and to preview the report from the report server. After you publish your report, you may need to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid. The types of credentials that can be used with this data extension depend on the SharePoint technology configuration for the SharePoint list that you are using as a data source.

The following tables outline credential retrieval behavior for the SharePoint list extension, when connecting to a local farm SharePoint list and to a remote SharePoint list.

Table 1 is for reports deployed to a legacy Windows SharePoint Site. A legacy Windows site supports only Kerberos, NTLM, and Forms Based Authentication (FBA). Table 2 is for reports deployed to a Claims-based SharePoint site.

Table 1

Table 2

1 Stored and prompt credentials with non-Windows credentials is not supported.

2 Forms-based authentication and Claims authentication are not supported for remote SharePoint lists.

3 Windows authentication, Forms Based authentication (FBA), Secure Application Markup Language (SAML) tokens, other identity providers or a combination of more than one of the above mentioned authentication providers.

-
Windows Authentication
For a SharePoint technology that is configured to work with a report server in Trusted Account mode, this option is not supported. This applies only to earlier releases of Reporting Services.

For a SharePoint technology that is configured to work with a report server in Windows Integrated mode, this option applies to both the current Windows user and the current SharePoint user.

For a SharePoint technology that is configured to work without a Report Server (local mode), this option is not supported. For more information on local mode, see [Local Mode vs. Connected Mode Reports in the Report Viewer (Reporting Services in SharePoint Mode)](ff487969\(v=sql.110\).md).

• Credentials are not required (Do not use credentials):
  To use this option, the unattended execution account must be configured on the report server. For more information, see Configure the Unattended Execution Account.

For information about Claims authentication support across the Microsoft BI stack, see Using Claims Authentication across the Microsoft BI Stack.

For more information, see Data Connections, Data Sources, and Connection Strings (SSRS), Specify Credentials in Report Builder, and Data Sources Supported by Reporting Services (SSRS).

Queries

To design a query, create a new dataset based on the data source, and then open the associated query designer. For more information, see Create a Shared Dataset or Embedded Dataset (Report Builder and SSRS).

The SharePoint List graphical query designer displays four panes:

SharePoint Lists Displays a list of all the SharePoint lists on the site for this data source. Select a list and then select the fields that you want in your query. The names of fields in this pane are the SharePoint friendly names, also known as display names. Hover over an item to display the following properties in the tooltip:

• **Name   **The unique name of the field.

• **Identifier   **The unique identifier of the field.

• **Field Type   **The data type of the field.

• **Hidden   **Whether the field displays in the SharePoint list view.

Selecting fields from multiple lists is not supported. You can create a dataset for each list and select fields from each dataset. If the lists have a common field, you can use the Lookup function in a tablix data region that is bound to one dataset to retrieve a value from the other dataset that is not bound to the data region. For more information, see Lookup Function (Report Builder and SSRS).

• **Selected Fields  ** Displays the fields that you have selected. The names of fields in this pane are friendly names that a SharePoint user has specified. When you close the query designer, you see these names in the dataset field collection in the Report Data pane. The relationship between unique names and friendly names is available in the Dataset Properties Dialog Box, Fields (Report Builder) page.

• **Applied Filters  ** Limits the data that is returned from the SharePoint list, before the data is returned to the report. Select the field name, operator, and value to use to limit the data that is retrieved in the list. The operators vary depending on the data type of the value that you select.

  You cannot change the sort order or specify groups in the graphical query designer. To do that, set sort expressions on the report dataset, and group expressions on the data regions in the report. Query parameters are not supported. To filter data in the report, use report filters or report parameters that you create. For more information, see Filter, Group, and Sort Data (Report Builder and SSRS) and Report Parameters (Report Builder and Report Designer).

• **Query Results  ** Displays example rows that are returned when the query runs. If the SharePoint list values change frequently on the SharePoint site, the values that you see in the query results pane might differ from the values that you see in the report.

• **Selected Fields  ** Displays the fields that you have selected. The names of fields in this pane are friendly names that a SharePoint user has specified. When you close the query designer, you see these names in the dataset field collection in the Report Data pane. The relationship between unique names and friendly names is available in the Dataset Properties Dialog Box, Fields (Report Builder) page.

• **Applied Filters  ** Limits the data that is returned from the SharePoint list, before the data is returned to the report. Select the field name, operator, and value to use to limit the data that is retrieved in the list. The operators vary depending on the data type of the value that you select.

  You cannot change the sort order or specify groups in the graphical query designer. To do that, set sort expressions on the report dataset, and group expressions on the data regions in the report. Query parameters are not supported. To filter data in the report, use report filters or report parameters that you create. For more information, see Filter, Group, and Sort Data (Report Builder and SSRS) and Report Parameters (Report Builder and Report Designer).

• **Query Results  ** Displays example rows that are returned when the query runs. If the SharePoint list values change frequently on the SharePoint site, the values that you see in the query results pane might differ from the values that you see in the report.

For more information, see SharePoint List Query Designer (Report Builder).

Query Text

To view the query that is generated by the graphical query designer, switch to the text-based query designer. In this view, you can see the XML that is created by the graphical query designer. The XML includes elements for the list name, the field collection, and the filter.

Example 1. Specified fields for a list

The following example shows a well-formed SharePoint query:

<RSSharePointList>
<listName>MyList</listName>
<viewFields>
  <FieldRef Name="Field1"/>
  <FieldRef Name="Field4"/>
</viewFields>
<Query>
  <Where>
    <And>
      <Gt>
        <FieldRef Name="Field1"/>
        <Value Type="Integer">1</Value>
      </Gt>
      <IsNotNull>
        <FieldRef Name="Field2"/>
        <Value Type="string"/>
      </IsNotNull> 
    </And>
  </Where>
</Query>
</RSSharePointList>

You can edit this view of the query as long as it remains well-formed XML text.

Example 2. All fields for a list

You can also specify only the name of a list, and all fields, including hidden fields, are returned. The following example retrieves all the fields from a list that is named Tasks:

<RSSharePointList>
<listName>Tasks</listName>
</RSSharePointList>

All fields for the list Tasks are returned in the query results.

Parameters

Parameters are not supported by this data extension.

Back to Top

Troubleshoot

Different data is returned when you use SharePoint List data extension for a report in SSRS 2008 R2 or SSRS 2012(https://support.microsoft.com/kb/2826162)

How-To Topics

This section contains step-by-step instructions for working with data connections, data sources, and datasets.

Add and Verify a Data Connection or Data Source (Report Builder and SSRS)

Create a Shared Dataset or Embedded Dataset (Report Builder and SSRS)

Add a Filter to a Dataset (Report Builder and SSRS)

Back to Top

Related Content

These sections of the documentation provide in-depth conceptual information about report data, as well as procedural information about how to define, customize, and use parts of a report that are related to data.

• Add Data to a Report (Report Builder and SSRS)
  Provides an overview of accessing data for your report.

• Data Connections, Data Sources, and Connection Strings in Report Builder
  Provides information about data connections and data sources.

• Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)
  Provides information about embedded and shared datasets.

• Dataset Fields Collection (Report Builder and SSRS)
  Provides information about the dataset field collection generated by the query.

• Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.
  Provides in-depth information about platform and version support for each data extension.

Back to Top

See Also

Concepts

Report Parameters (Report Builder and Report Designer)

Filter, Group, and Sort Data (Report Builder and SSRS)

Expressions (Report Builder and SSRS)