SQL Reporting Services 2005 for SharePoint

We can use SQL Server Reporting Services 2005(SSRS) in SharePoint environment. SSRS can utilize SharePoint features such as alert, version control on reports, permission and authentication. User can use SSRS report viewer web part provided by SSRS add-in for SharePoint to view the reports.
There are some prerequisites needs to be install before to integrate SSRS into SharePoint.

• Installation
  
  • SQL Server (SSRS) service pack 2
  • Reporting Services Add-in for SharePoint Technologies: you can download it from SSRS Add-in


• SSRS Configuration
  After installing all the prerequisites, we will now configure SSRS to integrate into SharePoint.
  
  • Open Reporting Services Configuration Manager: Start->All Programs -> Microsoft Sql Server 2005 ->Configuration Tool ->Reporting Service Configuration
  • Enter SSRS instance details and click on Connect
  • Server Status tab will show the status of SSRS , start the service if it is not started
    
    
  
  
  • Report Server Virtual Directory tab: In default installation SQL set up creates virtual directory for SSRS on port 80 , it is better to create a new virtual directory and application pool on different port if you already have SharePoint web Application on port 80 . In our case report server url is http://SSRS/ReportServer1
    
    
  
  
  • Report Manager Virtual directory : it is not require in Sharepoint integrated mode
  • Enter the account details for Windows and web Service access in Windows/Web service identity tab
  • Database Setup tab – Create a report server database in SharePoint integrated mode.
    This sets server properties on the report server and activates a new SOAP report server management endpoint that is used for integrated operations.e.g. I have created a new SSRS data base “ReportServer1” on “Server-test” server in Sharepoint integrated mode.
    
    That’s all in SSRS configuration. In next step we will Configure SharePoint to use SSRS.
    


• Sharepoint Configuration
  In order to use SSRS in Sharepoint environment , we need to do some configuration in sharepoint Center admin.
  
  • Go to central administration, under application management, go to the section "Reporting Services", and click on the first link "Configure Reporting Services Integration".In report server web service URL, type your SSRS server url http://SSRS/reportserver1, select Windows Authentication mode in Authentication mode, and click OK button
    
    
  
  
  • Click on "Grant Database Access" to grant Report Server account access to SharePoint databases. Specify the DB details, hit OK, and a dialog box should pop up as shown below:.
    
    Enter the user id/password, and hit OK.
    
  • Next, go to Set Server Defaults in Reporting Services section, and click OK to accept default setting.
  • Restart your SSRS. Now integration SSRS to WSS completes


• Report Deployment
  
  • Now Provision a website, Create a site collection
  • Go to Site collection features, and activate the "Report Server Integration Feature".
  • Create Three document libraries
    a) Reports <-- this is where we will store our reports. b) Data Connections <-- this is where we will store our data connections. c) ReportPages - > this is where we will store our web part page for report
    
  
  
  • Open Visual Studio 2005 and create a new project of type “Business Intelligence Projects” and select “Report Server Project” template
    
    
  
  
  • Add new Shared Data Source and Report into Corresponding folder
    
    
  
  
  • Create Dataset and report layout for your report and test the report using preview pane
  • Right Click on Project name in Solution explorer and click on Properties.
    Enter Target details for deployment on sharepoint site
    TargetDataSourceFolder - url of Data Connections document library
    TargetReportFolder - url of Reports document library
    TargetServerURL – URL of site collection
    
    
  
  
  • Now your project is ready to deploy , Click on Build->Deploy “Projectname”
  • Visual studio will copy your report and data connection into respective document library
  • You can view this report by directly clicking on report name or you can display it in a web part
  • Create a new webpart page in Report Pages Document library.
  • Add a reporting service viwer web part on your page
    
    
  
  
  • Click on Modified shared web part and set the report path
  • Now report will be visible on your web part page.
  
  Enjoy reporting………………
  
  
  
  
-----