Here is an impeccable guide on how to work with Reporting Services in SharePoint 2007.
The following are the steps I followed for my environment.
1. SQL, SharePoint and Reporting Services in SQL were already installed. Check with the aforementioned guide if your scenario differs.
2i. In the Reporting Services Configuration Manager (type this into the Windows Start menu) specify the name of the computer on which the report server instance is installed. The local computer appears by default, this is correct for my environment. Also, input the name of the DB server.
2ii. In the Report Server Database, click on Change Database. Create a new report server database, select the server name and provide authentication credentials. In the next screen, make sure you give a unique name to your new DB that will be used for the reports and select SharePoint Integrated Mode for the Report Server Mode.
2iii. Click on the Web Service URL (on the left) and enter a new unique name in the Virtual Directory.
2iv. Give a unique name to your Report Manager URL > Virtual Directory.
3. Download and install the Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies (x86/x64).
4. Activate the Report Server Integration feature in the Site Collection features of the Site Collection you aim to use reporting with. In case you don’t have a site collection, create one in your SharePoint Central Administration.
5. In your Central Administration, you should be seeing a new section titled “Reporting Services”. If you do not, you will need to activate the feature in the Site Collection features of your site collection, inside the Central Administration. To do so, first make sure that you are a site collection administrator. Do not skip this step even if you are a Farm Administrator. You will need to explicitly be a Site Collection Administrator. In the Central Administration, in the Application Management section, under the SharePoint Site Management, you will find the Site Collection Administrators. Enter and make sure you’re listed as one of the two administrators. If you’re already listed, remove yourself, do an iisreset and then come back and re-add yourself as a Site Collection Administrator. Once you’ve added yourself as a Site Collection Administrator, click on Site Actions > Site Settings > Site Collection Features. There, locate and activate the Report Server Integration Feature. You should now see the “Reporting Services” section in your Central Administration > Application Management.
6. In the Report Server Web Service URL, you need to enter the Report Server URL that you defined in step 2 in the Reporting Services Configuration Manager.
7. Just in case you can’t find the webparts after following the steps above, run this command in all your web front servers:
stsadm.exe –o addwppack –filename “C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab”
This should give you all the necessary web parts.
For more information, make sure to check the complete guide (link provided at the top of this article).
References:
1. How to: Configure Report Server Integration in SharePoint Central Administration
2. How to: Configure Report Manager
3. Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008
5. Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies
