Customizing External Lists of Project Data

In my previous post, I talked about using External Content Types to generate an External List within SharePoint 2010.  The main question, as always, is why would you want to do this?

….a couple of reasons:

  1. Your end users are used to SharePoint lists for managing projects.  I see this more and more in companies, where projects are managed in a list of projects.
  2. You desire to give end users permissions to create and save customized Project Center views.  With External Lists, your users gain the ability to create personal views of Project Server data.
  3. The organization requires the capability to create dynamic views of project data to, for instance, display all projects that have not been updated in the last two weeks.
  4. The Project Server data must be searchable within the SharePoint search engine.
  5. Specific fields must be totaled within the project portfolio (as of this writing, the Project Center view only totals free number entry fields, but will not total calculated fields.)

…and last but not least, it’s actually pretty easy.  I would contend that using External Lists is easier than developing a custom SSRS report – and gives your end users much more control over their individual user experience.

image

…so let’s walk through a couple of use cases….

Creating Personalized Views

Assuming permissions have been set appropriately, users may click on the Create View option under the Lists tab.

image

This opens a screen to configure the view.  Users may set the view to “personal” at the top of the page.  This means that others will not have access to the same view.

image

The user may now configure the specific fields, totals, grouping, filters and sorting as per individualized requirements.

image

The results appear as follow:

SNAGHTML1a60536

Creating Dynamic Filters

If you hang out on the Microsoft Project newsgroups long enough, this question invariably pops up….”How do I create a view that shows all projects that should have completed within the last week?” 

That question can be answered within Project Center, but it requires a little more work from the end user.   The challenge with using Project Center to answer this question is that the fields within Project Center are not dynamically recalculated whenever the view is refreshed.  To answer the question above, typically you could employ two options:

  1. Expose the Finish date and sort the view in descending order by date.  Then rely on the user to navigate through the list to identify the projects with Finish dates prior to today’s date.  Users may also manually add a filter to that column, but will have to revise the filter the next time the same query is run as it does not automatically update to today’s date.
  2. The “easier” method would be to create a custom enterprise field that flags a project with a slipped Finish date as “Yes.”  Then create a Project Center view that filters on the custom field.  This is easier for the casual end user as the work is done for them, but assumes the project manager has opened the schedule recently, recalculated fields, and republished the schedule.  Otherwise, that field will not be recalculated.  Since we’re looking for projects that may not have been updated, this technique is less than ideal.

To solve the challenge with External Lists is a much simpler story.  Simply select the option to edit the view under the List tab.  Add a filter with the following syntax to find projects that should have completed by today’s date:

image

If I wish to see all projects that have not been updated in two weeks, I might use the following filter.

image

External Lists provide another powerful tool for individualized reporting on Project Server data.

….next up…..using Enterprise Content Types to take timephased snapshots of project data – the Holy Grail of Project server killer apps.

Customizing External Lists of Project Data

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.

Using External Content Types with Project Server 2010

Capturing the Project Narrative with Project Server 2010

I remember the moment very clearly.  A couple of years ago, I was knee deep in a requirements definition workshop for a Project Server deployment.  We’d just waded through all of the wonderful dashboarding goodness of the Project Center views and I’d dutifully demonstrated a whole slew of custom metrics and indicators that I’d coded up the evening before.  It was like a Lucky Charms commercial, with the projected screen resplendent in purple arrows, green lights, and yellow diamonds.   I thought things were going pretty well when one of the key stakeholders looks at me and asks “But what about the human narrative?  I see all of these metrics, but they don’t give me the project narrative.” 

Good point.  I saw all of these beautiful metrics and complicated formulas collapse into the virtual hole from whence they had sprung.  Ever since then, this is a point that I’ve made in every requirements session, whenever an organization is looking at boiling the project portfolio down to a series of colorful indicators.  Not that there’s anything necessarily wrong with indicators and gauges and pretty lights.  A lot of consultants spend a lot of time developing those.

But let’s face it, as fellow MVP Alex Burton and I agreed over a couple of beers recently, an ideal status report has a couple of indicators, some trend analysis, and the human narrative.  The human narrative has always been and will always be key – although the metrics need to back it up to make a consistent story.

image

