Better late than never - A PG site for SSRS integration with SharePoint

I am aware and painfully so that a PG blog / site dedicated to SSRS integration with SharePoint has been long overdue. Hence, for my initial post I have compiled a short list of some unanswered questions I have seen from blogs / newgroups etc. on this subject. Feel free to send me your issues / thoughts on RS integration with SharePoint and I will try as best to answer / address them and post new blogs as and when required. I hope you find this helpful.

1. Is it possible to integrate SQL Server Reporting Services with WSS3 not MOSS ? Additionally, Is it possible to run Sharepoint Services and SQL Reporting Service on Windows 2003 X64?
   Yes it supports both WSS 3.0 and MOSS 2007. Additionally it is supported on x86 and x64. For details refer to Requirements for Running Reporting Services in SharePoint Integrated Mode

2. Does RS integrate with a SharePoint WFE using Forms or Basic Authentication?
   Yes, it does. Make sure you install Forms auth hotfix 939942 and the Basic auth hotfix 50001712 for the RS 2005 SP2 Add-in for SharePoint.

3. Does Reporting Services support Alternate Access Mappings (AAM)? If not, then what are the alternatives?
   Reporting services only supports access via the default zone in AAM. You will get an error along the lines  "The specified path refers to a SharePoint zone that is not supported" if you try to access a report from a non-efault zone. You can consider a workaround wherein multiple “Internal URLs” (Internet, intranet, etc.) for the same web application use a single “Public URL for Zone” all in the AAM default zone. Full AAM support is being considered for the next version post-SSRS 2008 is available with SSRS 2008 R2 SharePoint mode.

4. Is there support for Reporting against SharePoint lists?
   SSRS does not natively ships a SharePoint Lists data extension as yet in SSRS 2008 R2. For prior versions of SSRS, here are option that may want to consider:

   • Using the out-of-the XML data extension to read and wrtie to a SharePoint via the GetListItems and SetListItems methods of the SharePoint List web service. I have attached a TicTacToe.rdl report that I demo'd @ TechEd / TechReady. This report reads from and writes to a SharePoint list by the name oif TicTacToe.  
   • Other third-party solutions like Enesys RS data extension
   • Develop a custom solution such as this blog from Teun Duynstee.

5. How do I fix the "User cannot be found" error?
   This is a bug in wherein if the author /creator / publisher of a report is deleted from the site in which the report resides. Possible workarounds can be found in "User Cannot be found" RS Forum thread. We are working on releasing a hotfix #50003017 with the upcoming SQL 2005 SP2 CU to fix this issue.

6. Which authentication modes should I use - Windows Integrated or trusted account?
   Only use Windows Integrated for Kerberos enabled environments and in single-box deployment scenarios. For the rest use Trusted Account mode. In the scenario wherein you are having trouble setting-up Kerberos you should consider using Trusted account mode to at least setup and verify RS integration with SharePoint works. Once you have fixed your Kerberos issues then you can choose to switch to using Windows integrated. Note: in Trusted Account mode you cannot use “Windows Integrated” data source credentials (with the exception of SharePoint List extension in SSRS 2008 R2) and you will need to use “Stored Credentials” instead.

7. How do I fix the error message "The report server cannot access settings in the Sharepoint configuration database.  Most likely, the Windows Sharepoint Services object model is not installed..."?
   This is generally noticed in a multi-machine scenario when SharePoint WFE and RS are on separate machines. Make sure you install the minimum SharePoint install on the SSRS machine. For details refer to How to: Configure SharePoint Integration on Multiple Servers

8. Is Reporting Services Integration with SharePoint supported on SQL Server Express SKUs?
   No it is only supported on Standard and higher SQL SKUs. There are no plans to include it is Express SKUs. For the details refer to the SSRS feature matrix.

9. I am trying to get this working on a domain controller and the "grant database access" step always fails with "A new member could not be added to a local group because the member has the wrong account type"?
   Make sure your Report Server services accounts are domain accounts on a DC, else you will get this error when try to to add them to the local WSS_WPG group.

