Configure reporting for Project Server 2010
Applies to: Project Server 2010
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
Start Excel Services
Configure Excel Services
Start Secure Store Service
Configure Secure Store
Populate the Report Authors and Report Viewers Active Directory groups
Configure Business Intelligence Center access
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.
Accounts and security groups
The following table describes the accounts and security groups that you will need for the various procedures in this article.
| Account | Description |
|---|---|
Report Authors group |
Active Directory security group to which you add users who will create reports, or any other users who need to access reports in Microsoft Excel. This group is given read permissions to the Project Server 2010 Reporting database. Have your domain administrator create this group before proceeding with the procedures below. |
Report Viewers group |
Active Directory security group to which you add users who will view reports. Have your domain administrator create this group before proceeding with the procedures below. |
External Report Viewers group |
Active Directory security group for users who do not have a PWA user account but require access to the Project Server 2010 Business Intelligence Center to view reports. If you have such users in your organization, have your domain administrator create this group and add it to the Report Viewers group described above. |
Application Pool account |
Active Directory account that is used to run the application pools for the Excel Services Application and the Secure Store Service. This account must be configured as a managed account in SharePoint Server 2010. (Required if you do not yet have Excel Services or the Secure Store Service deployed.) Note You can use the same account that you used for to run the application pool for the Project Server Service Application. |
Secure Store Target Application account |
This account provides the credentials necessary for report viewers to view reports generated from data in the Project Server reporting database. This account must have db_datareader permissions on the Project Server 2010 reporting database. Tip We recommend that you add this account to the Report Authors Active Directory group described above to give it the necessary permissions. |
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.
Important
Microsoft Excel does not use the Secure Store Service for data access. Only Excel Services in Microsoft SharePoint Server 2010 uses Secure Store. Users working with reports in Excel require direct database access.
Perform the following procedure on the computer where your Project Server 2010 reporting database is located.
Note
Alternatively, you can connect to the database engine remotely using SQL Server Management Studio.
To add a login for the report authors group
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 reporting database 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.
Note
Use the SQL Server 2008 version of AMO regardless of which version of SQL Server you are using to host your databases.
Click to download the SQL Server 2008 Analysis Management Objects (https://go.microsoft.com/fwlink/p/?LinkId=130655)
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.
Start Excel Services
If Excel Services is not running on the farm, you must first configure an Excel Services service application.
Important
If you are planning to use Excel Services for purposes beyond reporting for Project Server 2010, we highly recommend that you review the planning and operations articles for Excel Services to assist with your deployment of Excel Services on your SharePoint Server farm. For more information, see Excel Services overview (SharePoint Server 2010) and Excel Services administration (SharePoint Server 2010).
If Excel Services is already running on your farm, proceed to Configure Excel Services; otherwise, follow the procedures below to start Excel Services.
Important
The account that is running the Excel Services application pool must have access to the content database where the PWA site resides. The account that is running the application pool for the Project Server Service application has the necessary access, so you can use that account if you want to. If you chose to use a different account for the Excel Services application pool, you can grant the required access by using the following Windows PowerShell script:
$w = Get-SPWebApplication -identity <PWA web application>
$w.GrantAccessToProcessIdentity(" <service account> ")
Be aware that this action grants db_owner access to the content database.
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
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 Application.
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 the managed account that you created for application pools from the Configurable list.
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
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:
Under Allow External Data, select the Trusted data connection libraries and embedded option.
Under Warn on Refresh, clear the Refresh warning enabled check box.
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
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.
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.
Important
If you are planning to use the Secure Store Service for purposes beyond reporting for Project Server, we highly recommend that you review the planning and operations articles for the Secure Store Service to assist with your deployment of the Secure Store Service on your SharePoint Server farm. For more information, see Plan the Secure Store Service (SharePoint Server 2010) and Configure the Secure Store Service (SharePoint Server 2010).
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
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 the managed account that you created for application pools 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.
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
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:
In the Target Application ID box, type ProjectServerApplication.
In the Display Name box, type a name for the Secure Store Target Application.
In the Contact Email box, type an e-mail address.
From the Target Application Type drop-down list, select Group.
Click Next.
On the Specify the credential fields for your Secure Store Target Application page, click Next.
On the Specify the membership settings page:
In the Target Application Administrators box, type the user name of the farm administrator.
In the Members box, type the name of the domain group you created for report viewers.
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.
Note
If you ever need to reset the credentials to use a new account or to update the password associated with the credentials, you can use this same procedure.
Use the following procedure to set the credentials for the ProjectServerApplication target application.
To set the credentials for a target application
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. We recommend that you add this account to the Report Authors Active Directory group to give it the required permissions.
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
In PWA, in the left pane, click Business Intelligence.
In the Business Intelligence Center, click Site Actions, and then click Site Permissions.
On the Edit tab, click Create Group.
On the Create Group page:
In the Name box, type a name for the group (for example, External Report Viewers).
In the Give Group Permissions to this Site area, select the Read check box.
Leave the other options at their default values and click OK.
On the People and Groups page, click New.
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.