Configure SSRS in SharePoint 2010

Here are the required steps for configuring SQL Server Reporting Services in SharePoint 2010 integrated mode.

My Test Environment : Single Server For SharePoint And SQL/SSRS Server , e.g. Server Name - GJ-PC

Prerequisites : Make sure you installed Reporting Service feature during SQL Server 2008 R2 Installation. Else add Reporting Services features to your existing SQL installation.

• SQL/Report Server Configuration : 
  • Open Reporting Services Configuration Manager: Start->All Programs->Microsoft SQL Server 2008 R2 ->Configuration Tools ->Reporting Service Configuration Manager
  • Enter SSRS instance details, Service Account Details and click on Connect.[Required in case if SSRS service instance is not started].
  • Confirm that "Server Status" tab will show the status of SSRS , start the service if it is not started
  • Open "Web Service URL" tab: Configure report server web service virtual directory.Enter the details , e.g.
    • Virtual Directory : ReportServer
    • IP Address: All Assigned (Recommended)
    • TCP Port : 1110
    • Apply setting , 
    • It will create a new web service at http://GJ-PC:1110/ReportServer
  • Open "Database" tab :  
    • Click on change database link
    • Select "Create a new report server database". Click next
    • Enter the details e.g. Server name : GJ-PC , authentication: Current User - Integrated Security
    • Click on Test Connection link. If succeeded then Click next
    • Enter the details e.g. Database Name:Report Server , Report server Mode: SharePoint Integrated Mode
    • Click Next
    • Specify the credentials that the report server will use to connect to the database.You can use Service Credentials.
  • That’s all in SSRS configuration. In next step we will Configure SharePoint to use SSRS.
  •  
• SharePoint Central Admin Configuration:
  • Open SharePoint 2010 Central Administration Site
  • Click on "General Application Settings", then select "Reporting Services Integration" in "Reporting Services" section.
    • Note : Report Section is not visible on General Application Settings then please install Reporting Services Add-in for SharePoint Technologies: http://www.microsoft.com/en-us/download/confirmation.aspx?id=622
  • Enter the following details on "Reporting Services Integration" form :
    • Report Server Web Service URL : http://GJ-PC:1110/ReportServer
    • Authentication Mode - Windows Authentication
    • Credential - Domain\User , password
    • Activate the Reporting Services Feature - Activate feature in all existing site collections or Activate feature in specified site collections.E.g. in my case i have activated it only for one site http://GJ-PC:8009
    • Once done, you will see the Activation State message.
  • Open SharePoint 2010 Central Administration -> General Application Settings -> "Add a Report Server to the Integration"
  • Specify the server and instance name of the report server database
• Verify SSRS and SharePoint Integration
  • Open your SharePoint 2010 site collection
  • Go to Site Settings -> Site Collection Features.
  • Check if the Report Server Integration Feature is Active, if not just click activate.
  • Now upload any sample ssrs report file in SharePoint document library.
  • And open this report. It should show the report data
  •  
• Create a sample SSRS report to show SharePoint List data into Report and publish it on SharePoint Library-
  • Open SQL Server Business Intelligence Development Studio from:  Start -> "All Programs" -> "Microsoft SQL Server 2008 R2" - SQL Server Business Intelligence Development Studio
  • Create New SSRS Project in Visual Studio : File -> New -> Project...
  • Select “Report Server project” template and create new project.
  • In solution explorer , Right click Reports folder and select Add New Report
  • Click Next to continue
  • Select New data source
    • Name - SPListDataSource
    • Type-Microsoft SharePoint List
    • Connection String - Local SharePoint - http://GJ-PC:8009
    • Credentials - select use Windows Authentication (Integrated Security)
    • Next
  • Click Query Builder
    • Select List - e.g. "MyList"
    • Test Query by "Run Query"
    • Ok -> Next
  • Select Tabular then click Next
  • Drag drop field in detail section , e.g Title, ID
  • Select Style then click Next
  • Enter Report name, Finish
  • Publish Report : 
    • Build Project - Right Click Project name - Build
    • Right Click Project name - Property
    • Set Target Report Folder -  Parent document library for report e.g. http://GJ-PC:8009/SSRS
    • Target Server URL - Site Url e.g. http://GJ-PC:8009/
    • Deploy - Right Click Project name - Deploy
  • Open your document library -  http://GJ-PC:8009/SSRS and check the report.