Do More Than Manage
Gord Schmidt's Ideas for Doing More with Microsoft Project Server
Summary Task

Another Approach to Risk Matrix Calculation

I just read Alexander Burton's post on entitled Encoding a Risk Matrix in Project Server. I saw where he was going with it, but I thought there might be an easier formula to produce the same output.

My thought was to use numbers and multiplication instead of words. By converting the Consequence and Likelihood to scores between 1 and 5 and multiplying the two values together, one gets a table like this:

Risk Matrix

Converting those numbers to a Residual Risk label merely becomes identifying which band the result falls into.

Val(Mid(Likelihood, 2, 1)) * Val(Mid(Consequence, 2, 1)) < 8, "Low",
Val(Mid(Likelihood, 2, 1)) * Val(Mid(Consequence, 2, 1)) < 16, "Medium",
Val(Mid(Likelihood, 2, 1)) * Val(Mid(Consequence, 2, 1)) < 21, "High",
Val(Mid(Likelihood, 2, 1)) * Val(Mid(Consequence, 2, 1)) >= 21, "Extreme"

The result is a simpler formula (although I will grant you that the Val and Mid functions and their repetition is still a little ugly).

Posted by Gord Schmidt on Sep 30, 2010 | Permalink | Configure | Extend

Summary Task

The resource is owner on one or more projects and cannot be deleted

I was cleaning up an instance of Project Server today. I had deleted all but a handful of projects from the instance and had moved on to the resources. When I attempted to delete the resources (Server Settings, Delete Enterprise Objects, (Delete) Resources and Users), I received the error message "The resource is owner on one or more projects and cannot be deleted".

As it turns out, Project Server is enforcing referential integrity; that is, it will not allow you to delete the user if there are projects that reference that user. In my case, there were some users that had published project templates and other users that owned projects that were in the Archive.

Once I deleted the projects from the Archive and resaved the Templates myself, I was able to delete the final users from my list.

Posted by Gord Schmidt on Feb 25, 2010 | Permalink | Resolve

Summary Task

Enterprise Calendars used by Enterprise Projects

The following SQL query can be run against the Project Server Published database to get a list of Enterprise Calendars used by Enterprise Projects in Project Server 2007.

select distinct
      msp_projects p
      join msp_project_calendars pc on pc.proj_uid = p.proj_uid
      join msp_calendars bc on bc.cal_uid = pc.cal_base_uid
      pc.cal_base_uid is not null

Posted by Gord Schmidt on Oct 11, 2009 | Permalink | Extend

Summary Task

Searching for Security Gaps

