Adding Text Search to the Project Center Interface

Update 6/15/2012: See the comment at the bottom.

…well not really, but essentially I’ll be discussing how to create the functionality that does that.  My self-assigned goal in this post is to enable SharePoint search to perform a text search of Project Center content – and – based on that search allow the user to navigate directly to the appropriate project record or PDP within Project Server.

I will be showing you how to do this using External Content Types (ECT) and then modifying the results with InfoPath to render a dynamic URL from the text based results of the ECT.

This is a request that I’ve run into a couple of times both on client sites and on the online forums.  Generally, it’s driven by the fact that we have too many projects and it’s hard for someone to zero in on specifically the right project.  Hence, the user would like to simply type in the name or a relevant keyword into SharePoint search and go directly to the record.

As alternatives, note that Project Center does offer a custom filter option, so I could go create a onetime filter that filters on specific text in the project name.  In my experience, this is a bit slow and also the filter has to be removed and reset for the next project.  The other solution would be to create custom views that slice and dice the portfolio and make it easier to navigate (see this post on creating a My Projects view).  The functionality I’ll show you how to build today would augment that.

1) Reviewing the Requirements

Let’s start off by looking at how we will navigate to the PDPs when we finish this effort.  If you click on a project in Project Center, you should see a PDP such as the Project Information page.  Note that I am using the default Contoso demo image so my defualt page is actually the Proposal Workflow Status page.

Here’s Project Center – just to get us all on the same page….

image

…and the PDP….showing the current workflow status.

image

Take a look at the URL for the PDP….

http://project.contoso.com/pwa/project%20detail%20pages/proposalstagestatus.aspx?projuid=012441e0-573e-48f7-a518-1169c28fcde8

In that URL, we can see the URL of Project Server: http://project.contoso.com/pwa/project%20detail%20pages….

….the actual PDP: proposalstagestatus.aspx…

….and the unique ID for the project: projuid=012441e0-573e-48f7-a518-1169c28fcde8

What we’ll do then is create an ECT and manipulate the results within InfoPath to regenerate that target URL.

2) Create an External Content Type

I won’t spend a whole lot of time talking about creating ECTs, but basically they’re a method of bringing external data (in this case Project Server data stored in SQL) into the SharePoint fold.

To support this example, I will create an ECT with four fields: Project Name, UID, Project Description and Project Keywords.  I could, in theory, pull any field I wanted, which should then be subject to the SharePoint search indexing.

For more information on creating an ECT, I refer you to this post from last year.  Note that we will not need a filter as part of the ReadList operation.

3) Create an External List

I now create a site to throw the list on.  Nobody should actually have to navigate to the site.  We simply want it to be appear within the search results.  For this example, I am going to create a site called External under the BI Center.  As this site will need to be modified with SharePoint Designer, we may need to move it off of the main PWA site – which has been set to preclude any SharePoint Designer edits.

On this site, I create an external list and point it at the ECT we just created.

image

So far, so good.  Note that you can click on the project to show the default display form.  Note that we could also add metrics to this form, i.e. other values that typically appear within Project Center.

image

Next, we’ll modify the display form so that it will display the URL of the project record within Project Center.

4) Modify the Display Form

Modifying the default display form for external lists is a relatively simple exercise – provided you know one workaround.  I was able to find this in a blog post yesterday, although unfortunately, I didn’t make a note of which one.   Anyway, it’s usually quite easy to modify display forms for SharePoint lists.  All one has to do is click on the option in the Ribbon.

Unfortunately, that option doesn’t work in external lists.  The trick here, is to click on the button in the ribbon to modify the list in SharePoint Designer.

image

Once the list is open in SharePoint Designer, select the option to modify the form in InfoPath.

image

This will launch InfoPath.

image

Now, let’s create a new field that will store the calculated URL.  To do this, right click on MyFields in the Fields dialog box on the right and create a new field called URL.  Set it as a hyperlink field type.

image

Drag the new field over to the form.  I also tidied up the form a bit by removing the UniqueID and cleaning up some of the label names.  I also converted the Description text box to a multiline display.

image

Now right click on the newly created field.  We’re going to modify the default value to display the appropriate URL.

image

Select the Properties option, then the button to edit the default calculation.

image