This post describes one technique of capturing that narrative and making it available in the Project Detail Pages (PDP) of Project Server 2010.  Note that this technique is applicable to any scenario where a single repository must store data from multiple projects, for instance a centralized approval document library or a centralized list of links to sites on another SharePoint farm – or in an implementation where each project does not in fact have its own site provisioned.  As my colleague Victor pointed out today in fact, this technique could even be used to keep a running log of change orders.

Creating the Master Status List

The first step is to create the master status update list.  This will be a centralized list of all of the status updates for each of the projects.  To create this list, navigate to the main PWA screen and select Site Actions from the top left.  Click on the option to View All Site Content.

image

In the next screen, select the option to create a custom list.  Select More Options to choose to remove the list from the Quick Launch bar.  I personally prefer to keep the status list off of the Quick Launch bar as otherwise users may try to navigate directly to the list and get confused.

image

Now we need to customize the list.  Once the new list is displayed, click on the List tab in the Ribbon, and select the List Settings button.

image

Add the columns that map to your organizational requirements for routine status reports.  In this case, I will make two multiline text fields: Last Period Status, and Next Period Activities.  To use these fields in reports, I’d recommend making them plain text, as the rich text formatting options may sometimes cause problems.

The list should now appear as follows:

image

Add one more field.  This field will capture the unique ID for the project.  The unique ID, or ProjUID is a field automatically assigned within Project Server that is used to connect the project level data with all of the other database entities.  More importantly for this purpose perhaps, the ProjUID is part of the URL of the Project Detail Pages in the Project Center.

Make the ProjUID field a single line of text.

image

Modifying the Default Entry Screen

After the list has been configured properly, we now need to modify the default New Item screen.  In SharePoint 2010, this is easily accomplished using the InfoPath editor which is triggered by the Customize Form button on the List tab.

image

The default form is now displayed, including all of the fields configured for this list.

image

As the Title field must be populated to save the list item, let’s set the Title field to default to a date stamp.  To do this, right click on the text box next to Title, and select the Text Box Properties option.  In the following example, I use the Concatenate function to combine the term “Status Update” with today’s date.

image

Click Ok.  Now remove the extraneous fields by right clicking on the heading to the left of the form row, and selecting Delete.

image

At the end, you should be left with only the fields that were added for the status report.

image

Add a Submit button by selecting the button from the ribbon and clicking on the form.  In the example below, I have right-justified the button.

image

Right click on the button to modify the Button Properties.

image

Publish the form to the list from the Backstage View.

image

You may now close InfoPath.

Creating the Project Detail Page

The next step is to create the Project Detail Page within the Project Server Server Settings screen.

image

Select the New Document option on the Documents Tab.

image

Name the PDP and select an appropriate format.  In this case, I’ll just use the simplest format I can find.

image

In the resulting page, we’re going to add three web parts: the custom list we just created, a query string filter web part, and an InfoPath form viewer web part.

image

First, let’s configure the query string web part.  Click on the option to open the tool pane. Add the text “ProjUID” to the Query String Parameter Name box.  This will make the web part pull the data in the PDP URL that comes after the “ProjUID” marker.

image

Click on the Ok button and configure the InfoPath form web part.  Set the web part to display the form we configured in the last step.  Change the default submit behavior to close the form.  Click on Ok.

image

We now need to connect the web parts.  The query string web part will serve two purposes: providing the default ProjUID value to the InfoPath form and filtering the Master Status Update List to only display the relevant updates.  Click on the drop down option next to the InfoPath form web part, and select the option to get data from the query string filter.

image

Next click on the drop down option next to the query string web part, and select the option to send filter values to the custom list.

image

Click on Stop Editing in the top right.  The PDP is now ready to be used.  Add it to an Enterprise Project Type.

Updating the Project Status

Once the Status Update PDP has been added to the appropriate EPT, users may click on the project name within Project Center to view the page.

image

Users may then enter updates in the form displayed in the center of the page.

image

Each of the historical updates will be displayed at the bottom of the screen.  As each update is tagged within the SharePoint list with the project UID, the development of a custom report should be relatively easy using SQL Server Reporting Services or even PowerPivot.

Other Tweaks

You’ll probably want to tweak the page to get it looking the way you would like it.  In the following screenshot, I’ve removed the InfoPath web part label and removed the Project UID field from appearing in the status list.  I’ve also set the status update list to not display the toolbar, which removes the option to create a new item from the bottom of the screen.

image

You may also wish to consider adding security or versioning to the SharePoint list to control who may actually be eligible to add information.

Capturing the Project Narrative with Project Server 2010