Configure Excel Calculation Services with Project Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2008-07-21

If your Microsoft Office Project Server 2007 installation is associated with a Microsoft Office SharePoint Server 2007 installation, you can configure Excel Calculation Services to allow easy report generation from the Office Project Server 2007 Reporting database.

With this functionality, you can create reports in Microsoft Office Excel 2007 and publish them to Office SharePoint Server 2007 where others in your organization can view them and save them offline for further customization.

There are two major steps to configuring this functionality:

  • Report Author Configuration — This involves configuring Microsoft SQL Server to allow report authors access to the Office Project Server 2007 Reporting database.

  • Report Consumer Configuration — This involves configuring Office SharePoint Server 2007 for report publishing.

Note

The procedures in this article assume that you are using SQL Server 2005. SQL Server 2000 can also be used and will provide identical functionality.

Note

Depending on your existing configuration, you may find that some of the procedures in this article have already been completed.

Configuring SQL Server

In order for the report author to be able to access the Office Project Server 2007 Reporting database from Office Excel 2007, it is necessary to configure SQL Server access and add a SQL Server login. This is due to the fact that Office Excel 2007 uses a different method of data access than does Excel Calculation Services.

To configure SQL Server access, you must ensure that the SQL Server database engine and SQL Server Analysis Services allows remote connections. This is done using the SQL Server Surface Area Configuration Tool.

Configure the SQL Server surface area

  1. On the computer running SQL Server 2005, click Start, All Programs, Microsoft SQL Server 2005, Configuration Tools, and then SQL Server Surface Area Configuration.

  2. Click Surface Area Configuration for Services and Connections.

  3. Select the View by Component tab.

  4. Expand Database Engine and then expand the instance of SQL Server that you are configuring.

  5. Click Remote Connections and select the Local and remote connections and Using both TCP/IP and named pipes option.

  6. Expand Analysis Services and then expand the instance of SQL Server that you are configuring.

  7. Select the Local and remote connections option.

  8. Click OK.

If you made changes to the SQL Server database engine configuration or the Analysis Services configuration using the procedure above, restart the SQL Server service and the SQL Server Analysis Services service for the instance of SQL Server that you are configuring.

Once SQL Server access is configured, you need to add a SQL Server login to allow specific access to the Office Project Server 2007 Reporting database to get schema information and data. We recommend that you use a domain group for easiest administration. If you do not use a domain group, you will need to repeat this procedure for each report author.

Add a login for a report author

  1. Click Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.

  2. Select the instance of SQL Server where your Office Project Server 2007 databases reside, and then click Connect.

  3. Expand Security, right-click Logins, and then click New Login.

  4. On the General page, type a user or group name in the Login name text box.

  5. Select the User Mapping page.

  6. In the Users mapped to this login list box, select the row containing the Office Project Server 2007 Reporting database.

  7. Select the Map check box for the Office Project Server 2007 Reporting database, and then select the db_datareader database role membership.

  8. Click OK.

Configuring Microsoft Office SharePoint Server 2007 Publishing

The publishing functionality provided in Office SharePoint Server 2007 enables authors to create and modify content and make it available to users with the appropriate levels of viewing permissions. Excel Calculation Services requires this functionality to allow for publishing from Office Excel 2007 to Office SharePoint Server 2007.

You must activate the Office SharePoint Server 2007 Publishing Infrastructure feature for the site collection used by Microsoft Office Project Web Access (PWA), and then activate the Office SharePoint Server 2007 Publishing feature for the PWA site. After the Office SharePoint Server 2007 Publishing Infrastructure feature is activated, additional permission levels and Office SharePoint Server 2007 groups that are used to delegate publishing responsibilities for users are automatically created.

Activate the Publishing Infrastructure site collection feature

  1. On the Office Project Web Access home page, click Site Actions, and then click Site Settings.

  2. On the Site Settings page, under Site Collection Administration, click Site collection features.

  3. On the Site Collection Features page, click Activate for the Office SharePoint Server Publishing Infrastructure feature.

  4. Return to the Site Settings page by clicking Site Settings in the breadcrumb.

