Export (0) Print
Expand All
1 out of 6 rated this helpful - Rate this topic

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).

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 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.

Supported credential options:

  • Windows Authentication (Use current Windows user) also known as integrated security

    • For a SharePoint technology that is configured to work with a report server in Trusted Account 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 (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).

  • Prompt for credentials: To use this option with SharePoint, you must select the “Use as Windows Credentials” option.

  • Stored Credentials (Use this user name and password): To use this option with SharePoint, you must select the “Use as Windows Credentials” option.

  • 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 more information, see Data Connections, Data Sources, and Connection Strings (SSRS), Specify Credentials in Report Builder, and Data Sources Supported by Reporting Services (SSRS).

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.

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

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 are not supported by this data extension.

Arrow icon used with Back to Top link Back 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.

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.

Arrow icon used with Back to Top link Back to Top

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.