Using External Content Types with Project Server 2010

External Content Types (ECT) are yet another function with SharePoint 2010 that allow users to create simple powerful solutions with Project Server data.  ECTs allow users to pull data directly from the Reporting database and surface it within SharePoint lists.  I plan to spend the next couple of posts talking about the possibilities of using ECT within a Project Server 2010 setting.  In the meantime, however, this post is designed to address how to create the ECT itself.

image

First off, ECTs and the Business Connectivity Service that they reside upon are well documented throughout the Web.  Feel free to plug those terms into your favorite search engine to identify a whole host of articles and posts on the topic.  One of the main questions that comes up is often related to security and configuring the Secure Store Service to work with BCS, for which I will refer you to this blog on the topic: http://blogs.msdn.com/b/bcs/archive/2010/03/12/authenticating-to-your-external-system.aspx

Creating the ECT

To create the ECT, we need to open a non-PWA site within the Project Server farm.  This site will serve as the repository for our ECTs – which may be consumed throughout Project Server.  Generally, I’d recommend placing the ECT on the top level site within the PWA web application – or a subsite under PWA – although that recommendation is subject to review by a more technically savvy SharePoint expert.  (If you’re planning a Project Server implementation, note that PWA should never really be provisioned as the top level site in a web app.)

For this example, I am using the Contoso demo image, and will use the http://intranet.contoso.com site to host my ECT.  I open that site with SharePoint Designer, a free download.  As you may have already realized, SharePoint Designer may not be used against the main PWA site.  This is a restriction placed by Microsoft to prevent folks from inadvertently bringing the entire Project Server implementation to its knees using SharePoint Designer.

Once SharePoint Designer has opened the site, click on the menu item for External Content Types.

image

Click on the option to create a new ECT.

image

The following screen will be displayed.

image

Enter the name of the new ECT and click on the link to configure the data source.

image

Point to the Reporting database, and select the table with the appropriate fields.  In this case, I will be using the MSP_EPMProject_UserView view as that includes almost all of the project level metadata.

image

We now need to define two operations: the Read List operation and the Read Item operation.  Right click on the selected table to define those two operations.  First we’ll create the Read Item operation.  Follow the default settings until you get the option to select the fields returned by the operation.

image

Uncheck all of the fields, then gradually add the fields you would like included back into the ECT.  Make a list of each field added, as the second operation must include exactly the same fields.  Once the appropriate fields have been selected, click on the Finish button.

Right click on the table again and create the Read List operation.  This will kick off by asking you to create a filter.  Filters are not required, but may be very powerful.  In later posts, I’ll address uses of the wildcard filter.

(For this reason, I’ll insert a <<boomark>> here to reference back to in future posts.)

For now, let’s skip the filter discussion and move on.  In the next screen, select exactly the same fields as were configured in the Read Item operation.

image

After selecting Finish, click “Save” in SharePoint Designed to deploy the ECT back to SharePoint.  Close SharePoint Designer.

Configuring ECT Security

If you didn’t review the blog post above, at this point, I would strongly recommend taking a look now: http://blogs.msdn.com/b/bcs/archive/2010/03/12/authenticating-to-your-external-system.aspx.  After the ECT has been created, we need to configure security.  Your best bet is to point it to the right Secure Store Service while creating it within SharePoint Designer.

The second option is to go into the Business Connectivity Service application within SharePoint Central Admin to configure security.

image

Click on Business Connectivity Services and then click on the new ECT.

image

Click on the button to set the Object Permissions.

image

Add the appropriate users or AD Group to the appropriate permissions.

image

Close Central Admin and navigate back to the main PWA site.

Validating the ECT

There are a couple of ways to determine if the ECT is now working properly.  The easiest way is perhaps to create an External List.  An External List is a SharePoint list that surfaces external data within the familiar interface of a list.  I’ll be writing more about that topic in a future post.

In the meantime, go back to the main PWA site (or another subsite), and under the Site Actions menu, select the option to View All Site Content.

image

Create a new External List.

image

Add the ECT to the External List.

image

The External List should now render a complete list of all projects within the system.

image

When clicking on an individual item, the information from the list is displayed in an easy to use format.

image

Stay tuned as I explore potential use cases for ECT in the next couple of posts.

Advertisements
Using External Content Types with Project Server 2010

9 thoughts on “Using External Content Types with Project Server 2010

  1. Roland Mayne says:

    Nice article! I have just a question about the field ProjectUID. As in your example this field is selected and mapped, but when you create a external list based on this content type this field is not displayed. Why?
    It will be very useful to display this to ling to the ProjUID in the Query filter. Do you have a solution?

  2. Jake says:

    I know this is an old post, but I was wondering if there is a way to add this list on a Project site. Once on the project site, how would I filter it to only show PDP details for that project, and not all the projects on your library?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s