The other day, a question was posted to the support forum about how to generate a drop down list within a project workspace that automatically referenced the list of resources within Project Server.  As an example, when creating a Risk or Issue, I want to be able to map it only to those resources that have been created within Project Server.

In the 2007 environment, I played around with this concept, specifically to get the WBS field for a specific project out of a project file, and into a site as a site column, thus allowing the user to map Risks, Issues, Documents, or pretty much anything to a specific WBS deliverable.  I never did get very far, but since the question came up, I figured it was worth a try to see if the story in 2010 is any better.

My conclusion:

1) It is better.

2) It’s not only better, it’s downright easy.

3) It’s not only easy, it’s actually pretty well documented.

So here’s how you do it… Note as a minor caveat that I am just taking what is already out on the InterWebs and applying it specifically to a Project Server environment.  Before you progress much further, I would encourage you to open up your favorite search engine and type the following terms: “SharePoint 2010 External Content Types.”  That should get you a wealth of information.

Creating an External Content Type

The easiest thing to do first is to navigate to the target site within PWA.  Click on Site Actions > Edit in SharePoint Designer.

SharePoint Designer will open and download the settings for that specific site.

Click on External Content Type along the left, and then new External Content Type at the top.

Set the name of the content type, and click to create a new external connection.

Add a connection, and choose SQL from your options.

Set up the appropriate parameters, and configure security appropriately.  For this purpose, I will just use the default security, but you’ll see that you have a couple of options.  For more information on those options, I recommend referring to the MSDN guidance.

Create the Read Item Operation

Once that has been configured properly, you’ll see the SQL database appear in the Data Source box.  Drill down on that until you find the table you’re looking for.  For resources, I would recommend using a view, specifically the EPMResource_UserView.

Right click on the table or view that you have selected.  We’re going to create two operations:

1) Read Item

2) Read List

Click through with the default settings until you get to the screen where all of the fields are checked.  At this point, we want to uncheck those and only select those items we need.  For today’s purposes, I will select two: the Resource UID, and the Resource Name.  I make sure the Resource UID is mapped to our key.

…and ResourceName, I just leave as the default setting – although just to be on the safe side, I do make it read only.

Create the Read List Operation

That gives me my Read Item operation.  Now we have to right click on the same table to generate the Read List operation.  The first thing I want to configure in the Read List operation are the filter parameters.  For more on configuring filters, I recommend you check out this link.  I am setting up a Wildcard filter on the ResourceName field.  This will allow the user to type any part of the name and see the appropriate results.

Again, I select the fields I wish to make available.

…and the ResourceName field.

….at this point, all I have to do is hit “Save” to push the External Content type out to the Site Collection.  You’ll see a dialog box and progress window appear as SharePoint Designer push the new elements out.

Configure External Content Type Security

The next thing we need to do is to configure appropriate security on the content type.  To do that, I must navigate to the Central Administration page, and from there click on the Manage Service Applications link.  Click on the Business Data Connectivity Service link.

You should see your newly created content type displayed.  Select that content type.

Click on the option to Set Object Permissions.

Add the appropriate AD groups to the Content Type.  In this case, since I am in a demo environment, I am just adding the system administrator.  Brad Sutton is configured in this content type by default within the demo image.

Adding the ECT as a Column

Now we’re almost there.  Let’s go back to the site, and add our External Content Type as a column to the Issues list.  I navigate to the Issues list, select the List tab, and then List Settings.

Then, create a new column, of the type External Data.

Here’s what the settings look like.  I type in the name of my ECT in the appropriate dialog box.

…and that’s it.  Now when I create a new Issue, I scroll down to the bottom of the dialog box, and I see the following option:

If I type in something like “Paul” I get the following results.

Or I can select the second button on the right to see a list of available resources.