BI ABuilder to create reports using PowerPivot workbooks that you can then publish to a PowerPivot Gallery as data sources. After installation, you can design reports by entering the URL of the PowerPivot workbook as the server in your Report Builder or Report Designer data source. The underlying PowerPivot infrastructure will make the appropriate connection to the embedded PowerPivot database residing within the workbook.
To configure your Reporting Services instance
- Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click Reporting Services Configuration Manager.
- In the Reporting Services Configuration Connection dialog box, specify the server name (Machine Name), select the Report Server instance, and then click Connect.
- Under Connect, click Database, and then under Current Report Server Database, click Change Database.
- Click Create a new report server database, and then click Next.
- Next to Server Name, enter a valid Database Server instance name (that is, the machine and instance on which you want to host your Report Server database), and then click Next.
- Next to Database Name, enter a database name of your choice, and then next to Report Server Mode, click SharePoint Integrated Mode. Click Next.
- Next to Authentication Type, select Windows Credentials, and then enter information about the account that will be used to connect to the Report Server database.
- On the Summary page, click Next, and then after the configuration wizard completes, click Finish.
- Under Connect tab, Click Web Service URL, and then click Apply.
Note: You should see the “The URL was successfully reserved” message at the bottom of the page.
Important: Do not close this dialog box because you will need the information it contains to complete the next step.
Integrate Reporting Services with SharePoint
- Open the SharePoint Central Administration website, click General Application Settings, and then select Reporting Services Integration. If this option is not available, open a command prompt window as administrator, navigate to the location where you copied the SQL Server 2008 R2 Reporting Services Add-in for SharePoint, and then run rsSharePoint.msi.
- In the Report Server Web Service URL box, enter the Web Service URL from the previous section.
- Confirm that Windows Authentication is selected, specify the account you want to use, and then click OK.