Pre Req: You should have at least one report ready to publish. I have explained how to build a report by fetching data from a SharePoint list in this post.
Following are the configuration steps you need on SQL side as well as on SharePoint side:
- Launch Reporting Service Configuration Manager from Start menu.
- Go to Database Setup tab, change Server Mode to SharePoint Integrated. It will ask you to create a new SSRS database. Go ahead and create it.
- Go to SharePoint Integration tab and then launch SharePoint 2007 Central Admin.
- In SharePoint Central Admin, go to Applications -> Reporting Services section
- Under Reporting Services, select Manage integration settings and provide Reporting Server URL (http://ServerName
) and Authentication Type as Windows Authentication in dev box (Trusted Connection in production environment). /ReportServer
- Under Reporting Services, select Grant database access and provide Server Name (with farm login credentials)
- Go to the Site Collection features of SharePoint site collection where you want to publish/run the reports and activate Report Server Integration Feature feature.
- Create a Reporting Document Library and upload report file RDL and Datasource file RSDS in it. This is the document library where we will store our reports and datasources.
You can also publish the reports and data source directly from BI Studio by these steps:
- Create a new blank web part page and add SQL Server Reporting Services Report Viewer web part on it. Then configure the web part properties to point to the RDL file in the document library.
- Save and publish the page and you should see the report in the Report Viewer web part...BOOM.