Another Approach to Risk Matrix Calculation
I just read Alexander Burton's post on EPMSource.com 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:
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).
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.
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.
case when c.wsec_cat_name is null
then 'Permission assigned directly to user'
else 'Category assigned directly to user'
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
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.
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.
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:
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.
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.
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.
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".
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.
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.
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.
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.
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)
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
- 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.
* 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”.