Different data is returned when you use SharePoint List data extension for a report in SSRS 2008 R2 or SSRS 2012

Article translations Article translations
Article ID: 2826162 - View products that this article applies to.
Expand all | Collapse all

Symptoms

Consider the following scenario:

  • You have a Microsoft SQL Server Reporting Services 2008 R2 (SSRS 2008 R2) or Microsoft SQL Server Reporting Services 2012 (SSRS 2012) report for a Microsoft SharePoint list or Document Library.
  • The report uses a built-in SQL Server Reporting Services SharePoint List data extension.
  • You build a dataset query that is based on the SharePoint list or Document Library, and then you run the query in the report designer.
  • You deploy the report to the SharePoint farm that hosts the SharePoint list or Document Library.
In this situation, the dataset query returns a different result in the report designer than in the SharePoint farm.

For example, you create a new view in SharePoint in which you select the Show all items without folders view option in a Document Library. You set the new view as the default view for the document library. In this situation, the report returns different data in the SharePoint farm than in the report designer.

Note If you deploy the report to the SharePoint site, only the top level folders and files of the Document Library are returned. However, the report designer returns all files that reflect the default view change regardless of folder locations.

Cause

This issue occurs because of different code paths that are taken by SSRS 2008 R2 or SSRS 2012 when SharePoint runs the report in the same farm as the list. When the local SharePoint farm runs the report, the list is retrieved by using the SPLocalConnection path. The path reads directly from the SharePoint list without SOAP calls and builds a local copy of the list information without the default view configuration. Therefore, the SPLocalConnection path does not reflect changes in the default view. 

However, when the report designer runs the report, the report uses the SPRemoteConnection path. This path uses SOAP calls that resemble the GetListCollection function. This function accesses the site through a remote HTTP connection instead of the local connection. Therefore, changes will be reflected in the default view configuration.

Workaround

To work around this issue, use one of the following methods:
  • Deploy the report to a native mode report server, and then run the report from the native mode report server. 

    Note
    If you have to view the report in SharePoint, install the native mode Report Viewer Web Part in SharePoint, and then call the report from your native instance of Reporting Services.

    For more information about how to view and explore native mode reports by using SharePoint Web Parts, go to the following MSDN website:
    How to view and explore native mode reports by using SharePoint web parts
  • Deploy the SharePoint List or Document Library to a different SharePoint farm that can be accessed by the farm that you have the report deployed to. Therefore, the report in the local farm can access the list remotely.
  • Use an XML data extension to build an XML query without using the SharePoint List data extension.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Properties

Article ID: 2826162 - Last Review: March 19, 2013 - Revision: 1.0
Applies to
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Service Pack 1
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Reporting Services
  • Microsoft SQL Server 2008 R2 Service Pack 2
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
Keywords: 
kbsurveynew kbtshoot kbexpertiseadvanced KB2826162

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com