When I am confronted with the security setup for a new customer it is relatively easy to go through the definitions of the Groups and Categories. What is more difficult, is finding the users that have permissions or categories assigned directly to them. (See Do Not Assign Categories or Permissions Directly to Users to find out why you don't want this.)

When the built-in user interface doesn't answer your questions, consider going to the data.

  use [ProjectServer_Published]
      case when c.wsec_cat_name is null
          then 'Permission assigned directly to user'
          else 'Category assigned directly to user'
          end SecurityIssue,
      dbo.msp_resources u
      inner join dbo.msp_web_security_sp_cat_relations r on u.res_security_guid = r.wsec_sp_guid
      left join dbo.msp_web_security_sp_cat_permissions p on r.wsec_rel_uid = p.wsec_rel_uid
      left join msp_web_security_categories c on r.wsec_cat_uid = c.wsec_cat_uid
      left join msp_web_security_features_actions f on p.wsec_fea_act_uid = f.wsec_fea_act_uid
      p.wsec_allow = 1 or p.wsec_deny = 1 or c.wsec_cat_uid is not null
  order by

Now this SELECT statement needs to be run against the Published database which means we are in unsupported territory so this query could fail when you apply some future hotfix, service pack or upgrade to a future version.

Posted by Gord Schmidt on Jun 30, 2009 | Permalink | Configure | Extend | Resolve

Summary Task

Pivot Table Width on Cube View Admin page

There are a few things in the Project Web Access interface that initially bothered me that have faded with time, but the width of the Pivot table on the View design page continues to irk me.

The monitor I use is wider than average, but the page for creating or modifying Data Analysis views only provides a Pivot table that is not even half of the page width. An equal amount is allocated to instructions that do not come close to filling the space.

Picture of the pivot table taking less than 50% of the page width

Today, I decided to hack it. And it is a hack. There is nothing elegant or clean or supportable about this. But if you are producing lots of Data Analysis views, perhaps you will want to do this too. But if you do, heed the following warnings:

  • This is not supported by Microsoft
  • This modification will impact all users regardless of their monitor size (or at least all users that can access the page)

Start on your server and navigate to the following file:

c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\Template\Layouts\CubeAdmin\CubeViewAdmin.aspx

Next, find the following code:

<!-- OWC section -->
<div id="idPivotTableDiv" style="display:block;" >
   <asp:label ID="lbldivPivotTableLBL" text="<%$Resrouces:PWA,CUBEADMIN_CUBEVIEWADMIN_PIVOTTABLE_LABEL%>" runat="server" />
   <div style="width:100%; height:300px; overflow:auto; border: 1px Solid Black;">
      <object classid="clsid:0002E55A-0000-0000-C000-000000000046" id="idPivot"></object>

Do you see the width and height definitions for the DIV section? That's where we should make changes, but only after you backup this file.

Perhaps try setting the width to 1000 pixels and the height to 500 pixels as below. You will have to base this on your screen size (remembering that this change is at the server level so every other user of the page will be impacted as well.

   <div style="width:1000px; height:500px; overflow:auto; border: 1px Solid Black;">

This results in something I find much more usable.

Pivot table at more than fifty percent of the page width

Posted by Gord Schmidt on Mar 20, 2009 | Permalink | Resolve | Extend

Summary Task

My Tasks? Surely not all of these!

As a follow-up to the posting Removing Tasks from My Tasks, I wanted to describe the tasks that can show up in users' My Tasks list that they may not be expecting.

Assignment Owner

Project Server 2007 introduces the concept of an Assignment Owner. The Assignment Owner is an assignment level field that defines the person who will provide the task status. Typically the Assignment Owner is the resource to which the task is assigned. The field is useful in situations where the person doing the work is unable to provide an update using PWA. For example, Rob the resource is performing tasks and does not have access to a computer let alone PWA so his manager Maria is marked as the Assignment Owner and she can provide updates in PWA on his behalf. This means, of course that Maria's My Tasks page shows her tasks as well as Rob's tasks where Maria is the Assignment Owner.

Every resource record has a Default Assignment Owner field which, by default should be the resource. In our example, Maria could be made the Default Assignment Owner for Rob and then the project managers would not need to update the Assignment Owner field.

If the Default Assignment Owner field is not defined for enterprise resources, the Assignment Owner in the assignments in a project schedule default to the project manager who created the assignment.

Generic Resources

When a project manager assigns a generic resource to a task, the project manager automatically becomes the Assignment Owner for that assignment. This makes a bit of sense when you sit back and think about it. Obviously the generic resource cannot be its own assignment owner -- there is not a real person! If a project manager wants to avoid having these assignments clog up the My Tasks page, consider setting the Published field to "No".

Inactive Resources

When an Enterprise Resource becomes inactive, the Assignment Owner for his/her assignments becomes the project manager who next publishes the project schedule. The project manager can avoid the additional tasks on his/her My Tasks page by reassigning those tasks to an active resource. 

Posted by Gord Schmidt on Mar 8, 2009 | Permalink | Resolve

Summary Task

Cannot Save Schedule to Project Server

A while back I was sitting in on a client training session when a bit of a puzzler presented itself. The class had been touring around PWA and had just created their first schedules in Microsoft Project 2007. Now everyone had successfully connected to Project Server 2007 from Project Professional 2007, but only half of the class was able to save to the server.

For half of the class, selecting Save from the File menu resulted in the "Save As" common dialog for files.

The Save As window appears

We double-checked that the client was connected to the server properly and we confirmed that the users had the appropriate rights. Then the trainer remembered a setting on the Save tab of the Options dialog window. The half that did not work had the setting "Save Microsoft Office Project Files as:" set to "Microsoft Project 2000 - 2003 (*.mpp)".

When we changed the value to "Project (*.mpp)", they could save to Project Server 2007.


Posted by Gord Schmidt on Jan 22, 2009 | Permalink | Resolve

Summary Task

Removing Tasks from My Tasks

A common question I get from administrators and users is how to remove tasks from the My Tasks page in Project Server 2007.

The ways a task “disappears” from a user’s My Tasks page:

  • The Project Manager sets the Published field to No for the task and publishes the project
  • In MS Project, the Task is deleted and/or the resource assignment is removed
  • The user sets the view to only include “Current Tasks” and the task start-end date range fails outside of the current task date range

Some additional suggestions from Patrick Conlan:

  • An Administrator removes the project from the Published and Reporting databases
  • The Project Manager changes the resource in the plan from committed to proposed
  • The Project Server hacker modifies the MSP_WEB_SP_QRY_Statusing_ReadStatus stored procedure in the Published database to filter out more records (of course, this would not be supported by Microsoft)

Let me know if you know any others.

Posted by Gord Schmidt on Jan 10, 2009 | Permalink

Summary Task

Build Generic Team from Enterprise

In some organizations the project managers do not build the team on their schedules, instead leaving this function to resource managers or the PMO.

Recently, I came across an implementation where the enterprise project templates were populated with all of the generic resources. This was done because the project managers did not have the rights to use the Build Team from Enterprise functionality.

As you can imagine, the Resource Sheet and Resource Usage views were difficult to use as they were full of unused generic resources.

A better solution in this situation is to give the project managers the rights to use the Build Team from Enterprise functionality, but only for the generic resources.

Imagine your group is called Project Creators and it already has at least the following global permissions:

  • Log On
  • Log on to Project Server from Project Professional
  • New Project
  • Open Project Template

Create a new category with the following details:

  • Category Name: Generic Resources
  • Projects: All current and future projects in the Project Server database *
  • Resources: Only the resource indicated (and then select each and every generic resource)
    Image of the selecting of the generic resources

Modify the Project Creators group:

  • Categories: Add the Generic Resource category to the Selected Categories list
  • Allow the following permissions for the Generic Resources category:
    • Build Team On Project
    • Assign Resource
    • View Enterprise Resource Data
      Image with just the necessary category permissions allowed  
  • In the Global Permissions section, allow the following permission:
    • Build Team on New Project

Now the users in the Project Creators group will be able to Build Team from Enterprise, but the list of available resources will be limited to Generic Resources.

Build Team from Enterprise -- limited to generic resources

* Note that even though all projects are in this new "Generic Resources" category, users will not gain rights to view or open additional projects as the category permissions do not include permissions such as “Open Project” or “View Project Details in Project Center”.

Posted by Gord Schmidt on Nov 15, 2008 | Permalink | Configure

Summary Task

Check out Michael Jordan's Series of Presentations

Michael Jordan, a Lead Architect at Microsoft, has been delivering a great series on Project Server 2007. In the series, he presents on the aspects to consider when you evaluate, plan, deploy, and operate Project Server 2007. You can register for the upcoming webcasts and catch up on the ones you have missed at the Microsoft Project Webcasts page.
Posted by Gord Schmidt on Nov 12, 2008 | Permalink | Resources