One of the capabilities of SQL Server is the ability to create and publish rich reports on your data. One of the capabilities of SharePoint Server is the ability to store, manage and provide a portal to documents. Those documents could be your reports. So you have one system letting you create reports and the other letting you share them afterwards, delivering your data into the hands of the users who need it. In theory, this should mean that your users should be able to find the data they need in existing reports, be sure they’re looking at the most recent information and not request new reports when the data already exists in others. I say in theory because I have less faith in users doing this than I have in the capabilities of the software to deliver this functionality.
In order to get to this situation, you need to do some configuration on both sides. This article on TechNet describes the steps clearly to get the initial configuration working. One of the main steps is that you need to install the Reporting Services add-in for SharePoint 2010, which includes the admin screens for Reporting Services inside SharePoint.
Once you’ve done your configuration, you can give people the ability to view reports through SharePoint. This is done using the SQL Server Reporting Services Report Viewer web part – quite a mouthful! This allows you to point to a specific report on your SQL Server and display it through the browser.
To add this web part, go into edit mode in SharePoint, by going to the Page tab of the ribbon in the SharePoint site in question and clicking the Edit button. As with adding any other web part, you can go to the Insert tab that appears in the ribbon and clicking on the Web Part button. When the integrated mode is set up, the Miscellaneous folder in the menu includes the option for the Report Viewer web part. This inserts the web part, but it starts off blank. So do you connect it to a specific report?
You configure this by opening the tool pane. In the tool pane, you have the option to browse through SharePoint libraries to find reports that have been published. So now you can display SSRS reports in a SharePoint page, perhaps showing team reports in a team site or as part of a dashboard alongside other BI elements.
So now you can combine the rich power of Report Builder Reports with all the other capabilities that SharePoint provides.
Configuration article – http://technet.microsoft.com/en-us/library/bb326356.aspx
Jess is a partner technology advisor specialising in SharePoint an BI working for Microsoft in the UK