Making a Read Only PDP with InfoPath Forms

The genesis of this post lay in a conversation I had with a client.  The goal of the discussion was to identify a method to lock down specific project detail pages (PDP) within Project Server.  I figure that there’s probably a number of ways to accomplish this, but since I had been playing with InfoPath lately, this seemed to be the most logical choice.

From a process standpoint, the locked down PDP would enable a PMO to set specific fields at the project initiation, and then either prohibit or strongly discourage project managers to modify these fields.  If you’ve used Project Server for any time, of course, you’ll know that there is no true field level security….once a user can edit the project in Microsoft Project Professional, all of the fields may be edited.  But in some cases, perhaps where the project manager is primarily interacting with the project within the browser, this may be an effective way to control fields.

For other techniques that would probably be used in conjunction with this, I would encourage you to take a look at this post on security trimming PDPs, and some of my early posts of extending PDP pages into SharePoint lists.  At some point, I’ll probably come up with a proposed solution for leveraging SharePoint lists to provide secure extensions of Project Server data.

The Challenge

So the challenge I set before myself was to replicate a PDP page, but to make it read only.  At the end of this experiment I wanted to be able to pull up a PDP page, and have it render Project Server data without requiring any further input from the user.  My conclusion is that it’s not all that hard to accomplish this, albeit it requires a couple of tricks to get the output of the Query Filter Webpart to match the data requirements of the database query.

Preparing the SharePoint Site

The first thing we need to do is to add two libraries to our main PWA site: a data connection library and a forms library.  The data connection library will contain the required UDC file to link our form to the Project Server database.  The forms library will contain the form for our PDP pages.

You’ll find the options to create both libraries after selecting the View All Site Content option under Site Settings, then clicking Create and Libraries.

image

As a useful trick, you may note an InfoPath form maps to a specific content type within the form library.  If you plan on deploying this solution with different InfoPath forms, you’ll probably want to enable content type management within the forms library and then create a couple of extra content types.

Once you’ve created those two libraries, you’ll probably want to confirm that InfoPath is in fact configured within your SharePoint farm.  To do that, you’ll have to navigate to the Central Admin site.  Click on the General Application Settings.

image

Click on Configure InfoPath Forms Services.  Assuming that this does not violate your organizational security policies, go ahead and check the last three check boxes.  Click OK.

image

Close Central Admin.

Creating the Data Connection

To create the data connection, let’s open a blank form in InfoPath Designer.

image

On the Data tab, click on the Data Connections button.

image

Follow the default options to create a new data connection file to receive data from a SQL database.  For this example, I will be using the EPMProject_UserView table from the Reporting database.

image

Select the fields that you will require.  For this post, I’ll just go ahead and select all the fields in the view.

image

Complete the wizard.  Make sure to deselect the last checkbox on the last screen.

image

Once you get to the Data Connections dialog box, select to convert the newly created data connection to a connection file.

image

Post the connection file to the connection library created when we started this process.  Close InfoPath and navigate to the connection library.

image

As versioning is turned on within this library by default, you may need to click on the drop down arrow next to the connection file and set it to approved.

Configuring the InfoPath Form

Our next step is to create the InfoPath form.   With the data connection created, this is actually quite simple – with one minor caveat.  We need to add a mechanism to the form to manipulate the data received from the query string filter and convert it into something recognizable by the form query.

Create a new form with the Data Connection File template.  You could probably use any template, but this is the one that seemed the most appropriate when I was writing this post.

image

Connect it to the data connection created previously.

image

You should end up with a screen that looks like this.  Note the data fields on the right hand side of the screen.

image

InfoPath fields show up in two flavors: query and data.  The query fields are used to query the database….the data fields yield the results.  So our goal is to add the Project UID field to the appropriate query field, trigger the query, and show the results in the data fields at the bottom of the form.

You may wish to play with various permutations of this form, but the simplest way to emulate the PDP functionality is to delete everything but the data field box.

image

Now add a custom field called TempUID to the form data set by right clicking on the myFields item in the right hand dialog box.  This will create a field called my:TempUID.

image

Now click on the option to Manage Rules in the Ribbon.  Select the TempUID field, and click the option to set a New rule.

image

Set the rule to set the query field ProjUID whenever the TempUID field is changed. (Not the data field). The format for the data will appear as follows:

concat(“{“, translate(., “abcdefghijklmnopqrstuvwxyz”, “ABCDEFGHIJKLMNOPQRSTUVWXYZ”), “}”)

…where the “.” represents the TempUID field.  This formula does two things:

  • Adds round brackets around the TempUID entry, i.e. {abcdef}
  • Converts the lower case letters to upper case, i.e. {ABCDEF}

If you don’t add this formula, then the query will return an error stating that the ProjUID field is invalid data.  When completed, the calculation should look something like this (noting again that the field name appears as a “.”):

image

Add a second rule to the same field to query for data after the first rule is run.

image

Going back to the form, drag the desired fields to the data field box.

