The Report Center site template provided with MOSS 2007 creates a site that supports KPIs, dashboards, Excel Web Access Web Parts, and reports. To configure it for additional functionality, we install and configure the SQL Server Reporting Services Add-in for SharePoint technologies provided with SQL Server 2005 SP2 and configure trusted locations for the Excel files that you will use as data sources for Excel Web Access.
Cross-Ref
For more information about using and configuring KPIs, see Chapter 16.
You can extend the functionality of your Report Center by installing the SQL Server 2005 Add-in for SharePoint Technologies provided with SP2 for SQL Server 2005. This add-in will install the Report Viewer Web Part that is designed to display reports on your web part pages. In addition, the add-in extends the integration of SharePoint and SQL Server 2005 SP2 Reporting Services by:
Updating the Reporting Services Configuration tool so that you can configure your reporting services servers to create and manage SharePoint integrated databases
Enabling report subscriptions to be delivered to SharePoint libraries
Enabling synchronization from the SharePoint content database to the report server database so that the master reports live in SharePoint but the report server retains a copy
Mapping SharePoint permission roles to report server operations so that users that have add items permissions in SharePoint can create reports and users that have manage Web site permissions in SharePoint can create a reporting services schedule
Adding content types for reports, report data sources, and report models
The add-in is installed on your SharePoint servers but to support the integration, you must have the following configuration for your Reporting Services servers.
SQL Server 2005 SP2 Reporting Services.
SharePoint on the Reporting Services server. This installation does not need to be configured but the software must be present to provide the WSS Object Model.
To install the Microsoft SQL Server Reporting Services Add-in for Microsoft SharePoint Technologies on your SharePoint servers, follow these steps:
1. | Double-click the SharePointRS.msi installer package provided with SQL Server 2005 SP2. The Welcome Screen appears. |
2. | Click Next. |
3. | Accept the End User License Agreement (EULA) and click Next. |
4. | Type your name and company name in the Registration Information dialog and click Next. |
5. | Click Install. |
6. | When the installation has completed successfully, you will see the Finish screen. Click the Finish button. |
You must also configure a Reporting Services database that can store the subscription, schedule, and caching information associated with your reports. SharePoint will store reports, data sources, and models. To configure a Reporting Services database, follow these steps:
1. | From your Microsoft SQL Server 2005
Configuration Tools menu, select Reporting Services Configuration. |
2. | Type the machine name and instance for your reporting services server and click Connect. |
3. | Click the Database Setup option in the left-hand navigation pane. You will need at least one database in SharePoint mode and when you change the mode it will prompt you to create a new database. To change the mode and create a new Reporting Services database, click Change in the database connection screen, as shown in Figure 15.4. |
4. | Confirm that you want to proceed in the warning box that you will have to create a new database. |
5. | Type the database name and security credentials that you want to use for your new database and confirm that Create the report server database in SharePoint Integrated mode is selected, as shown in Figure 15.1. Click OK. Figure 15.1. Entering database information for a new Reporting Services database |
6. | Click Apply. You should now see that your database displays that it is in SharePoint integrated mode and that the SharePoint integration item in the left navigation panel is now showing a green check mark. |
7. | Close the Reporting Services Configuration tool. |
If you want to enable data sources to use stored credentials to access data or run subscription reports, you must configure the reporting services execution account. This account runs operations at a low security level. To configure a reporting services execution account, follow these steps:
1. | From your Microsoft SQL Server 2005
Configuration Tools menu, select Reporting Services Configuration. |
2. | Type the machine name and instance for your reporting services server and instance and click Connect. |
3. | Click the Execution Account option in the left-hand navigation pane. |
4. | Select the Specify an Execution Account checkbox. |
5. | Type the username in domainusername format in the Account field. |
6. | Type the password and retype the password in the Password and Verify Password fields. |
7. | Click Apply. |
To complete the reporting services integration, you need to configure the settings in SharePoint Central Administration. To configure SharePoint settings for Reporting Services integration, follow these steps:
1. | Open SharePoint Central Administration for your farm and click the Application Management tab. |
2. | Click Manage integration settings in the Reporting Services section. |
3. | Type the URL of the Report Server Web Service and choose the authentication mode, as shown in Figure 15.2. This URL will typically be http://<servername>/reportserver unless you’ve modified the virtual directory name during the Reporting Services installation. Figure 15.2. Configuring the Reporting Services Integration Settings in SharePoint |
4. | Click Grant database access in the Reporting Services section. |
5. | Specify the database server and instance for your reporting services server.. The service account(s) for the report server web service and report server windows service account will be granted rights to the SharePoint content databases. |
6. | Click OK and enter credentials that will allow access to the reporting server configuration in the username and password dialog. |
7. | Click Set server defaults in the Reporting Services section.
|
8. | Click OK. |
For each document library in which you want to store reports and enable users to create and store new reports, models, and data sources, you need to add the content types for those items to that library. To add the report builder report, report model, and report data source content types, follow these steps:
1. | Navigate to the document library to which you want to add the content types. |
2. | Select Document Library Settings from the Settings menu. |
3. | In the content type section, click Add from existing site content types. |
4. | Select Report Center Content Types from the Select site content types from drop-down menu, as shown in Figure 15.3. Figure 15.3. Adding the report services content type to a document library |
5. | Select Report Builder Model and click Add. |
6. | Select Report Builder Report and click Add. |
7. | Select Report Data Source and click Add. |
8. | Click OK. |
If you plan to store Excel workbooks in your Report Center, which you will be using as sources for KPIs or dashboard components, you need to configure the Report Center as a trusted location. The trusted locations list is a security mechanism that is provided by SharePoint so that you can enable locations where you can ensure that the workbooks are trusted and without dangerous links or programming to outside data sources. The trusted file location is configured at the Shared Service Provider level. To configure SharePoint to trust the file location of your Excel workbooks, follow these steps:
1. | Open the administration page for your Shared Service provider |
2. | Select Trusted file locations in the Excel Services Settings section. |
3. | Select Add Trusted File Location in the top navigation bar. |
4. | Type the address for your document library, file share, or Web site address in the Address field. |
5. | Select whether the location is a SharePoint site, file share, or Web site address using the Location Type radio buttons. |
6. | Select whether you would like to trust child libraries or directories by checking the Children trusted box. |
7. | Type the appropriate session management settings in the session management section:
|
8. | Type the appropriate workbook values in the workbook property section:
|
9. | Type the Calculation Behavior in the calculation behavior section.
|
10. | Type the external data settings in the External Data section.
|
11. | Select whether you would like to allow user-defined functions in the User Defined Functions section. |