Activate the Publishing site feature

  1. On the Site Settings page, under Site Administration, click Site features.

  2. On the Site Features page, click Activate for the Office SharePoint Server Publishing option.

Starting the Excel Calculation Services service

In order to use Excel Calculation Services, the Excel Calculation Services service must be running on your farm. This service can be configured through the SharePoint Central Administration Web site.

Start the Excel Calculation Services service

  1. In SharePoint Central Administration, click the Operations tab.

  2. On the Operations page, under Topology and Services, click Services on server.

  3. In the services list, click Start for the Excel Calculation Services service.

Creating a report center

You need to create a site to host the reports published from Office Excel 2007. This is done by creating a site collection using the Report Center template.

Create a report center

  1. In Project Web Access, click Site Actions, and then click Create Site.

  2. In the Title text box, type a title for your report center.

  3. In the URL name text box, type the URL you want to use for the report center.

  4. In the Template Selection area, click the Enterprise tab, and then select the Report Center option.

  5. In the Permissions area, select the Use unique permissions option if you want to restrict permissions on the reporting Web site; otherwise keep the default option of Use same permissions as parent site.

  6. Click Create.

Configuring the Project Server Shared Services Provider

To use Excel Calculation Services with Office Project Server 2007, you must configure the Shared Services Provider that hosts PWA. This process includes three major steps:

  • Configuring an unattended service account

  • Adding a trusted file location

  • Adding a trusted data connection library

