Configure reporting for Project Server 2010

Accounts and security groups

The following table describes the accounts and security groups that you will need for the various procedures in this article.

Add a login for the report authors group

In order for a report author to be able to access the Project Server 2010 Reporting database from Excel, it is necessary to configure Microsoft SQL Server access and add a SQL Server login. 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 authors.

Perform the following procedure on the computer where your Project Server 2010 reporting database is located.

To add a login for the report authors group

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

2. Select the instance of the SQL Server database engine where your Project Server 2010 reporting database resides, and then click Connect.

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

4. On the General page, click Search.

5. Click Object Types, and select the Groups check box.

6. Click OK.

7. Type the name of the group you created for report authors.

8. Click Check Names.

9. Click OK.

10. Select the User Mapping page.

11. In the Users mapped to this login list box, select the row containing the Project Server 2010 Reporting database.

12. Select the Map check box for the Project Server 2010 Reporting database.

13. Select the db_datareader database role membership check box.

14. 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 (https://go.microsoft.com/fwlink/p/?LinkId=130655)

Start Excel Services

If Excel Services is not running on the farm, you must first configure an Excel Services service application.

If Excel Services is already running on your farm, proceed to Configure Excel Services; otherwise, follow the procedures below to start Excel Services.

Starting Excel Services involves two steps:

• Turning on the Excel Calculation Services service

• Creating an Excel Services service application

To start the Excel Calculation Services service

1. On the SharePoint Central Administration Web site, in the System Settings section, click Manage services on server.

2. 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.

3. In the Service list, click Start next to Excel Calculation Services.

To create an Excel Services service application

1. On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.

2. On the Service Applications tab, click New, and then click Excel Services Application.

3. In the Name box, type a name for the service application.

4. In the Application pool name box, type a name for the application pool.

5. Choose the managed account that you created for application pools from the Configurable list.

6. Click OK.

Configure Excel Services settings

When Excel Services has been started, 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

1. In Central Administration, in the Application Management section, click Manage service applications.

2. Click the Excel Services service application.

3. On the Manage Excel Services page, click Trusted File Locations.

4. Click Add Trusted File Location.

5. 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/

6. Under Trust Children, confirm that the Children trusted check box is selected.

7. 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.

8. Leave the remaining options at their default value and 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

1. In PWA, in the left navigation pane, click Business Intelligence.

2. In the left pane, click Data Connections.

3. On the Data Connections page, select the English (United States) option (or the appropriate language for your locale).

4. On the toolbar, click View Properties.

5. On the Data Connections properties page, right-click the English (United States) (or the appropriate language for your locale) link, and then choose Properties.

6. On the Properties dialog box, select the Address (URL) value.

7. 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.

8. Click Cancel.

9. On the Data Connections properties page, click Close.

To set up trusted data connection libraries

1. In Central Administration, in the Application Management, click Manage Service Applications.

2. Click the Excel Services service application.

3. Click Trusted Data Connection Libraries.

4. Click Add Trusted Data Connection Library.

5. 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)

6. Click OK.

Start the Secure Store Service

In SharePoint Server, the 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 the 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 the Secure Store is already running on your farm, proceed to Configure Secure Store Service.

If you do not already have the 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

1. On the SharePoint Central Administration Web site, in the System Settings section, click Manage services on server.

2. 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.

3. In the Service list, click Start next to Secure Store Service.

To create a Secure Store Service service application

1. On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.

2. On the Service Applications tab, click New, and then click Secure Store Service.

3. In the Name box, type a name for the service application.

4. In the Application pool name box, type a name for the application pool.

5. Choose the managed account that you created for application pools from the Configurable list.

6. Click OK.

7. When the service application has been successfully created, click OK.

To generate a Secure Store Service key

1. On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.

2. Click the Secure Store service application.

3. On the Edit tab, click Generate New Key.

4. Type and confirm a Pass Phrase, and then click OK.

Configure Secure Store Service settings

Once the Secure Store Service Application has been created, you must create a Secure Store target application.

To create a Secure Store target application

1. On the SharePoint Central Administration Home page, in the Application Management section, click Manage Services Applications.

2. Click the Secure Store Service.

3. On the Secure Store Service page, select the Edit tab.

4. Click New.

5. On the Create New Secure Store Target Application page:

   1. In the Target Application ID box, type ProjectServerApplication.

   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.

6. On the Specify the credential fields for your Secure Store Target Application page, click Next.

7. 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 viewers.

   3. Click OK.

Once the target application has been created, you must set the credentials that you want to associate with it. These are the credentials that will be used by Excel Services will use to access the reporting database when a user refreshes the data in a report.

Use the following procedure to set the credentials for the ProjectServerApplication target application.

To set the credentials for a target application

1. On the Secure Store Service Application page, select the check box for the target application that you just created.

2. On the ribbon, click Set Credentials.

3. 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. We recommend that you add this account to the Report Authors Active Directory group to give it the required permissions.

4. Click OK.

Populate the Report Authors and Report Viewers Active Directory groups

To provide your users with the needed access to the Business Intelligence Center in PWA and the reports within, you must populate the Report Authors and Report Viewers Active Directory groups as follows:

• Report Authors group: Add the Active Directory accounts of users who will be creating reports using Excel.

• Report Viewers: Add the Active Directory accounts of PWA users who will be viewing reports in the Business Intelligence Center.

  Note

  If your report authors will also be viewing reports, you can add the Report Authors group to the Report Viewers group in Active Directory.

• External Report Viewers (optional): If you have users who do not have a PWA account but require access to the Business Intelligence Center to view reports, add their Active Directory accounts to this group and follow the procedure for granting this group access to the Business Intelligence Center in Configure Business Intelligence Center access, below.

Configure Business Intelligence Center access

Users who have accounts in PWA are automatically granted access to the Business Intelligence Center as follows:

• Team Members: Members of the Team Members group in PWA are automatically added to the Team members group (Microsoft Project Server) SharePoint Group in the Business Intelligence Center.

• Project Managers: Members of the Project Managers group in PWA are automatically added to the Project Managers Group (Microsoft Project Server) SharePoint Group in the Business Intelligence Center.

If you have users who do not have PWA accounts but need to access reports in the Business Intelligence Center, you must grant them access separately. You can do this by adding those users to an Active Directory group for external report users (as noted in the Accounts and security groups section above) and then granting permissions to the Active Directory group in the Business Intelligence Center.

Use the following procedure to grant permissions to external report viewers.

To grant permissions to external report viewers

1. In PWA, in the left pane, click Business Intelligence.

2. In the Business Intelligence Center, click Site Actions, and then click Site Permissions.

3. On the Edit tab, click Create Group.

4. On the Create Group page:

   1. In the Name box, type a name for the group (for example, External Report Viewers).

   2. In the Give Group Permissions to this Site area, select the Read check box.

   3. Leave the other options at their default values and click OK.

5. On the People and Groups page, click New.

6. On the Grant Permissions dialog box, type the name of the Active Directory group that you created for external report viewers, and then click OK.

Troubleshooting

If you get error messages when trying to view reports, see Project Server 2010: Business Intelligence Center–Common Errors (https://go.microsoft.com/fwlink/p/?LinkId=202437), and the Excel Services data refresh flowchart for troubleshooting information.

See Also

Other Resources

Secure Store for Business Intelligence service applications (SharePoint Server 2010)
About the BI Center (https://go.microsoft.com/fwlink/p/?LinkId=206333)
Project Server 2010 Ignite Training: Reporting Part 1
Project Server 2010 Ignite Training: Reporting Part 2