Configuring the Report Center

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.

Configuring Reporting Services integration

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.

Installing the SQL Server Reporting Services Add-in for SharePoint Technologies

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.

Creating a SharePoint Integrated Reporting Services database

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 [View full size image]
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.

Configuring the Execution Account

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.

Configuring the Reporting Services settings in SharePoint

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 [View full size image]
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. If you would like to limit the number of historical snapshots allowed for a report, select Limit number of snapshots to and enter the number of maximum snapshots. If you would like to limit the timeout for report processing, select Limit report processing (in seconds) and enter the maximum number of seconds. If you would like to enable logging for report processing and enable automatic removal of old logs, enter the number of days you would like logs to be retained. Select whether you would like to enable Windows integrated security for data sources or not. Select whether or not you would like to enable ad-hoc report processing. If you enable this option, users will be able to use Report Builder to create ad-hoc reports and your reporting services will process those ad-hoc reports.
8.	Click OK.

Adding Reporting Services Content Types

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 [View full size image]
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.

Configuring Report Center as Trusted Location

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: Session Timeout (as seen in MOSS): Values between –1 and 2073600 seconds (no timeout – 24 days). This is the maximum time that an Excel Calculation session can stay active as measured from the end of the request. If you enter a value of 0 then the session stays active only for that particular request. Short Session Timeout: Values between –1 and 2073600 seconds (no timeout – 24 days). This is the maximum time that an Excel Web Access session can stay open but inactive as measured from the start of the open request. If you enter a value of 0 then the session stays active only for that particular request. Maximum Request Duration: Values are –1 (no limit) and between 1 and 2073600 seconds (1 second – 24 days). This defines the maximum duration of a single request in a session.
8.	Type the appropriate workbook values in the workbook property section: Maximum Workbook Size: Values are 1 to 2000MB. This is the maximum size of the workbook that can be opened by Excel Calculation Services. Maximum Chart Size: Any positive integer is a valid value. This is the maximum size of the chart that can be opened by Excel Calculation Services.
9.	Type the Calculation Behavior in the calculation behavior section. Volatile Function Cache Lifetime: Valid values are -1 (calculated once per session) and 0 (calculated is always calculated) and 1 to 2073600 seconds (1 second to 24 days). These values define the maximum time in seconds that a manual or automatic calculation will be cached. Workbook Calculation Mode: File, manual, automatic, or automatic except data tables. This setting defines the calculation mode of Excel Calculation Services and will override the workbook settings unless you select file.
10.	Type the external data settings in the External Data section. Allow External Data: None, trusted data connection libraries only and trusted data connection libraries and embedded. Warn on Refresh: Select this option if you want to display a warning before refreshing the data from external locations Stop When Refresh on Open Fails: Selecting this option will stop the open operation on an external file if the file contains a refresh on open data connection and the file cannot be refreshed when it is opening and the user does not have open rights to the file. External Data Cache Lifetime: Valid values are -1 (never refresh after first query) and 0 to 2073600 seconds (0 seconds to 24 days). These values define the maximum time in seconds that the system can use cached manual or automatic external data query results. Maximum Concurrent Queries Per Session: Any positive integer is a valid value. This defines maximum number of concurrent queries that can be executed during a single session.
11.	Select whether you would like to allow user-defined functions in the User Defined Functions section.