• Article

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 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 How to: Add and Verify a Data Connection or Data Source (Report Builder 3.0 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 3.0.

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.

Types of credentials include the following:

  • Current Windows user (also known as integrated security).

    • For a SharePoint technology that is configured to work with a report server in Trusted Acount mode, this option is not supported.

    • 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, this option is not supported.

  • Use a stored user name and password. This option only supports Windows integrated security.

  • Prompt the user for credentials. This option only supports Windows integrated security.

  • No credentials are required. To use this option, the unattended execution account must be configured on the report server. For more information, see Configuring the Unattended Execution Account in the Reporting Services documentation in on msdn.microsoft.com.

For more information, see Data Connections, Data Sources, and Connection Strings (SSRS), Specifying Credentials in Report Builder 3.0, 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 How to: Create a Shared Dataset or Embedded Dataset (Report Builder 3.0 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.

  • **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 3.0) 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 Filtering, Grouping, and Sorting Data (Report Builder 3.0 and SSRS) and Parameters (Report Builder 3.0 and SSRS).

  • **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 3.0) 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 Filtering, Grouping, and Sorting Data (Report Builder 3.0 and SSRS) and Parameters (Report Builder 3.0 and SSRS).

  • **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 3.0).

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.

Arrow icon used with Back to Top linkBack to Top

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.

Arrow icon used with Back to Top linkBack to Top