Configure reporting for Project Ser...

Configure reporting for Project Server 2010

Updated: 2009-11-12

[This article is pre-release documentation and is subject to change in future releases.]

Microsoft Project Server 2010 integrates the Microsoft SharePoint Server 2010 Business Intelligence Center Web application, which provides a central point for hosting the reports, dashboards, and report connections that can be auto-created or manually authored to provide access to reporting data in Project Server for your users. The Business Intelligence Center can be used to host content created with Excel Services in Microsoft SharePoint Server 2010, Visio Services in SharePoint, PerformancePoint Services in SharePoint, PowerPivot and SQL Server Reporting Services.

To configure reporting, you must do the following steps:

Add a login for the report authors group in SQL Server
Install SQL Server 2008 Analysis Management Objects
Configure Excel Services
Configure Secure Store Service

Additionally, you will need two Active Directory groups, one for report authors and one for report viewers, as well as an Active Directory account for the Secure Store target application.

To view a video demonstration of configuring reporting for Project Server 2010, click one of the following links:

Configure Reporting (Streaming)
Configure Reporting (Download)

Add a login for the report authors group

In order for the report author to be able to access the Project Server 2010 Reporting database from Microsoft Excel, it is necessary to configure Microsoft SQL Server access and add a SQL Server login. This is due to the fact that Microsoft Office Excel 2007 and Microsoft Excel 2010 use a different method of client data access than does Excel Services. The login must allow specific access to the Project Server 2010 Reporting database to get schema information and data. Use the domain group you created for report viewers.

To add a login for the report authors group

On the computer running SQL Server Analysis Services, click Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.
Select the instance of the SQL Server database engine where your Project Server 2010 databases resides, and then click Connect.
Expand Security, right-click Logins, and then click New Login.
On the General page, click Search.
Click Object Types, and select the Groups check box.
Click OK.
Type the name of the group you created for report authors.
Click Check Names.
Click OK.
Select the User Mapping page.
In the Users mapped to this login list box, select the row containing the Project Server 2010 Reporting database.
Select the Map check box for the Project Server 2010 Reporting database.
Select the db_datareader database role membership check box.
Click OK.

Install SQL Server 2008 Analysis Management Objects

If you do not already have the SQL Server 2008 Analysis Management Objects (AMO) installed, you must install them on each application server in the farm.