image

Save the form, and publish it to the form library created at the beginning.  When publishing the form, promote the TempUID field so that it may be used in Webpart connections.

image

After publishing the form, close InfoPath.  You are now ready to create the Project Detail Page.

Creating the PDP

Back in Project Server, navigate to the Project Detail Pages under Server Settings.  Add two Webparts to the page, the Query Filter web part and the InfoPath form.  Edit the Query Filter Webpart to pull the ProjUID parameter.

SNAGHTML2154f81

Configure the InfoPath form to pull the Query String Filter into the TempUID field we promoted when we published.

image

…and that’s it.  Add the PDP to your project type, and it will automatically display Project Server data whenever the page is viewed.

SNAGHTML21954ce

Yes, I admit the form is kind of ugly – and that description field got truncated, but dressing up InfoPath forms is probably a decent topic for another post.  For now, we got the data to show up – which I think is pretty good progress.

Credits

To give credit where credit is due….thanks very much to Jimmy McAnally (@jimmymcanally), a former colleague.  I ran into him at a recent Houston SharePoint User Group meeting, and he graciously spent a couple of hours helping me with a different hypothetical InfoPath question.  In the process, he ended up teaching me enough about InfoPath to get me started down this path.  Also thanks to Clayton Cobb (@warrtalon), SharePoint MVP, for graciously attempting to help me out on the online forums – despite my near-incoherence in being able to actually describe the issues I was facing.  I’ll give them credit for everything that actually works in this post and I would encourage you to blame me for any incorrect or misleading information.

Advertisements
Making a Read Only PDP with InfoPath Forms

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

Centralizing Project Detail Page Information: Centralized Links List

In a previous post, I talked about how to generate a centralized list of project status updates using nothing more than a custom list, InfoPath, a Query String Filter Web Part, and duct tape.  For this post, I wanted to talk about a couple more use cases for the same approximate model….i.e. a centralized information repository shared by all projects across a Project Server 2010 implementation. 

The more Project Server 2010 deployments I perform, the more I realize that this model is essentially the one size fits all solution to a number of information management needs.

image

This post will talk about using the same implementation model to deploy a centralized link list and a centralized document library.  Why would you want to do this, you ask?  Good question.  My response: count how many of the following items may apply to your particular Project Server implementation.

  • Not all projects are associated with a project workspace, or the ratio of projects to workspaces is not 1:1.
  • Projects have approval documents which may need to get posted before the actual workspace is provisioned.  Perhaps the workflow requires a workspace only after the approval documents have been scanned and loaded to the system.
  • Your project managers have SharePoint sites or external collaboration sites outside of the Project Server farm and would like to include links from Project Center to those sites.
  • You desire to surface a list or library within the PDP pages and therefore piggy back on the Project Center navigation interface by having users click on the project name and then the specific PDP page.

Creating a Centralized Link List

In this scenario, the organization does not maintain a single website for each project in Project Server.  Some projects may not have a site.  Some projects may be linked to sites that exist in other farms.  In an IT governance scenario, each project may represent a potential change for a specific application – where the application maps to a site, and each project maps to a document library on the application site.

The first step, as in the previous post, is to create a centralized list of links.  In the main PWA site, under Site Actions, select the option to View All Content.

image

Create a new Links List.  Select the More Options to opt the list out of the Quick Launch Bar.

image

Now click on the List Settings option.  We are now going to add a field to capture the Project Unique ID field (ProjUID).

image

Add the ProjUID field as a single line of text.

image

We now need to modify the input form for the list.  We will do that using the built-in InfoPath form editing functionality.

image

The default form should open.

image

Right click on the ProjUID row and select the option to delete it.  The field will still exist, but not show up in the form.

image

Add a button and configure it to trigger the Submit action.

image

In the Backstage View, publish the form back to the list.

image

Close InfoPath.  Navigate back to PWA.  Select Server Settings, and create a new Project Detail Page (PDP).

image

Once the page has been created, we will need to add three webparts to the page: a query string webpart, an InfoPath form viewer webpart, and the webpart for the custom list we created above.  The results should appear as below.

image

Now we configure the query string filter webpart and the InfoPath webpart.  Set the InfoPath webpart to display the customized form from the Master Links List.  Change the default Submit behavior to “Close the Form.”

image

Open the tool pane to configure the query string filter.  This filter will pull the ProjUID value from the URL of each PDP page when it is presented.  Enter “ProjUID” in the Query String Parameter Name field.

image

Click Apply.  Now, we need to connect the webparts.  The query string filter webpart will now perform two purposes: to populate the ProjUID field in the Master Links List, and to filter the displayed links at the bottom of the page based on the ProjUID field in the URL.

SNAGHTMLd2cdec

Configure the two connections.  The PDP should now appear as follows:

image

The PDP may now be used to track links to project sites.  When added to a project, here’s what it looks like…

image

..coming up next….deploying a centralized document library.

Centralizing Project Detail Page Information: Centralized Links List

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