Configuring SharePoint 2010 to seamlessly run Project Server Excel reports

The title says it all!  This is an important topic if you want to use Excel Pivot tables to dynamically generate reports on Project Server 2010 data.  Let me outline the steps:

1. Start Excel Services

2. Configure Trusted File Locations for Excel Services Settings

3. Start the Secure Store Service

4. Configure Secure Store Service Settings

These steps will configure SharePoint with Excel Services to communicate with Project Server 2010.  This will not retrieve data or configure SQL Server.  I will leave that for my next blog post.

1. Start Excel Services.  To do this you will be doing two things:

1.1 Start a service and

Login to SharePoint Central Administration and under the System Settings group you will want to select Manage services on Server. You will se a list of services and you need to ensure that the Excel Calculation Services are “Started”. If the service does not say “started” then click “Start” hyperlink under the action column just to the right of the status column.

1.2. Create an Excel Services application

After you have the service running you will need to click on Application Management on the quick launch at the left in your browser.  Then under Application Management select and click on Manage Service Applications.

You will be on the service applications tab, click New and then Excel Services Application.  Supply a Name, and an Application Pool Name.  Make sure that the Managed Account is set to use the SharePoint account you use to manage Application Pools. Now click OK.

2. Configure Trusted File Locations for Excel Services Settings.  This will eliminate the need to authenticate between Excel Services and the Project Server Excel Services reports.  Do this for every server URL where you have reports or report templates.

2.1. Go back to the Excel Services Application page in SharePoint Central Administration. Step 1.2 above. Now add a trusted file location.

Click on Trusted File Locations, and click Add Trusted File Location.  In the Address box, enter each URL, the out of the box ones will be like:

<a href=”http:////ProjectBICenter/Templates/”>//ProjectBICenter/Templates/”>http://<servername>/<projectsitename>/ProjectBICenter/Templates/

or

<a href=”http:////ProjectBICenter/Sample%20Reports/”>//ProjectBICenter/Sample%20Reports/”>http://<servername>/<projectsitename>/ProjectBICenter/Sample%20Reports/

Make sure the check the Trust Children option. Under the Allow External Data section, make sure to check the Trusted data connection and libraries and embedded option. and under Warn on Refresh clear the checkbox for Refresh warning enabled.   Now click OK.

3. Configure Trusted Data Connections Libraries. This will give users access to the connection strings/settings that run the reports.  Do this for every server URL where you have data connection files stored.

3.1. Determine your Data Connections URL.  Navigate to the connection string library.  In Project Sever default you may want to go to Project Web App\Business Intelligence\Data Connections\English (United States).  Once in the library, click Properties in the ribbon and copy the Address (URL) value. Now Cancel the properties and Close the dialog box.

3.2 Go back to the Excel Services Application page in SharePoint Central Administration. Step 1.2 above. Now add a trusted data connection library.

Click on Trusted Data Connection Libraries and select Add Trusted Data Connection Library.  In the Address box enter the URL from 3.1 above.  Then click OK.

4. Start the Secure Store Service.  This will allow SharePoint to get valid credentials for the use with the trusted addresses established above.

4.1 Turn On the Secure Services Store

Go back to the Manage Services on Server page under System Settings in Central Administration.  Make sure to select the right application server where you have Project Server installed.  In the Service list, locate the Secure Store Service and click Start if not already started.

4.2 Create a Secure Store Service application

Go back to the Manage Service Applications page under Application Management in Central Administration.  On the Services Applications tab click New – Secure Store Service.  Enter a Name, select the application pool from above and choose the managed account, also from above, from the Configurable list.  Click OK and OK.

4.3 Generate a Secure Service key

Go back to the Manage Service Applications page from 4.2 above.  Select the Secure Store service application that you just created.  On the edit tab, click Generate New Key.  Type and confirm a Pass Phrase, write this down somewhere safe. Click OK.

5. Configure Secure Store Service Settings

5.1 Go back to the Manage Service Applications page from 4.2 above. Select Secure Store Service and click on the Edit tab.  Click New to create a new Target  enter the following

Application ID type ProjectServerApplication.

Display Name box, type a name for the Secure Store Target Application.

Contact Email box, type an e-mail address.

In the Target Application Type drop-down list, select Group.

5.2  Click Next. On the Specify the credential fields for your Secure Store Target Application page, click Next again.  Now in the Specify the membership settings page enter the following

              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.

5.3 On the Secure Store Service Application page, select the check box for the target application that you just created.  In the ribbon, click Set Credentials.

Type the user name and password of the account you created for the secure store target application. This account must have db_datareader permissions on the Project Server Reporting database  Click OK.

At this point you will now have the ability to run the sample reports in your project Server instance.  In my next post I will show you how to create a custom connection and report using my timesheet forecasting and billing report.

If you have any questions, comments or suggestions please reply to this posting below.