I had a number of reports configured in SSRS to output data from our SharePoint server. They worked fine but with the following issues.
- When browsing the report if the user navigated away from the page and then clicked the back button all the SharePoint data within the report would display an error
- If the user exported the report to Excel all the SharePoint data would be replaced by an error.
The reports in question all had something in common; none of them had a table or matrix within the report to display the SharePoint Dataset (via SOAP query). In each case I was using the data for calculations within tables linked to other datasets (SQL connections).
As soon as I added a hidden table to directly display the SharePoint data the problem went away.
It would seem therefore that unlike SQL connections which ‘just work’, SOAP datasets are only loaded when the report is first displayed, unless you have a table or matrix that directly references it. Hence why it wasn’t loaded the data on the page back or the Excel Export.
My Setup: Visual Studio 2010 publishing reports to SQL Server Reporting Services 2008 R2.