How to Create a Project Roles Matrix with Resource Sheet in Project Server

This article applies to Project Server 2007 and 201o with SQL Server 2008 and 2008 R2

Project Managers (PM) use the Build Team feature to add people to Resource Sheet in Project Pro/Server to enable the assignment of people to tasks. The Build Team tool pulls from the Enterprise Resource Pool (ERP) in Project Server using Project Pro or PWA. The people records in the ERP consist of built in attributes such as email address, Pay Rate etc. and are often configured with custom attributes using Custom Felds such as Skill, Location etc. These attributes are at the person level and not the project level hence the problem when the PM wants to assign a project roles to people on his Resource Sheet. Project Pro/Server, just does not support custom attributes on the Team of People on the resource sheet, after all the work of building your team, you cannot assign the role that each person is committed to on your project.

Ideas to solve the problem

One would think that you could create a Custom Field called Role at the Resource level with the option to roll down but that does not work. How about use BCS in SharePoint 2010 and pull the data and build a custom interface, can’t do that because the Build Team records are not in the Reporting database. How about just build a custom SharePoint list on the Project Workspace and copy and paste the Build Team to the SharePoint list and assign roles that way, not good because now the data is in 2 places and if a team member is added to build team, PM must remember to add to the Roles Matrix also, this is not optimal. A developer could build a custom PSI Event handler to capture the Build Team Changes on the Publish event and update a custom table that is programmed to host the Role attribute, this is doable but expensive.

The No Code Solution

User SQL Server 2008/R2 Replication and replicate the MSP_PROJECT_RESOURCES table from the Published database to your own custom database, then use BCS with Read and Update operations to store the Role information. Then hook the solution to the PDP pages for simple user access.

Steps to follow

  1. Setup Transactional Replication ? This video from TechNet demonstrates how

    Create a Publisher database 
    Create a Subscriber database 
    Select replicable SQL Server database objects 
    Configure security for the replication

  2. Test replication – Change Build Team and Publish project to see immediate results
  3. Open the target table and add a suitable text field that will house your Role text
  4. Create a table to house the Roles lookup data for the BCS Roles Pick List
  5. Build an External Content Type and List using Business Data Connectivity (BCS) Services SharePoint 2010 that has Read and Update operations to the target table.
  6. Create another External Content Type for the Roles Lookup pick list.
  7. Create an association operation to configure the Roles Pick list in BCS
  8. Add the Roles External List to the Project Detail Page with a hidden URL Web Part filter using the Web Part infrastructure.
    The whole process takes less than an hour to build Proof of Concept but to put into production complete with testing would take longer of course. Now when the PM adds people to the Build Team, that person show up on the Role PDP awaiting for someone to assign the Project Role to the person.
    One caveat might be if you want to assign a role to someone who is not in Resource Pool, you would have to build in New Record and Delete Record operations which may conflict with the Replication, more to follow.

Coding Details

You will need to copy this code into a .NET>Web>Server Control Project build and compile an assembly and deploy to the GAC on the SharePoint Application server

This is the control c# code that gets the ProjectGUID from the workspace. Then you hook up and configure the BCS Filter finder and paramater to use the GUID in the filtering.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.Office.Project.Server.Library;

 

namespace ProjUIDCtl
{
    [DefaultProperty(“Text”)]
    [ToolboxData(“<{0}:ProjUIDCtl runat=server></{0}:ProjUIDCtl>”)]
    public class ProjUIDCtl : WebControl
    {
        [Bindable(true)]
        [Category(“Appearance”)]
        [DefaultValue(“”)]
        [Localizable(true)]

        private Guid? GetCurrentProjectUid()
        {

            SPWeb currentWeb = SPContext.Current.Web;
            if ((currentWeb.WebTemplateId >= WSS.WssPWSTemplateNumericIdMinLimit) &&
              (currentWeb.WebTemplateId <= WSS.WssPWSTemplateNumericIdMaxLimit))
            {
                string webPropProjUid = currentWeb.AllProperties[“MSPWAPROJUID”] as string;
                if (!string.IsNullOrEmpty(webPropProjUid))
                {
                    return new Guid(webPropProjUid);
                }
            }
            return null;

        }

 

 

        public string Text
        {
            get
            {
               
                return GetCurrentProjectUid().ToString();
            }

        }

        protected override void RenderContents(HtmlTextWriter output)
        {
            output.Write(Text);
        }
    }
}

Best way to deploy to GAC is using GACUTIL.EXE. Download the Gacutil.exe from Windows SDK and install the .NET Developer Tools. If you are on a produciton server and cannot install the SDK, copy over the following directory and run from there “C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools” to a folder on the server containing the DLLs I wanted to install:

  • gacutil.exe
  • gacutil.exe.config
  • 1033\gacutlrc.dll     (can be placed in the same dir as gacutil.exe on the server) Note: if you don’t include gacutlrc.dll in the same directory as gacutil.exe on the server, when you run gacutil.exe it doesn’t actually ‘do’ anything – just exits without error.

image

Then I ran from command prompt:    

gacutil /i mydll.dll to install the DLLs in the GAC.

image

image

Now you will need to mark the control as safe in the web.config. Navigate to Inetpub and find your web.config for your web app and enter this line in the <SafeControls> section. You will need the public key token from the properties of the assembly from the GAC and the namespace from code in Visual Studio.

<SafeControl Assembly=”ProjUIDCtl, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a89b80270eb5f91d” Namespace=”ProjUIDCtl” TypeName=”*” Safe=”True” />

Now all you have to do is add a statement to the SharePoint page using SharePoint Designer to utilize the control. Change out the PublicKey Tolken to match. The using the .NET tool box in SPD, expand the tool box and find our control under SharePoint Controls section and insert it onto page.

This is code for top of page
<%@ Register tagprefix=”ProjUIDCtl” namespace=”ProjUIDCtl” assembly=”ProjUIDCtl, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a89b80270eb5f91d” %>

This is code to add the control, put it at the end of placeholdermain closing tag as indicated.
<ProjUIDCtl:ProjUIDCtl runat=server id=”ProjUIDCtl1″></ProjUIDCtl:ProjUIDCtl>
</asp:Content>

Create your external list on your workspace that has the Project GUID (this is the external content type that points to the replicated table talked about above) Open the external list in SPD and edit the list view page and add the above to expose the control.

When done, view page in browser and the GUID should show at bottom of page

image

Notice our control is in the list while creating our paramater

image

On your External Content type add a filter parameter to the read list operation so we can use it in SPD for configuring the filter on our control.

image

Then configure the finder on the SPD tool bar to use the parameter and voila done

image

I know this needs to be written more clearly but I had to upload my notes to not loose them.

The big trick to make this work without errors in the BCS list is you must match the read only check marks across operations, among other things. more to followimage

 

Regards

Jim Cox

Advertisements