Once there, I’ll add the following custom formula:

image

Hit Ok, and publish the form back up into the InfoPath list.  You may have to save the template somewhere prior to publishing.  It doesn’t matter where you save it.

image

5) Reviewing the Results

Let’s take a look at the results.  To do that, navigate back to your external list.  Refresh it for good measure.  Click on one of the projects to review the results:

image

Click on the URL to confirm it navigates back to the appropriate place in Project Center.

image

You now have an extended version of the Project Center navigation structure accessible via enterprise search that allows users to easily find projects.  You may want to investigate reconfiguring your search and perhaps adding a custom scope to hit the external list, but that’s a topic for another blog.

  image

Adding Text Search to the Project Center Interface

Incorporating Project Server Data into Word Documents

This is a topic I’ve briefly played with before – how to automatically incorporate Project Server data into Office documents.  The REST API has long been the go to tool for doing such things, as the API allows us to consume Excel reports dynamically in Word or Powerpoint documents.

image

The general goal in adding this sort of document automation is to attempt to drive user behavior a bit.  We want our users to move away from generating documents on their C drive and generating them within SharePoint.  Adding document automation is on of the major incentives to do so, as it makes it easier to create a document within SharePoint than on the local drive.

In this post, I want to take that integration to the next level, i.e. I want to incorporate Project Server fields within the text itself of a Word document.  I might do this to automate some of my status reporting, or to create a project charter template that automatically populates with key data. 

Now, I’ll point out that this technique is not quite seamless, and probably could benefit from a little custom SharePoint development….but hopefully this may give you some ideas, and be referenced in other, more robust solutions.  I also haven’t quite figured out how to deploy this across multiple sites without custom development.  So for now, take the solution for what it is, a rough idea for automating documents at the workspace level.  If someone smarter than I wants to take this idea and run with it….by all means, be my guest.

Building the External Content Type

The first step in accomplishing this is to build an external content type (instructions here).  When building the external content type, make sure to add a filter on the project name (instructions here).

Building the Document Template

Now let’s go create our document template.  To do this we need a Project Workspace.  It doesn’t have to be a live project workspace as really all we need is a document library.  We’re going to add our ECT to the document library as a custom field.

First, navigate to the Library Settings.

image

Scroll down and add a new column.

image

I then configure the new column as below – using the name of the ECT that I created in one of the previous posts.

image

Now let’s associate that field with the default content type.  To do this, we need to enable content type management within the document library.  Click on the advanced settings to do this.

image

Enable content type management for the library.

image

Confirm that the field has been associated with the default document content type.  Once we’ve done that, lets create a new document from the workspace.  (You could also take an existing document template and load it to the document library and then adapt it to our purposes.) 

image

Note how the Document Information Panel now shows a number of blank fields.

image

These fields will be populated from the project header data in the document library.  More importantly, we can consume those fields within the document using the Quick Part mechanism.

image

I’ll go ahead and create a quick status report leveraging the Quick Parts.

image

Now let’s save the document back to the workspace.  For good measure, I’ll save another copy off to my desktop as a template file.

Once the file has been posted back to the SharePoint site, test it out by selecting the option to Edit Properties.

image

Type the project name and hit the search button….

image

…and the metadata is now populated.  Open the document to see the changes.

image

Yes….I know it’s kind of annoying to have to open the document and then close it and edit the metadata column to get it to work properly.  I am sure there’s a decent solution to that, but for now, I’ll leave it there.

Associating the Document Template

The next step here might be to associate our validated document template with the content type on the workspace.  To do that, I go back into our document library and upload the template to the content type.

image

…and now I can create a document within the document library, save it, add the metadata and know that everything is properly updated.

As I said above, the entire process is perhaps a bit contrived, but hopefully this should serve as a launching point for ideas.  Other options that might be a bit easier (depending on your particular skill set) would be to simply write VBA within Word to query Project Server data and populate fields correctly.

Also, consider combining this technique with the concept of document sets.  In that case, you would only have to apply the fields once, and then have all of the changes trickle down to the document set components.  Please see this post from Alex Burton on leveraging document sets with Project Server: http://epmsource.com/tag/document-sets/.

Credits

