We can easily integrate SharePoint 2010 and SQL Server 2010 R2 Reporting Services. We simply need to do some configuration tasks on the report and SharePoint servers and we could already enjoy publishing and managing reports in SharePoint.
This paper will show us the basic configuration, report creation, and publication steps.
1. We will start by installing Microsoft SQL Server 2008 R2 Reporting Services in our report server.
2. Using the Reporting Services Configuration Manager, we will configure our the Reporting Services in SharePoint Integrated Mode.
3. Now let us open the General Application Settings in our Central Administration.
4. If the Reporting Services component is not available, it means that it was not installed as an optional software during our SharePoint installation process. Not a problem! Simply download and install the add-in from the Download Center.
5. Now, let us go back to the General Application Settings in our Central Administration and open the Reporting Services Integration under the Reporting Services section. Let us make sure that the Report Server Web Service URL, Authentication Mode, and Credentials are properly configured.
If necessary, we could also make use of the Add a Report Server to the Integration and Set Server Defaults facilities.
6. Next, we will open our SharePoint site, create a Document Library, and add the Report Builder Report and Report Data Source content types (notwithstanding that we will not be using the Report Data Source in this piece). This is where we are going to publish our report.
7. At this point, we will create a report.
8. In the Report Builder, we will choose the Table or Matrix Wizard.
9. Then, we will complete the Data Source Properties selecting Microsoft SharePoint List as the connection type, <SharePoint Site URL> for the connection string, and Windows User for the credential.
10. In the Design a Query part of the wizard, for our purpose, we will choose the Title and Created columns of the Announcements list.
11. And finally, we will save our report in the document library we have created in the early step.
12. And the we go, from the same document library, we could now open our report.
13. Additionally, we could edit our report using the same Report Builder tool.
Or we could create the same report using the SQL Server Business Intelligence Development Studio and publish it our SharePoint site.
Thank you very much!