You must configure an Unattended Service account to connect to external data sources that require both a user name string and a password string. These strings are required for authentication in Excel Calculation Services. For additional information, see How to configure the Excel Services Unattended Service account in Microsoft Office SharePoint Server 2007 (https://support.microsoft.com/kb/928738).

Configure the unattended service account

  1. In SharePoint Central Administration, under Shared Services Administration, click the Office Project Server 2007 SSP.

  2. On the Shared Services Provider home page, under Excel Services Settings, click Edit Excel Services settings.

  3. Under External Data, type the name and password of the service account you want to use, and then click OK.

In Office SharePoint Server 2007, a trusted file location is an Office SharePoint Server 2007 document library, a UNC path, or an HTTP Web site that is configured as a trusted repository for workbooks that Excel Calculation Services can access. Excel Calculation Services opens workbooks that are stored in trusted file locations only.

Configure a trusted location for Excel Services

  1. On the Shared Services Provider home page, under Excel Services Settings, click Trusted file locations.

  2. Click Add Trusted File Location.

  3. In the Address box, type the trusted location address: http://<servername>/<pwaname>/<reportcentersite>.

  4. Select the Windows SharePoint Services Location Type.

  5. Under Trust Children, select the Children trusted check box.

  6. Under Workbook Calculation Mode, select the Automatic option.

  7. Under Allow External Data, select the Trusted data connection libraries and embedded option.

  8. In the User-Defined Functions area, specify whether you plan to use user-defined functions.

  9. Click OK.

Excel Calculation Services can be configured to require the use of .odc files for all data connections. These files are used to centrally manage connections to external data sources and are stored in data connection libraries. The data connection libraries have to be explicitly trusted before Excel Calculation Services will allow workbooks to access them.

Set up trusted data connection libraries

  1. In SharePoint Central Administration, under Shared Services Administration, click the Office Project Server 2007 SSP.

  2. On the Shared Services Provider home page, under Excel Services Settings, click Trusted data connection libraries.

  3. Click Add Trusted Data Connection Library.

  4. On the Add Trusted Data Connection Library page, in the Address box, type http://<servername>/<pwaname>/<reportcentersite>/Data Connections.

  5. Click OK.

Configuring Single Sign-On

In Office SharePoint Server 2007, single sign-on (SSO) authentication enables users to access multiple system resources without having to provide authentication credentials more than once. Office SharePoint Server 2007 implements SSO authentication by including a Windows service and a secure credentials database.

To authenticate a data connection in a workbook against an external data source, you can configure Excel Calculation Services to retrieve authentication credentials from an SSO store. To enable SSO functionality for Office SharePoint Server 2007, you need to start the Microsoft Single Sign-On service and then manage SSO settings in Central Administration.

Configure the Microsoft Single Sign-On Service

  1. Click Start, Administrative Tools, Services.

  2. Double-click Microsoft Single Sign-On Service.

  3. On the General tab, select Automatic from the Startup type drop-down list.

  4. On the Log On tab, select This account, and then type the user name and password for the administrator of your Office Project Server 2007 Shared Services Provider.

  5. Click OK.

  6. Select Microsoft Single Sign-On Service in the services list, and then click Start.

Configure settings for Single Sign-On

  1. In SharePoint Central Administration, click the Operations tab.

  2. Under Security Configuration, click Manage settings for single sign-on.

  3. On the Manage Settings for Single Sign-On page, click Manage server settings.

  4. In the Single Sign-On Administrator Account section, in the Account name text box, type the user name under which the Microsoft Single Sign-On Service is running.

  5. In the Enterprise Application Definition Administrator Account section, in the Account name text box, type the user name under which the Microsoft Single Sign-On Service is running.

  6. Optionally, in the Database Settings area, update the database server name and database name that you want to use.

  7. Click OK.

Create a Single Sign-On Enterprise Application Definition

  1. On the Manage Settings for Single Sign-On page, under Enterprise Application Definition Settings, click Manage settings for enterprise application definitions.

  2. On the Manage Enterprise Application Definition page, click New Item.

  3. In the Display name text box, type a name.

  4. In the Application name text box, type a name. This name is used in the Excel Office Data Connection SSO field to identify which set of credentials should be used when retrieving the data.

  5. In the Contact e-mail address text box, type an appropriate contact e-mail address.

  6. Select the Windows authentication check box if your Office Project Server 2007 deployment uses Integrated Windows authentication.

  7. Leave the default values for the remaining fields, and then click OK.

Add security credentials to your SSO application definition

  1. In SharePoint Central Administration, click the Operations tab.

  2. Under Security Configuration, click Manage settings for single sign-on.

  3. On the Manage Settings for Single Sign-On page, click Manage account information for an enterprise application definition.

  4. From the Enterprise application definition drop down list, choose the single sign-on application definition you created in the previous procedure.

  5. In the Group account name text box, type the name of the security group that you want to allow access to your data sources.

  6. Click Set.

  7. On the Provide <Application Definition> Account Information page, enter the user name and password of a user that has access to the data sources that you will be accessing.

  8. Click OK.

  9. Click Done.

Office Project Server 2007 and Office SharePoint Server 2007 are now configured to allow report creation and publishing using Office Excel 2007 and data from the Office Project Server 2007 Reporting Database.

Creating a sample report

This section describes how to create a sample report in Office Excel 2007 and publish it to the report center that you created.

Create a sample report

  1. In Office Excel 2007, on the Data tab, click From Other Sources, and then click From SQL Server.

  2. In the Data Connection Wizard, on the Connect to Database Server page, type the name of the instance of SQL Server where your Office Project Server 2007 Reporting database is located in the Server name text box, and then click Next.

  3. On the Select Database and Table page, select the Office Project Server 2007 Reporting database from the drop-down list, and then click Next.

  4. On the Save Data Connection File and Finish page, click Authentication Settings.

  5. On the Excel Services Authentication Settings dialog box, select the SSO option and type the SSO ID in the SSO ID text box. (This is the ID that you created in the "To create a Single Sign-On Enterprise Application Definition" procedure earlier in this article.)

  6. Click OK.

  7. Click Finish.

  8. In the Import Data dialog box, select the PivotTable Report option, and then click OK.

  9. Using the fields available in the Pivot Table Field list, create a pivot table.

  10. Click the Office button, point to Publish and then click Excel Services.

  11. In the File name text box, type the location of your report library: http://<servername>/<pwaname>/<reportcentername>/ReportsLibrary, and then press ENTER.

    Office Excel 2007 opens the report center.

  12. In the File name text box, type a file name, and then click Save.

  13. On the Choose Document Type dialog box, keep the default value of Report, and then click OK.

You can now view the report in the report library.