Note that this post was inspired by (yet another) highly informative and entertaining presentation from Mr. Tom Resing, now of Rackspace.  His presentations have become a highlight of the annual Houston SharePoint Saturday event.

Update (5/11): Tom’s presentation was indeed informative and entertaining, but after looking at my notes, Mike Huguet, PFE Extraordinaire, was the one who actually presented on customizing Word documents at the SharePoint Saturday event.  Just wanted to clear that confusion up.  Now I can rest at night.

Incorporating Project Server Data into Word Documents

Surfacing Risk Data in an External List

…so now that we have a custom SQL view developed and that data is surfaced in the form of an External Content Type, let’s add it to our SharePoint site.

Navigate to a SharePoint site, select the option to View All Site Content, and create a new External List.  If you plan to modify this list using SharePoint Designer, you’ll need to provision the list on any site but the main PWA site.

image

The list will appear as follows:

image

Add a grouping by Project Name, and you get the following…

image

Formatting the External List

To get fancy, you can even add conditional formatting to the External List.  To do so, open the list in SharePoint Designer.  Click on one of the cells in the column you would like to format, and choose the option to apply Conditional Formatting. 

image

Add the appropriate options, hit Save, refresh the page in the browser, and you should see something like this….

image

Search around the Web, and you should be able to find all sorts of blog postings about how to add icons instead of simple conditional formatting.

Removing the HTML Tags

One thing you may note is that some of the text fields have HTML tags.  This is because the fields are stored as rich text fields and then surfaced as plain text.

image

There’re probably a couple ways to fix this, including using both SQL and SharePoint Designer functionality.  Until I figure those out however, I simply went to the Project Site and converted the fields in the SharePoint Risk list from rich text to plain text. 

image

That solved the issue and doesn’t appear to throw errors on publish.  Note that you will have to republish the Risk InfoPath form if you make any changes to the fields.

…and there you have it, a more or less out of the box way to create an aggregated Risk list.  With a little practice, it shouldn’t take more than a couple of hours to implement.

Surfacing Risk Data in an External List

Creating an External Content Type for Project Risks

In this post, I will talk about how to surface the custom SQL view we developed in the previous post through External Content Types and SharePoint Business Connectivity Services (BCS).

Now this isn’t a new topic.  The only difference is that we’re using it to surface risk information.  For more information on deploying ECT against Project Server, please see one of my older posts.

….and, er….that’s pretty much it.  Not sure how much more I can add to that.  Once you’ve done that against the new custom SQL view, it will look something like this:

image

Next up….adding the data to an external list.

Creating an External Content Type for Project Risks

Customizing SQL Views to Provide Useful Datasets

In my last post, I decided to take a look at how to use External Content Types to surface Project Server Reporting database data on Risks and Issues.  In this post, I plan to talk about some quick and easy steps that are required to provide a useful dataset.

Now, before we get too far into this post, I should probably mention that working directly in the database is always a sensitive issue, but as far as I can tell, adding a custom view to the Reporting database will not cause supportability issues going forward.  Just make sure that everything is documented and backed up.  Now back to the narrative…

The challenge that I found when I first started playing with the risk tables in the Reporting database was that none of the risk tables seemed to include the Project name.  A couple include the Project ID field, but that won’t help when it’s surfaced in an External List.

image

…so I decided to create a custom SQL view.  To do that, I fired up SQL Management Studio and navigated to the Reporting database.  Once I found that, I right clicked on the Views option to create a new View.

image

From there, it’s a relatively simple matter to select the WSSRisk_Olapview and EPMProject_Userview tables to be included in my view.  I check the fields to be included, and then add a join at the ProjectUID field.  (If you’re not familiar with this interface, it works much like Access, you just grab the ProjectUID field from the table on the left and drag it to the one on the right to generate the join.)

image

Save the Custom View, and you can now use it for reporting purposes, whether that report is based on an External Content Type, an Office Data Connection file, or a direct connection back into SQL.

image

Customizing SQL Views to Provide Useful Datasets

Centralized Risk Repositories with External Lists

I was plugging away at the Project Server newsgroup when one of the contributors asked an interesting question.  After trying to aggregate Issues from each of the project sites to a single centralized repository using Content Query Webparts, Bart E. asked, “So there is no OOTB solution for a sortable aggregation, I take it?”

