Project Programmability

This blog focuses on customizations and programming for Project Web App, Project Server, Project Professional and Project Standard. Includes User Interface (UI) customizations, Project Server Interface (PSI) and Visual Basic for Applications (VBA) Programming. It also covers Business Intelligence.
 
 

February, 2007

  • Project Programmability and Business Intelligence

    VBA Event Handler Example

    • 4 Comments

    I felt that it is time to give VBA some love. I was surfing Web and came across this post on Changing the Cell Background Color which gave me an idea for a blog post. This post is a simple example of how to write an event handler that checks if a name of a task has a prefix of “XYZ_”. If it does, it changes the background color of the Task Name cell to yellow.

    This could be useful in scenarios where there is validation when saving to the server and you want to warn the user before the save. For example, say there is a third party application that inserts tasks into project plans automatically. When tasks are inserted by this application, it is prefixed with a code, “XYZ_”. This allows project managers to quickly identify tasks that have been inserted by the third party app. To prevent project managers from inserting the task with the same prefix, a Project Server event handler has been written to prevent tasks with the prefix from any user, except for the user context used by the third party app. This event is only fired during a save to Project Server. To give an early warning to the project manager that the project will fail on saving to the server, we can do following:

    1. Open Project and then the Visual Basic Editor (Alt + F11)
    2. Create a new Class Module for the Project

       

      Note: If you want the event to be fired for all projects that are associated with a Project Server, you will need to check out the Enterprise Global and create the event handler in it. For simplicity, I am only creating the event handler for this project.
    3. Change the name of the module to something meaningful, such as EventHandlers.
    4. Copy the following Code into the class module (This is the event handler):
    5. Public WithEvents App As Application
      Public WithEvents Proj As Project

      Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)
          
          MsgBox ("Test")
         
      End Sub

      Note: This link is to the Project 2003 SDK. It is a list of all the Project Client events you can hook into:

      http://msdn2.microsoft.com/en-us/library/aa679860(office.11).aspx

    6. Open the ThisProject Object:

    7. Paste in the following code at the top of the ThisProject Object:
    8. Dim X As New EventHandlers

      Sub Initialize_App()

          Set X.App = MSProject.Application
          Set X.Proj = Application.ActiveProject

      End Sub

      This will setup the event handler to fire before a task is changed.

    9. Now select the "Project" Object and then the "Open" procedure:

    10. This will stub out the built in event handler that will fire when the project opens. Here we want to call the initialization method we created in step 6:

      Call Initialize_App

    At this point we have the event handler hooked up and every time the user changes a task, they will get an annoying test message box. To test it, run: ThisProject.Initialize_App.

    Here is what you should get when you change a task:

    Now that we have the before task change event handler working, we need get the task that changed to change the cell background color to yellow if the task name begins with "XYZ_". In step 4 we created the event handler, we will need to change the code from displaying the test message box to:

    Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)
       
        If (Field = pjTaskName) Then
            If (InStr(NewVal, "XYZ_") = 1) Then
                ActiveCell.CellColor = pjYellow
            End If
        End If
       
    End Sub

    Now every time a user changes a task name to begin with "XYZ_" they will see the background color of the cell change to yellow:

     

    My scenario may be a bit of overkill, but hopefully it illustrates how to use the Before Task Change event and how to change the background color of a cell. Maybe in a future post, I will implement the server event that checks the names of the tasks.

    Chris Boyd

  • Project Programmability and Business Intelligence

    Task Start and Finish Dates

    • 3 Comments

    When working with the task start and finish dates via the Project PSI, you might find some strange behaviors. It is not that it is strange, it is that these two fields are used by the scheduling engine to calculate your project's schedule. Hopefully this post will give you some insight to how to work with these two fields and why they may not be set to values that you expect.  

    When you first create a task, you can set the start date and finish date for the task. The below sample code shows you how to create a new task and how to set these fields:

    dsP = new WSProject.ProjectDataSet();

    WSProject.ProjectDataSet.TaskRow taskRow = dsP.Task.NewTaskRow();
    // Set the requied fields
    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = taskGuid;
    taskRow.TASK_NAME = "Example Task 3"

    // Set the start and finish dates
    taskRow.TASK_START_DATE = new DateTime(2007, 01, 20);
    taskRow.TASK_FINISH_DATE = new DateTime(2007, 01, 20);

    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

    projWS.QueueAddToProject(jobGuid, sessGuid, dsP, false);


    The above sample code sets the start and finish date for the task to be January 20th, 2007. When I publish the project and view it in Project Center Drill Down, this is what I get:

    You might notice that the start date and finish date are not set to January 20th, but instead January 17th. This is because the start date of the project is set to January 17th. When the scheduling engine works out the schedule, it looks at the task I just created and determines that it has no constrains, thus it can be started right when the project begins. Thus the scheduling engine changes the start and finish date to January 17th.

    Now, lets create another task that is dependent on the one we just created. This time, we will make it's start and finish date January 31st, 2007:

    // Create a second task

    dsP = new WSProject.ProjectDataSet();
    taskRow = dsP.Task.NewTaskRow();

    Guid task2Guid = Guid.NewGuid();
    jobGuid = Guid.NewGuid();

    // Set the requied fields
    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = task2Guid;
    taskRow.TASK_NAME = "Example Task 4"

    // Set the start and finish dates
    taskRow.TASK_START_DATE = new DateTime(2007, 01, 31);
    taskRow.TASK_FINISH_DATE = new DateTime(2007, 01, 31);

    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

    // Here we make it dependent on the task we created before

    WSProject.ProjectDataSet.DependencyRow dependRow = dsP.Dependency.NewDependencyRow();

    dependRow.PROJ_UID = projGuid;
    dependRow.LINK_PRED_UID = taskGuid;
    dependRow.LINK_SUCC_UID = task2Guid;
    dependRow.LINK_UID = Guid.NewGuid();

    dsP.Dependency.AddDependencyRow(dependRow);

    projWS.QueueAddToProject(jobGuid, sessGuid, dsP, false);

    PublishProject(projGuid);

    Again you will notices that the schedule engine has moved the task forward to January 18th:

    This is because the dependency we added to the new task on the one we had previously created.

    Lets say that you have a task that you need to schedule, but you know it cannot start before a certain date, due to some external factors from your project. In this case, you do not want the scheduling engine to move your task forward beyond that date. In this case, we need to set the TASK_CONSTRAINT_DATE and TASK_CONSTRAINT_TYPE fields. The below sample shows how to do this:

    // Create a task with a constraint
    dsP = new WSProject.ProjectDataSet();

    WSProject.ProjectDataSet.TaskRow taskRow = dsP.Task.NewTaskRow();
    // Set the requied fields
    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = taskGuid;
    taskRow.TASK_NAME = "Example Task"

    // Set the start and finish dates
    taskRow.TASK_START_DATE = new DateTime(2007, 01, 22);
    taskRow.TASK_FINISH_DATE = new DateTime(2007, 01, 22);

    taskRow.TASK_CONSTRAINT_DATE = new DateTime(2007, 01, 22);
    taskRow.TASK_CONSTRAINT_TYPE = (short)Library.Task.ConstraintType.StartNoEarlierThan;
    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

    projWS.QueueAddToProject(jobGuid, sessGuid, dsP, false);

    PublishProject(projGuid);

    Here is what we get:

    So finally we are able to create a task and have it start on a particular date, but there is a catch. You can only put one type of constraint on your task. Here are a list of constraint types that you can use:

     

    Constraint Type Description
    AsLateAsPossible Schedules the task as late as it can without delaying subsequent tasks. Use no constraint date.
    AsSoonAsPossible Schedules the task to start as early as it can. Use no constraint date.
    FinishNoEarlierThan Schedules the task to finish on or after the constraint date.
    FinishNoLaterThan Schedules the task to finish on or before the constraint date.
    MustFinishOn Schedules the task to finish on the constraint date. Once selected the task will not be moveable on the timescale.
    MustStartOn Schedules the task to start on the constraint date. Once selected the task will not be movable on the timescale.
    StartNoEarlierThan Schedules the task to start on or after the constraint date.
    StartNoLaterThan Schedules the task to start on or before the constraint date.

     

    Hopefully you have a somewhat of an idea about creating a task and how the start and finish date is affected by the scheduling engine. Now, lets take a look at updating a task's start and finish date. If you need to update a tasks start or finish date, you will quickly learn that you cannot simply read the project data set, find the task you want to update in the task table and update the start and finish date fields like this:

    dsP.Tables[dsP.Task.TableName].Rows[1][dsP.Task.TASK_START_DATEColumn] = new DateTime(2007, 12, 03);

    You will quickly run into the following runtime exception:

    Column 'TASK_START_DATE' is read only.

    As the exception states, this is because the start date and finish date are read only fields. These fields are read only because they are calculated fields and cannot be set when updating a project data set. 

    So how can you get around this? Again, you can place constraints on the dates like we did when creating tasks. The scheduling engine will honor the constraint when calculating the schedule, but remember, there are other factors that affect the calculation. Such as the number of resource assigned to the tasks and the amount of work required to complete the task. So if you constrain your start date, it will affect your finish date. This is why you can only place one constraint on a task. The below example shows how you can update a task that must start on January 15th, 2007:

    dsP = projWS.ReadProject(projGuid, ProjOutlookConnector.WSProject.DataStoreEnum.WorkingStore);

    dsP.Tables[dsP.Task.TableName].Rows[1][dsP.Task.TASK_CONSTRAINT_TYPEColumn] = (short)Library.Task.ConstraintType.MustStartOn;
    dsP.Tables[dsP.Task.TableName].Rows[1][dsP.Task.TASK_CONSTRAINT_DATEColumn] = new DateTime(2007, 01, 15);

    projWS.QueueUpdateProject(Guid.NewGuid(), sessGuid, dsP, false);

    PublishProject(projGuid);

    Here is what you will see in PWA after publishing the project:

    We have primarily focused on the factors that affect the start date. Just like the start date, the finish date is affected by many factors that the scheduling engine takes under consideration. For example, the finish date is affected by number of resources assigned to the task, calendar exceptions, such as weekends, specific exceptions in individual resource calendars, and the amount of work assigned required to complete the task.

    These are only some basic examples. Project's schedule engine is very complex and there are a number of factors that affect the start and finish date of a task. Hopefully I have given you some insight why your start and finish dates change.

    Chris Boyd

    Technorati Profile
  • Project Programmability and Business Intelligence

    Creating a Task Hierarchy

    • 2 Comments

    The following questions has been asked:

    "[...] is there a way to set up the task hierarchy within a single project?"

    and the answer is YES! To create a hierarchy within a single project, you have to set the outline level for the sub tasks. The below example creates two task, Summary Task and Sub Task. For the Summary Task, we do not set the outline level, we just create it as normal task. The Sub Task is where you set the outline level. In this example we set the outline level to 2:


    Connection conn = new Connection("http://chboyd01/pwa");

    WSProject.Project projWS = (WSProject.Project)conn.GetWebService(Connection.Project);

    Guid sessGuid = Guid.NewGuid();
    Guid jobGuid = Guid.NewGuid();
    Guid taskGuid = Guid.NewGuid();

    Guid projGuid = GetProjectUidFromProjectName("Excel");

    projWS.CheckOutProject(projGuid, sessGuid, "");

    WSProject.ProjectDataSet dsP;

     

    // Create a task with a constraint

     

    dsP = new WSProject.ProjectDataSet();

    WSProject.ProjectDataSet.TaskRow taskRow = dsP.Task.NewTaskRow();

     

    // Set the required fields

    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = taskGuid;
    taskRow.TASK_NAME =
    "Summary Task";

    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

    projWS.QueueAddToProject(jobGuid, sessGuid, dsP, false);

    // Create a second task

    dsP = new WSProject.ProjectDataSet();

     

    taskRow = dsP.Task.NewTaskRow();

    Guid task2Guid = Guid.NewGuid();

    jobGuid = Guid.NewGuid();

    // Set the required fields

    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = task2Guid;
    taskRow.TASK_NAME =
    "Sub Task";

    // Set the start and finish dates

    taskRow.TASK_START_DATE = new DateTime(2007, 01, 31);
    taskRow.TASK_FINISH_DATE =
    new DateTime(2007, 02, 03);

    taskRow.TASK_OUTLINE_LEVEL = 2;

    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

    projWS.QueueAddToProject(jobGuid, sessGuid, dsP, false);

    PublishProject(projGuid);

    The below screen shot is the result of running the above sample code:

    Chris Boyd

     

    Technorati tags: , , ,
  • Project Programmability and Business Intelligence

    Working with Deliverables

    • 2 Comments

    The other day I had a request from an internal customer that wanted to create deliverables for a large number of tasks that already existed in their project plan. They wanted to be able to simply flag each task as a deliverable and have it published. They did not want to do all the steps involved with creating a deliverable. 

    They also wanted to tightly couple the task name and dates with the deliverable name and dates. Currently, if a deliverable is linked to a task, when the task’s dates change, the dates for the deliverable do not. This is by design to allow the project manager to intentionally make the change to the deliverable dates since these dates are commonly published to a large audience. In this case, the user wanted the deliverable dates to change with the task dates with minimum user intervention.

    To get started, I created a flag enterprise custom field. The custom field that I created was "Pub Deliverable" and it is a task custom field. I added the field to the Gantt Chart view in Project Professional:

    Next, I wrote the following VBA macro:

    Sub Create_Flagged_Tasks_As_Deliverables()
       
        Dim t As Task
        Dim fPub As String

        For Each t In ActiveProject.Tasks

            ' This gets the flag value from the Enterpise Custom Field
            fPub = t.GetField(FieldNameToFieldConstant("Pub Deliverable"))
           
            If fPub = "Yes" Then
               
                ' If the task has this deliverable GUID, then there is no deliverable
                If t.DeliverableGuid = "00000000-0000-0000-0000-000000000000" Then
                    DeliverableCreate t.Name, t.Start, t.Finish, t.Guid
                Else
                    DeliverableUpdate t.DeliverableGuid, t.Name, t.Start, t.Finish
                End If
           
            Else
           
                If t.DeliverableGuid <> "00000000-0000-0000-0000-000000000000" Then
                    DeliverableDelete (t.DeliverableGuid)
                End If
               
            End If
           
        Next t
           
    End Sub

    This macro loops through all the tasks. If the flag field "Pub Deliverable" is set to yes, then it either creates or updated the deliverable. If it is set to no and there is a deliverable associated with the task, the deliverable is deleted.

    Before you can run this code, you will need to publish and create a workspace for your project. To run it, follow these steps:

    1. Open your Project Plan

    2. Press Alt+F11 – This will open the VBA Editor

    3. Double click on ThisProject Object:

    4. Copy the VBA code into the Object

    5. Run it by clicking on the Play button:


    With this solution, the user can simply flag each task that they want published as a deliverable and run the macro. If you want to have this code executed regularly without user intervention, you might want to consider placing this code in an event handler (VBA Event Handler Example).

    Chris Boyd

  • Project Programmability and Business Intelligence

    Adding the Project Programmability RSS Feed to Live

    • 1 Comments

    This is a little off topic, but I thought that it might be interesting to some...

    You can easily add a RSS feed to this blog on your live.com homepage:

    1. Go to: www.live.com
    2. Click on "Add Stuff":

    3. Click on "Advanced options":

    4. In the "Subscribe to a specific feed by entering the URL" text box enter:

      http://blogs.msdn.com/project_programmability/rss.xml
    5. Click "Subscribe" and you should now have a RSS feed to the blog:

       
  • Project Programmability and Business Intelligence

    Creating Deliverable Reports

    • 0 Comments

    I have had many questions with regards to the Deliverables feature and reporting. If your not familiar with Deliverables, I suggest you take a look at my blog post on the Project blog:

    http://blogs.msdn.com/project/archive/2007/02/24/deliverables.aspx


    Just like all other project data, when a project plan is published, the data makes it way to the reporting database. This allows you to create some very useful reports on deliverables and dependencies. To get started with reporting, you may want to read through this post:

    http://blogs.msdn.com/project_programmability/Default.aspx?p=2

    In this post, I am only going to provide some background information on Deliverables and a couple of queries to get you started with creating your own reports. To begin with, these are the views and tables that are most commonly used for Deliverable reports:

    MSP_EpmProject_UserView

    This view shows all the projects. Commonly you will join the Project UID in this view with the Project UID or the Relationship UID from the other views. By doing this you can get information about the project the deliverable or dependency is associated with, such as the name of the project.
    MSP_WssDeliverableToProjectLinks_UserView

    This view lists all the published deliverables, not the dependencies. In this view you can get information such the UID for the project a deliverable is associated with and the start and finish date of a deliverable.
    MSP_WssDeliverableToTaskLinks_UserView

    This is the same at the MSP_WssDeliverableToProjectLinks_UserView except that is has additional fields for deliverables that are linked to tasks. This allows you to report on task details for the associated deliverable. For example, you could use the task information to write a report that shows all deliverables where the deliverable finish date is before the task finish date.
    MSP_WssListItemAssociation

    This view shows all the different associates with risks, issues and deliverables. Here you are going to want to look at the relationship type ID. The relationship type ID tells you if it is a deliverable or a dependency and if it is linked to a task or not. It is also where you can find if a dependency exists.
    MSP_WssRelationshipType

    This table lists the different types. These types refer to risks, issues and deliverables. For deliverables and dependencies, the following types are important:

    Relationship Type ID

    Description

    11 This is a deliverable that is linked to a task.
    12

    This is a dependency on a deliverable that is linked to a task. 

    13

    This is a deliverable for a project. It is not linked to any task within the project.

    14

    This is a dependency on a deliverable for a project. It is not linked to any task within the project.

    There are a set of common queries that user tend to want when creating a report for deliverables. This first query is a simple list of all the deliverables and what project they are associated with:

    SELECT 
    	ProjectName As 'Project Name', 
    	Title As 'Deliverable', 
    	StartDate As 'Start Date', 
    	FinishDate As ' Finish Date'
      FROM 
    	MSP_WssDeliverableToProjectLinks_UserView 
    	Inner Join MSP_EpmProject_UserView 
          	On 	MSP_WssDeliverableToProjectLinks_UserView.ProjectUID =
    MSP_EpmProject_UserView.ProjectUID
    
    

    The following query lists all the projects that have taken dependencies on a deliverable for given project. For the query to work, you need to set ProjectSelect.

    SELECT     
    	DeliverableProj.ProjectName AS SelectedProject,
    DependancyProj.ProjectName AS DependentProject,
    DeliverableLinks.Title, DeliverableLinks.StartDate, DeliverableLinks.FinishDate FROM MSP_EpmProject_UserView AS DeliverableProj INNER JOIN MSP_WssListItemAssociation ON DeliverableProj.ProjectUID =
    MSP_WssListItemAssociation.ProjectUID INNER JOIN MSP_EpmProject_UserView AS DependancyProj ON MSP_WssListItemAssociation.RelatedProjectUID =
    DependancyProj.ProjectUID INNER JOIN MSP_WssDeliverable AS DeliverableLinks ON MSP_WssListItemAssociation.ListItemUID = DeliverableLinks.DeliverableUniqueID WHERE (MSP_WssListItemAssociation.ProjectUID
    <> MSP_WssListItemAssociation.RelatedProjectUID) AND (DeliverableProj.ProjectName = @ProjectSelect)

    This last query lists all the projects that a given project is dependent on. Again, you need to set ProjectSelect for the query to work.

    SELECT     
    	DependancyProj.ProjectName AS SelectedProject,
    DeliverableProj.ProjectName, DeliverableLinks.Title, DeliverableLinks.StartDate, DeliverableLinks.FinishDate FROM MSP_WssListItemAssociation INNER JOIN MSP_EpmProject_UserView AS DependancyProj ON MSP_WssListItemAssociation.RelatedProjectUID = DependancyProj.ProjectUID INNER JOIN MSP_EpmProject_UserView AS DeliverableProj ON MSP_WssListItemAssociation.ProjectUID = DeliverableProj.ProjectUID INNER JOIN MSP_WssDeliverable AS DeliverableLinks ON MSP_WssListItemAssociation.ListItemUID = DeliverableLinks.DeliverableUniqueID WHERE (MSP_WssListItemAssociation.RelatedProjectUID <> MSP_WssListItemAssociation.ProjectUID) AND (DependancyProj.ProjectName = @ProjectSelect)

    To take a look at the last two queries in real reports, check out the Project Give and Get Reports in the Report Pack:

    http://blogs.msdn.com/project/archive/2007/01/30/sql-server-reporting-services-report-pack-for-project-server-2007.aspx

    This should be a good start with creating Deliverable reports. If you come up with some interesting queries for creating Deliverable reports, please share them by posting them as comments!

    Chris Boyd

     

    Tags:

Page 1 of 1 (6 items)
Recent Posts