10. I have a MOSS 2007 site set up on "localhost" and RS 05 is running in native mode.How can I view the reports in sharepoint webparts on the site?

    • Use WSS 2.0 Report Explorer and Report Viewer web parts. These are supported in WSS 3.0 as well. 
    • Make sure you are Comparing Levels of Integration Across Versions of Reporting Services and SharePoint Technologies

11. How to publish to SharePoint document library via Report Designer? 
    Make sure you have SQL 2005 SP2 BIDS when deploying to SSRS 2005 SP2 and the following project properties set to absolute paths:
    TargetDataSourceFolder https://<SharePoint_site>/<SharePoint_Doclib>
    TargetReportFolder https://<SharePoint_site>/<SharePoint_Doclib>
    TargetServerURL: https://<SharePoint_site>
    If you have issues deploying to a SharePoint site that uses Forms Authentication mode then consider posting directly to the Report Server by changing TargetServerURL to:
    TargetServerURL: https://<server_name>:<port>/<ReportServer_VDIR>
    For details refer to How to: Publish a Report to a SharePoint Library from Report Designer 

12. What do I do when I get "User does not have permission to add feature to site collection" when installing the RS Add-in for SharePoint msi?
    Locate the installation log created by the RS Add-in msi in the Temp folder - <Drive>:\Documents and Settings\<user_name>\Local Settings\Temp\RS_SP_<number>.log file. Next you should be able to locate log entries along the lines:  

    Activating feature to root site collection: <sharepoint_site_collection>
    ******* User does not have permission to add feature to site collection: <sharepoint_site_collection>

    This means that the RS integration feature was installed however the feature may not be activated for the <sharepoint_site_collection>, because the user who ran the msi wasn't a site collection admin. In order to view the RS integration feature in the site you need the site colection administrator to activate the Report Server feature. 

13. When I try to access a report thru the MOSS Site I get an internal catalog exception near GetSystemPropertiesAction() but it works just fine when I access it directly from the report server URL?
    This is may happen when there are multiple Central Administration sites in a SharePoint Farm. Verify this by checking whether you see multiple URLs for “Central Administration” Alternate Access Mappings Collection under Central Administration -> Operations -> Alternate Access Mappings. If this is the case, then browse to the Report Server URL directly and determine the https://<server_name>/ in the SharePoint sites listed. Ensure the URL containing this <server_Name> is first in the list of URLs for the "Central Administration" group under Central Administration -> Operations -> Alternate Access Mappings. This issue has been fixed in SSRS 2008 RC0.

14. What are some newer whitepapers related with RS integration?
    SQL Server 2005 Integration with Microsoft SharePoint Products and Technologies Troubleshooting Integration with SQL Server 2005 and Microsoft SharePoint Technologies

TicTacToe.rdl

Comments

• Anonymous
  August 01, 2008
  PingBack from http://blog.a-foton.ru/2008/08/better-late-than-never-a-pg-site-for-ssrs-integration-with-sharepoint/

• Anonymous
  March 11, 2009
  Is the Alternate Access Mappings (AAM) limitation an issue in the SharePoint SSRS AddIn or a more fundamental limitation in the ReportService2006 Web Service API? In other words would a custom sharepoint integration hit the same issue or could it effectively provide full AAM support by querying the correct zone properties? Thanks, Brian

• Anonymous
  May 21, 2009
  The AAM limitation is because the Report Server in SharePoint integrated mode only supports / understands the AAM "Public URL for zone" belonging to the SharePoint Default Zone. If the intent of the custom SharePoint Integration is to enable reporting on non-Default SharePoint zones, then it would need to do the appropriate AAM mapping from other zones to the default zone before the request is forwarded to Report Server. thanx, Prash

• Anonymous
  August 10, 2009
  Thanks Prash for such a great summary. Many of the items discussed here have been discussed in great details in my upcoming books about SSRS integration with sharePoint. Here is the URL : http://www.amazon.com/gp/product/0470481897/ref=yml_dp Thanks Reza Alirezaei , MOSS 2007 MVP http://blogs.devhorizon.com/reza

• Anonymous
  August 19, 2011
  Here is one my attempt to resolve the issue to show report for all zones. praveenbattula.blogspot.com/.../customize-reportviewerwebpart-in-c-and.html