….well, that got me to thinking.  Is there an easy out of the box solution to deliver centralized risks and issues from Project Server?  The obvious solution would seem to be through the use of External Content Types, which I have come to regard as the Swiss Army knife of the SharePoint 2010 world.  Using External Content Types and External Lists would allow the development of easily customizable lists of aggregated risks.

So I sat down with a virtual image to see if I could make it work.  To my surprise, 20 minutes later, I pretty much had it working – with some minor formatting issues that took me back to the drawing board.

Hence my next couple of posts will be about this topic, specifically how to use External Content Types to surface the risk information embedded in the Project Server reporting database.  This approach is only suitable in the following circumstances:

  • Projects are published primarily from the Microsoft Project client – as that is the action which seems to refresh the database from the Project site data.
  • Risks are managed without custom fields, or at least the aggregation does not require those custom fields to work.  The Reporting database only captures the default fields.
  • Project schedules are updated regularly.  It is through the update process that the risks are populated within the Reporting database.

First, let’s start with a little review of the available literature.  What options are currently available?  Here are the ones I’ve come across so far.

  1. SSRS Reports – as documented by Christophe Fiessinger here.
  2. Third party tools such as this one from iPMO.
  3. Utilizing the Content Query Webpart within SharePoint – which required a little more coding the last time I played with it than I was comfortable with.  Still, probably worth a blog post at some point in the future.  A variant of this approach would cross site collections and might include something like the Content Monster Webpart.

This series of posts will focus on a fourth method, using External Content Types and an External List to surface Reporting database data.  You could probably do the same with a Web Service pulling data from SharePoint content databases, but I would defer to the SharePoint folks on that.

Hence, over the next couple of days, watch out for the following posts:

  1. Customizing SQL views to create the dataset
  2. Creating an External Content Type from a customized view
  3. Creating an External List with Risk data

(My plan is to come back and add hyperlinks to this post once each of those individual posts have been published.)

Centralized Risk Repositories with External Lists

Creating a Centralized Document Repository with External Content Types

The last couple posts I’ve written have focused on using External Content Types (ECT), a feature delivered within SharePoint 2010 as part of the Business Connectivity Service.  This post continues that discussion with an example of creating a shared document repository across multiple projects within Project Server.

I discussed a different type of implementation of similar functionality in a previous post.  in that example, I used an attachment-enabled list to allow users to upload documents via the Project Detail Pages (PDP).  From a user perspective, this would involve navigating to each individual project within the Project Center, navigating to the correct PDP, and then uploading the document.  Similarly, to review the documents, you would have to navigate to each project.

This post is slightly different.  Suppose you needed to perform a batch upload – you had a couple documents for each project that you wanted to upload in a single interface.  ….or suppose that you needed to see all of these documents in a single library, despite the fact that they may be attached to different projects.

image

My guess as to the most typical usage model for this kind of implementation would be where the PMO performs an administrative function and batch uploads specific documents.  Those documents are then available for consumption by the various project team members from the PDP interface.

Create the ECT

Follow the instructions in this post to create the ECT.

Make sure to add a filter to the Read List operation as documented in this post.

Create the Document Library

From PWA, select the View All Site Content option under the Site Actions menu.

image

Create a document library.

image

Navigate to the Library Settings.

image

Within the Library Settings, choose the option to Create Column.

image

Configure the new field as depicted in the following illustration.  This will allow users to select the project name and tag the document with the project unique identifier.

image

Test the document library.  Navigate to the main document library and add a document.  Confirm that the document metadata includes the option to enter the project name.

image

Review the uploaded document to confirm that the ProjUID field is populated properly.

image

You now have a centralized document repository with project level metadata in the PWA site.

Adding the Documents to a Project Detail Page

Adding a filtered view of the document library to a PDP is now quite easy.  Create a new PDP and add two webparts: the query string filter webpart and the newly created document library.

image

Modify the query string webpart to pull the ProjUID parameter from the PDP URL.

image

Now connect the webparts so the query string filter webpart filters the documents in the library by the ProjUID field.

image

Add the PDP to the correct Enterprise Project Type, and you now have the filtered data surfaced within a PDP.

image

Creating a Centralized Document Repository with External Content Types