Click to download the SQL Server 2008 Analysis Management Objects (http://go.microsoft.com/fwlink/?LinkId=130655&clcid=0x409)

Note:
After installing the AMO objects, restart the Project Application Service on each application server in the farm where it is running. This service is configured in SharePoint Central Administration under System Settings, Manage services on server.

Configure Excel Services

If Excel Services is not running on the farm, you must first configure an Excel Services service application. This involves two steps:

Turning on the Excel Calculation Services service
Creating an Excel Services service application

If Excel Services has already been configured on the farm, you can skip these two steps.

To turn on the Excel Calculation Services service

On the SharePoint Central Administration Web site, in the System Settings section, click Manage services on server.
If you have more than one application server on the farm, choose the application server where you want to run the Excel Calculation Services service from the Server list.
In the Service list, click Start next to Excel Calculation Services.

To create an Excel Services service application

On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.
On the Service Applications tab, click New, and then click Excel Services.
In the Name box, type a name for the service application.
In the Application pool name box, type a name for the application pool.
Choose a managed account from the Configurable list.
Click OK.

Once Excel Services has been configured, you must configure trusted file locations for the Project Server 2010 Sample Reports and Templates libraries.

Follow this procedure twice: once for each library.

To configure a trusted file location

In Central Administration, in the Application Management section, click Manage service applications.
Click the Excel Services service application.
On the Manage Excel Services page, click Trusted File Locations.
Click Add Trusted File Location.
In the Address box, type:

For the Templates library:

http://<servername>/<projectsitename>/ProjectBICenter/Templates/

or

For the Sample Reports library:

http://<servername>/<projectsitename>/ProjectBICenter/Sample%20Reports/
Under Trust Children, confirm that the Children trusted check box is selected.
In the External Data section:
1. Under Allow External Data, select the Trusted data connection libraries and embedded option.
2. Under Warn on Refresh, clear the Refresh warning enabled check box.
Click OK.

You must configure trusted data connection libraries in order to give users access to the connectors that link the report spreadsheets to the data in the Project Server Reporting database and OLAP databases. As part of this process, you will need the URL of the data connection library in Microsoft Project Web App (PWA).

Use the following procedure to determine the URL of the data connection library in PWA.

To determine the URL for the data connection library

In PWA, in the left navigation pane, click Business Intelligence.
In the left pane, click Data Connections.
On the Data Connections page, select the English (United States) option (or the appropriate language for your locale).
On the toolbar, click View Properties.
On the Data Connections properties page, right-click the English (United States) (or the appropriate language for your locale) link, and then choose Properties.
On the Properties dialog box, select the Address (URL) value.
Right-click the selected text, and then click Copy.

This is the URL for the data connection library, which you will need when you set up a trusted data connection library in the next procedure.
Click Cancel.
On the Data Connections properties page, click Close.

To set up trusted data connection libraries

In Central Administration, in the Application Management, click Manage Service Applications.
Click the Excel Services service application.
Click Trusted Data Connection Libraries.
Click Add Trusted Data Connection Library.
In the Address box, paste the URL for the data connection library that you copied in the previous procedure. It should be in the following format:

http://<ServerName>/<ProjectSiteName>/ProjectBICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)
Click OK.

Configure Secure Store Service

In SharePoint Server, Secure Store Service enables users to access multiple system resources without having to provide authentication credentials more than once. SharePoint Server implements Secure Store Service 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 Secure Store Service. To enable Secure Store functionality for SharePoint Server, you need to start the Microsoft Single Secure Store service and then manage Secure Store settings in Central Administration.

If you do not already have Secure Store Service configured in your farm, you must do that first. This involves three steps:

Turning on the Secure Store Service
Creating a Secure Store Service service application
Generating a Secure Store Service key

To turn on the Secure Store Service

On the SharePoint Central Administration Web site, in the System Settings section, click Manage services on server.
If you have more than one application server on the farm, choose the application server where you want to run the Secure Store Service from the Server list.
In the Service list, click Start next to Secure Store Service.

To create a Secure Store Service service application

On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.
On the Service Applications tab, click New, and then click Secure Store Service.
In the Name box, type a name for the service application.
In the Application pool name box, type a name for the application pool.
Choose a managed account from the Configurable list.
Click OK.
When the service application has been successfully created, click OK.

To generate a Secure Store Service key

On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.
Click the Secure Store service application.
On the Edit tab, click Generate New Key.
Type and confirm a Pass Phrase, and then click OK.

Once Secure Store is configured, you must create a Secure Store target application.

To create a Secure Store target application

On the SharePoint Central Administration Home page, in the Application Management section, click Manage Services Applications.
Click the Secure Store Service.
On the Secure Store Service page, select the Edit tab.
Click New.
On the Create New Secure Store Target Application page:
1. In the Target Application ID box, type ProjectServerApplication.
  
  Note:
  This value is case-sensitive.
2. In the Display Name box, type a name for the Secure Store Target Application.
3. In the Contact Email box, type an e-mail address.
4. From the Target Application Type drop-down list, select Group.
5. Click Next.
On the Specify the credential fields for your Secure Store Target Application page, click Next.
On the Specify the membership settings page:
1. In the Target Application Administrators box, type the user name of the farm administrator.
2. In the Members box, type the name of the domain group you created for report users.
3. Click OK.
On the Secure Store Service Application page, select the check box for the target application that you just created.
On the ribbon, click Set Credentials.
On the Set Credentials for Secure Store Target Application (Group) dialog box, type the user name and password of the account you created for the secure store target application.

Important:
This account must have db_datareader permissions on the Project Server Reporting database.
Click OK.