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.

  • Project Programmability and Business Intelligence

    Implementing My Tasks “Read Only Custom Fields”


    Using Project Server 2007’s Reporting Database and SQL Server Reporting Services 2005

    The attached report illustrates how to safely query the RDB to allow team members to see a read only view of task assignment data. We’ve picked a random task custom field to illustrate the concept. I’ve deliberately defined the report to use “drill down” by Project Name to mimic the My Tasks grid behavior and to (UI) scale for users with lots of task assignments.

    The figure below illustrates the My Tasks web part placed above the Report Viewer web part which is displaying the sample report.


    Customizing and Installing the Report

    1. Use the SQL Server 2005 Business Intelligence Workbench (available with SQL Server 2005) to customize the report to add the fields and custom fields that you want to display to your users.
    2. Customize the report’s data source to connect to your Reporting Database.

      Note that when you deploy this report to the server you should ensure that the data source is customized with a cached userid and password that has access to read the Reporting Database. These credentials are used to avoid prompting the user for credentials and are stored securely in SQL Server.

    3. Add the Reporting Services Report Viewer web part to the My Tasks page and edit the web part properties to point to the report.


      Note the non-default web part settings for the report viewer:

      - Tool Bar: Small (the user needs to be able to enter the date filter)
      - Fixed Height: 12 Inches

    Notes on Implementation

    The report has two parameters that are used in the query:

    1. (Hidden) UserAccount is the userid – this is used to get the caller’s ResourceUID and hence filter assignments correctly, this should never be “unhidden” as the user could then enter any userid with the risk of information disclosure.
    2. TimeFilter – defaults to two weeks previous from “now”, but can then be set by the caller to any date via the calendar picker. It filters out all task assignments that started before that date. [Note that you could get creative here and add different/additional filters if you want]. Use Report/Report Parameters to edit the default setting if required.
    3. Note that the query filters out “unpublished” task assignments – when a project is published its data moves to the Reporting database. Task assignments only move to the My Tasks “statusing” tables if the Project Manager has decided that they be published.

    Other notes on deployment

    To maximize performance we avoided use of the _Userview SQL Views and referenced the underlying tables. If you want to access Task Assignment custom fields (the example show Task Custom Fields) then the query in the report will need to be reworked slightly, this should be trivial.

    The RDB is only minimally indexed. We strongly recommend the addition of secondary indexes on the following tables:

    Msp_Epm_Resource (Composite on ResourceNTAccount, ResourceUID)

    Msp_Epm_Assignment (Composite on ResourceUID, AssignmentStartDate)


    And if you are delivering a small sub set of custom fields you could consider an indexed view over those fields to maximize performance.

    Patrick Conlan

  • Project Programmability and Business Intelligence

    Learn How To Build a Silverlight App for PWA. . .


    . . . which is, after all, a SharePoint application.

    See the SharePoint and Silverlight Training Course. It includes 30 hands-on labs, including labs on accessing data through WCF services.

  • Project Programmability and Business Intelligence

    Setting the Project Owner


    We are currently in the early stages of planning the next version of Project. To help plan out the work, we are using Project 2007.  To begin, we needed to create a number of projects in bulk and decided to use the PSI to help out with the process. We already had the name for all the projects and the PMs who would be the owners. So, I created an application that read from a text file and set the PM as the project owner and some custom fields to be able to identify the projects.

    It was important to set the project owner so that PM can easily access and manipulate the project plan created by the application. This is a fairly easy task when working with the project dataset. To set the project owner, set ProjectOwnerID to the GUID of the resource. The following method may help to look up the resource GUID:

    private Guid GetResourceGuid(string ls_name)


      WSResource.Resource lo_resWS =


      WSResource.ResourceDataSet lo_resDS = new WSResource.ResourceDataSet();


      string nameColumn = lo_resDS.Resources.RES_NAMEColumn.ColumnName;

      string resUID = lo_resDS.Resources.RES_UIDColumn.ColumnName;


      PSLibrary.Filter.FieldOperationType equal =   



      PSLibrary.Filter lo_filter = new PSLibrary.Filter();


      lo_filter.FilterTableName = lo_resDS.Resources.TableName;


      lo_filter.Fields.Add(new PSLibrary.Filter.Field(resUID));

      lo_filter.Criteria = new PSLibrary.Filter.FieldOperator(equal, nameColumn, ls_name);



      lo_resDS = lo_resWS.ReadResources(lo_filter.GetXml(), false);


      return (Guid)lo_resDS.Tables[lo_resDS.Resources.TableName].Rows[0][0];


    Acourse, I knew all the PMs were resources on the Project Server instance and did not trap for errors for trying to access the dataset if no rows existed. If you use this method in general, you should put a try catch around:


    This method also uses the connection object that I described in an early post for connecting to the PSI.

    Chris Boyd

  • Project Programmability and Business Intelligence

    VBA Help: Use the Local, Offline VBA Help File


    The online VBA Help for Project 2010 (and other Office 2010 products) is not published yet to -- it has many missing members and topics. When you install the RTM release of Project Standard 2010 or Project Professional 2010, the local VBA Help file ([Program Files]\Microsoft Office\Office14\1033\WINPROJ.DEV.HXS) is installed just fine, but the default Project Help window uses the content on

    Until the publication issue is corrected, to see all of the topics in VBA Help, right-click the Connection Status drop-down list in the bottom right corner of the Project Help window, and then click Show content only from this computer

    You can also update the local VBA Help file; see the Project 2010 SDK download.


  • Project Programmability and Business Intelligence

    Scalable Issues & Risks Report



    Out of the box the Issues & Risks components of Project Server 2007’s collaboration functionality have an architectural problem which surfaces when 100’s of Project Server Workspace sites are created. This problem is forced by the need to allow flexibility in PWS placement within the farm and the ability to assign issues and risks to any user within the farm, not just specific project team members.

    This results in a non-scalable multi-site query whenever a team member hits the home page of the Issues and Risks page in Project Web Access. The home page issue is mitigated by removal of the functionality; however the team member still needs a place to see all issues and risks across the Project Server site. These instructions detail how to implement such a place in a scalable manner using the data held in the Project Server Reporting Database in combination with SQL Server Reporting Services.

    Caveat: this solution is provided as a workaround; it is not intended to become part of the Project Server 2007 solution and as such will not be localized or migrated/upgraded in future releases. The source of this solution is provided to the customer as-is and should be tested within the customer’s pre-production environment prior to production deployment. Microsoft Product Support Services will not be able to offer full support for this software.

    Architectural Notes

    This solution uses the flexibility of the Microsoft Office Project Server 2007 architecture to leverage the following technologies:

    - Project Server Reporting Database
    This database contains Issue, Risk, Project & Resource data used by this solution. Note that this data is updated on project publish (or explicit WSS site synchronization) so after new Issues & Risks are created the project should be published or the site synchronized in order to make this data available in the reporting database.
    Team members should subscribe to the Issues & Risks lists for their projects in order to get earlier notification of new data.

    - SQL Server Reporting Services 2005 SP2
    The reports used to provide this solution were built using the Report Designer (available in the “Business Intelligence Workbench”) that ships with SQL Server 2005. The SP2 version of the software was used. Note that the installation section refers to the non-integrated Report Viewer web parts, used to demonstrate the integration. It is recommended that the integrated mode be used for a cleaner interface.
    The solution uses the Report Viewer web part that must be installed into the farm as described in the installation section.
    The solution is built as a master report that hosts two sub-reports, one for issues and one for risks – this makes individual reformatting and editing a little bit easier.

    - SharePoint Web Part Pages
    The report developed to display the list of issues & risks is hosted within the Reporting Services report viewer web part within a web part page that is added to the Project Web Access site. The report assumes that flat lists are being used for Issues and Risks, if folders within the list are to be used then further testing is recommended.

    Security Notes

    The report should be run using a data connection with credentials that have read-only access to the Reporting database, specifically the following tables:

    - MSP_EpmProject: Used to obtain project name and URL details

    - MSP_EpmResource: Used to filter data based on the caller (see more below)

    - MSP_WssRisk: Data on Risks, updated when the project is published or the site is explicitly refreshed, not when an issue or risk is added to the list.

    - MSP_WssIssue: Data on Issues, updated when the project is published or the site is explicitly refreshed, not when an issue or risk is added to the list.

    A design aim for the report is to avoid having to register every team member in the reporting database. This is met via SQL Server Reporting Services parameterized filtering on the caller’s userid (this is passed into the query so that only data directly assigned to the caller is returned) – This report was not tested in a farm configuration, it is recommended that it be fully tested in a multi-server configuration before live deployment to ensure that data is filtered.

    This report does not respect SharePoint list-item security (which is not enabled by default) – if an issue or risk is assigned to a team member and then secured individually in a manner that denies the team member access they will continue to see the data pertaining to the item in this report, however the link will not display the item form.

    Scalability and Performance Notes

    This solution will be considerably more performant than the cross-site query that it replaces. However it is recommended that an additional index be created in the reporting database to facilitate the filtering, specifically:


    CREATE NONCLUSTERED INDEX [IX_FastAccountLookup] ON [dbo].[MSP_EpmResource] 
    [ResourceName] ASC 
    INCLUDE ( [ResourceNTAccount]) 

    Installation Notes


    SQL Server 2005 Reporting Services 2005 is already installed in the farm, in non-integrated mode as specific website (in the example below: http://pconlan08:81/ReportServer) – it is recommended that integrated mode be used.

    The SQL Server 2005 Reporting Services web parts are installed on every SharePoint server in the farm, installation instructions: (note that the CAB file is located in a different location on an X64 server, search for on the Reporting Services server(s))

    Installation Steps:

    1. Customize and Deploy the Reports

    Load the attached Reporting Services solution into Business Intelligence Workbench. Edit the Shared Data Source to point to the correct SQL Server and Reporting Database; then set the appropriate credentials (Reporting Services implements a secure credential store)
    Review the form design and make any customer-requested changes (see Report Configuration Notes below)
    In Business Intelligence Workbench edit the Project Properties to point to the correct Reporting Services server and Deploy the solution. Use the http://xxxxx:nn/Reports url to validate that the report works as intended before proceeding to the next step.
    Once tested, use the SQL Server Management Studio to connect to the Report Server and grant appropriate access to the reports to all team members. Test this with a subset of team members to ensure they can access the report.

    2. Create a web part page to host the web part for the report

    - Navigate to the PWA site as a SharePoint administrator, and use the Site Options drop down to select Site Settings/Modify All Site Settings which will bring up the Site Settings page.
    - Select Site Libraries and Lists which will bring up the Site Libraries and Lists page.
    - Select Create New Content which will bring up the Create page.
    - From the menu select: Web Part Page

    - Fill in the dialog as below:

    Note that you may want to place this page in a new document library rather than the Documents library as depicted as you will be granting read access to team members to this library.
    Note that once created the page can be “hidden” in the library to prevent accidental erasure (to do this edit its properties in the library view).
    This creates the page, click the “Add a Web Part” orange bar to add the web part. Use the “Advanced Web Part gallery and options” link, select the “Server Gallery” and then the “Report Viewer” web part.

    This will add the web part to the page. Now configure it as below:
    Note that the Toolbar and chrome are turned off and a fixed height of 12” is used. No width need be set. The Report Path can contain spaces (no need to %20 escape the string)
    Save the changes and the web page, navigate to the URL to ensure that the report web part displays as expected and that it contains the report as expected. (Note that if you edit and redeploy the report you will need to restart your IE session to avoid caching the report format)

    3. Connect the page to the Project Web Access menu

    In PWA Server Settings choose Quick Launch from the Look and Feel set of options.
    Add a new Entry to the Work Item Group: “Issues and Risks Report”, connect it to the page you created above, as depicted below:

    The final step in this task is to hide the un-scalable Issues & Risk option. In the Quick Launch dialog click the Issues & Risks link and set its hidden property.

    The final result should look like:

    You have now deployed the report, and added it to the PWA menu for team members to use.

    Report Implementation Notes

    Several SQL Server 2005 Reporting Services features have been used to deliver the reports, these are briefly discussed below.

    Team members will click on the new Issues and Risks Report link, this will take them to the page below:

    1. Issues and risks are grouped by status (to put Active at the top of each) – a filter could be added to remove items that have been postponed and/or closed if required, this is a trivial change to the reports. Note that if the types of status have been customized this may look slightly different.

    2. Within the grouping data is sorted by Due Date and Project Name. If Due data is not set then “Now” is assumed (ie the current date will be used)

    3. The Title is set up to be clickable to take the team member to the detailed item form, where the item can then be viewed and/or edited.

    4. Dates have had their time element stripped to save space on each row.

    5. The report displayed above is actually three reports – the master (that contains the Microsoft Office logo and sub-report objects) and two sub reports.

    6. Data is filtered on items assigned to the caller using Reporting Services filtering capabilities – the report has a hidden parameter (a standard Reporting Services feature) that is populated with the callers Windows account, care should be taken to test this in your environment.

    7. Further customization could add graphs/additional data to the view.

    Patrick Conlan

  • Project Programmability and Business Intelligence

    Agile Custom Project Guide


    In 2003, we released an Agile custom Project Guide, based on MSF for Agile Software Development methodology (which is scenarios driven , context based, Agile software development process).  We have been able to get it working for Project 2007 as well.  The Agile Project Guide allows the project manager to easily implement the Agile methodology for their projects, by outlining the Agile process and offering templates for process documentation.

    The Agile Project Guide integrates with Microsoft Office Project 2007 in order to manipulate tasks within the project plan itself and provides context sensitive task/resource information in the project guide panel.


    Here is what you need to do to get the Agile custom Project Guide working:

    1.      Download the attached zip file that contains the Agile Custom Project Guide files to your local machine

    2.      Launch Project 2007 client,

    3.      Enable the Project Guide from the View menu or just go to Tools | Options | Interface and select the “Display Project Guide” checkbox

    From the Tools | Options menu | Interface tab

    4.      Select the “Use a custom page” radio button (from the “Project Guide Functionality and Layout Page:” section)

    a.      Click the Browse button and point to the mainpage.htm file, in the Agile Project Guide folder you’ve copied locally in step 1

    5.      Select the “Use custom content” radio button (from the “Project Guide Content:” section)

    a.      Click the Browse button and point to the AgileGuide.xml file, in the Agile Project Guide folder you’ve copied locally in step 1

    6.      Click Ok on the main dialog

    7.      You should now see the Agile Custom Project Guide






    Due to the size of the Zip file, I need to split the file into two. This post has the first file. I will do a second post that will have the second file attached.


  • Project Programmability and Business Intelligence

    Working with Deliverables


    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
                    DeliverableUpdate t.DeliverableGuid, t.Name, t.Start, t.Finish
                End If
                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

    Building Web Parts using VS.NET extensions for SharePoint


    Mike McDonogh sent us this recommendation:


    If any of you are planning on building web parts for Project Server 2007 I’d HIGHLY recommending using the VS.NET extensions for SharePoint.



    The trick to get it working in VS.NET 2005 so that it will run in Project Server Sites is as follows:


    1.    Create a new Web Part project

    2.    Open the class and change the code System.Web.UI.WebControls.WebParts.WebPart to Microsoft.SharePoint.WebPartPages.WebPart

    3.    Create a DWP file, Project Web Access site required a DWP file to be imported

    <?xml version="1.0"?>

    <WebPart xmlns="">

       <Assembly>DynamicProjectRegistratonModule, Version=, Culture=Neutral, PublicKeyToken=99646028e7d67527</Assembly>

       <TypeName> DynamicProjectRegistratonModule.RegisterProjectWP</TypeName>

       <Title>Web part for registering new projects in Project Server</Title>

       <Description>You can put a description here.</Description>


    4.    Click deploy in Visual Studio.NET


    Why would you want to use the extensions [huge time saver]:


    ·         Build the Web Part assembly.

    ·         Package the Web Part solution as a Windows SharePoint Services Feature.

    ·         If this Web Part solution was deployed previously, the extensions retract the previous version of the Web Part solution Feature.

    ·         Updates the web.config safe controls settings automatically.

    ·         Install your Web Part assembly to the global assembly cache.

    ·         Deploy and activate your Web Part solution Feature in Windows SharePoint Services.

    ·         Add your Web Part to the SafeControls list.

    ·         Restart Microsoft Internet Information Services (IIS) by invoking the iisreset command.

    ·         Attach to the w3wp processes to enable debugging.

    ·         Packaging for deployment to QA/DEV/Production in VS.NET is easy.

  • Project Programmability and Business Intelligence

    How to use the “Skip to Stage” Feature in Project Server 2010 Workflows



    The skip to stage feature is designed to allow administrators the ability to restart a workflow and have it attempt to skip along the workflow until it reaches a particular stage. A common scenario for this functionality is when a project has progressed to a specific point in a workflow, but needs to be pushed back to a previous stage due to any number of reasons, such as, a new stage was inserted that needs to be addressed, certain fields need to be changed that are exposed only in previous stages, etc.

    Project Server workflows, like SharePoint workflows, must always be executed linearly. They cannot begin execution at random locations within a workflow. Neither can they “jump” from one point to another point, unless coded to do so within the workflow. The skip to stage functionality cannot circumvent this limitation. As such, the issue with restarting a workflow and attempting to push it to a particular point is that any activities which “stopped” the workflow before will continue to do so again. For Example, activities such as SetProjectStage, OnProjectSubmit, OnProjectCommit, the officeTask, ect, will still cause the workflow to dehydrate and give control back to the user. Although for the SetProjectStage activity we have done some extra work and put logic inside it so that it can be skipped, so long as there are no required fields left in the stage

    Therefore, to fully work with the skip to stage functionality you will need to wrap activities within “if statements”. When an administrator restarts a workflow and chooses to skip to a certain stage, we pass into the workflow two properties. The first property indicates that a skip stage command has been sent, and the second property contains the stage Guid that the workflow should stop at. Our SetProjectStage activity reads these two variables and skips if the first variable is set to true, and if the current stage is not the stage that is set in the second property. However, it won’t skip if there are required fields in the stage. So you will need to mimic this behavior around your other activities. You should wrap all of the activities between the SetProjectStage activities (not including the SetProjectStage activity) with an ifElseActivity statement that checks the first Property.

    Customizing Workflows in Visual Studio to work with skip to stage

    To make any workflow work correctly with the skip to stage functionality an “if statement” should be placed around all of the activities between each of the SetProjectStages. The key example of when this would be necessary is for the office task activities.

    Screen shot of example workflow:


    In the above example the “ifElseActivity1” is used to bypass the leadApproval sequence activity, which contains all of our approval activities.

    As you can see from the above screen shot, it is the “if statement” that we need to figure out how to code. In order to do so, we need to be aware of the two properties that are passed into the workflow during a skip to stage event:

    WorkflowContext.SkipToStage : Boolean : If true, a skip to stage functionality has been requested

    WorkflowContext.StageUid : Guid : Guid of the stage the workflow should skip to

    Steps for creating the workflow:

    1. Insert and set the properties of a SetProjectStage activity

    · You will need to set the “AlwaysWait” property to True in order for the Skip to Stage scenario to properly work.

    · If you do not set the Always wait property to true the workflow may not be able to skip to and stop at previous stages.


    2. Insert an IfElseActivity

    3. Have the activity check the “projectSequence1.WorkflowContext.SkipToStage” variable.

    · If variable is set to false, execute all activities

    · Else, bypass



    The WorkflowContext.StageUid is not needed for the “If Statement” but is there in case you wish to check what stage the user is trying to skip to.

    *The complete above example is found in the attached Visual Studio solution.*

    Administration: Initiating the Skip to Stage feature

    To attempt to force a project to skip to a particular stage, do the following within Project Server.

    1. Log into Project Server as an Administrator

    2. Go to Server Setting


    3. Click on “Change or Restart Workflows”


    4. In the top drop down, select the EPT that the project(s) that you would like to restart and skip to a stage belong to.

    5. Select the project(s) which you would like restart and skip to stage from the left panel and add them to the right panel.


    6. Check the “Restart current workflow for the selected projects” option


    a. Alternatively you can also select the “Associate projects with a new Enterprise Project Type:” option. If you select an Enterprise Project Type that has a workflow associated with it, you can then select which stage in that workflow the project(s) should attempt to skip to.

    7. Next select which stage the project(s) should attempt to skip to

    a. Skip until the current workflow stage

    i. Selecting this option will tell the projects to attempt to skip until they reach the stage that the project(s) are in currently

    b. Skip to a particular workflow stage & then selecting a particular stage

    i. Selecting this option will tell the projects to attempt to skip until they reach the stage that has been selected

    ii. If the selected stage does not exist, the workflow will stop at the very first opportunity.

    8. Press OK

    9. The project restart jobs will be sent to the queue, and the projects will attempt to restart and skip.

    Skip to Stage Example

    Expected behavior of the skip to stage can be summed up by the following example.

    · A workflow has 3 stages

    · All stages are marked as “AlwaysWait”

    · Project is on Stage 2

    · Stage 1’s required custom fields are filled out

    · Stage 2’s required custom fields are not filled out

    · Stage 3’s required custom fields are not filled out

    Administrator will go and:

    o Restart Skip to current stage

    § This will make the workflow restart and stop at stage 2

    o Restart Skip to stage 1 (trying to make the workflow stop on a stage where all of the require custom fields are filled out)

    § This will make the workflow stop at stage 1 and wait for submit

    o Restart Skip to stage 3 (trying to make it jump over stage 2 even though its required custom fields are not yet filled out)

    § Workflow will stop at stage 2

    § Workflow will not be able to skip stage2 if it has required fields.

    § If you really want to skip it, you need wrap the SetProjectStage as well in an if/else skip condition, which checks the SkipToStage variable and the StageUid variable.

    o Restart Skip to stage 5 (trying to make the workflow stop on a stage that does not exist)

    § Workflow will stop at stage 1. This is because of the combination of “Destination stage” doesn’t exist & current stage=”Always wait”.


    This feature has been designed and created to address the majority of our customer needs when it comes to being able to skip between stages. In this blog we reviewed there are two steps to use this feature. The first is to correctly develop your workflows to be able to skip over the activities that are between the SetProjectStage activities. This is done by wrapping the activities within an ifElse activity. And the second stage is to restart the workflows and select the stage the project should attempt to skip to. This is done with Project Server --> Server Settings --> Change or Restart Workflows.

    If you have any additional questions please feel free to post comments, and I will follow up accordingly.

  • Project Programmability and Business Intelligence

    Getting at the Task Time Phased Data


    I have been asked many times how to get the task time phased data from the PSI. Unfortunately there is no way to get this through the PSI. I suggest that you either go to the RDB for this information or the cubes. Here is a quick example.

    I created a project with one task and broke the work down over a week:


    I saved and published the project so that it would make its way into the reporting database. Here is the query I wrote to retrieve this data:

        INNER JOIN MSP_EpmTask_UserView 
    ON MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID WHERE (MSP_EpmTask_UserView.TaskName = 'Task 1')

    Here is the result of the query:

    Chris Boyd

  • Project Programmability and Business Intelligence

    Writing a PSI Extension for Project Server 2010


    The blog articles How to make PSI Extensions in Project Server 2010 - Part I and  Part II show how to create and use a simple PSI extension that reads a list of timesheets for the current user, by calling both the GetCurrentUserUid method and the ReadTimesheetList method in the extension. The code in the articles requires using the undocumented Microsoft.Office.Project.Server.Interfaces.dll and Microsoft.Office.Project.Server.Schema.dll assemblies, creates WCF channels for the Resource and Timesheet services, and then returns an XML string of the results.

    The blog articles show more information about using WCF development in Project Server than the Project 2010 SDK includes. For background information about why PSI extensions in Project Server 2010 are different than extensions in Project Server 2007, read those articles.

    The PSI Extensions in Project Server 2010.docx article in the attachment ( shows how to create and use a PSI extension that makes the same calls as in the previous blog, but with the following differences:

    • Use the Resource and Timesheet proxy service files, instead of the undocumented assemblies.
    • Configure the services programmatically, similar to the same way shown in Walkthrough: Developing PSI Applications Using WCF, instead of creating a WCF ChannelFactory.
    • Return a TimesheetListDataSet, instead of a string.
    • Use an app.config file for the client, as with any other PSI service, instead of creating a ChannelFactory.

    The attachment includes the draft article, complete code of the extension and a test application, and examples of the PSIExtensions.svc and web.config files for the PSI.


  • Project Programmability and Business Intelligence

    Checking Errors

    Here is a great recommendation from Jim Corbin:

    There are two main places to check for configuration and runtime errors when you are developing solutions for Project Server. The Unified Logging Service (ULS) trace logs can be more detailed than the application event log.

    · Application events   In the Start menu on the Project Server computer, click Run, and then type eventvwr. In the left pane of the Event Viewer window, click Application to see the events logged by Project Server, Windows SharePoint Services, ASP.NET, SQL Server, custom event handlers, and other applications. The Project Server event sources include ProjectQueueService and pjevtsvc.

    · ULS You can configure the ULS trace log to record specific or all categories and levels of activities in Project Server and SharePoint. To view the trace logs, you can use Windows Notepad, Microsoft Excel, or the Log Viewer add-in feature for SharePoint. Log Viewer is a useful download that is available from CodePlex.

    To configure the ULS trace log for a specific Project category:

    1. Open the SharePoint 3.0 Central Administration application, click Operations, and then click Diagnostic logging in the Logging and Reporting section.

    2. In the Event Throttling section, select a specific category such as Project Server - Server-Side Events. If you select Project Server - General, all Project categories will be logged.

    3. Select the Verbose level for the least critical event to report in the trace log.

    Caution   Run verbose logging only when you need it. Especially on a production server, select only a specific category. The size of logs can grow to be large. To turn off all logging, select the empty category and None for the trace log least critical event. To record relatively few events, select High or Monitorable.

    4. Use the default path for the trace logs, for example, C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\. If you use the Log Viewer add-in for SharePoint, it looks for trace logs in the default path.

    5. The default maximum number of log files to maintain is 96.

    6. The default number of minutes to use a log file is 30. That is, ULS tracing creates a new log file after 30 minutes. Log files include the date and time in the filename, for example, SERVERNAME -20070424-1612.log.

    To use the Log Viewer add-in for SharePoint:

    1. Download and install the Log Viewer feature from CodePlex SharePoint 2007 Features. See the Releases tab for the list of downloads. The release notes include installation instructions.

    2. The Log Viewer is a global feature. After it is installed, click Operations on the Central Administration page, and then click View Unified Logging Service in the Utilities section.

    3. On the Unified Logging Service (ULS) Logs page, select a file to view, and then select the Project Server category. That shows all of the specific categories such as Project Server Queue, Project Server Server-Side Events, Project Server Reporting, and so forth.

    4. To see all events in the ULS log, leave the trace severity drop-down list blank. If you select Verbose, that shows only the verbose level events.

    5. Click Go.

    Log Viewer add-in for SharePoint showing a ULS log for Project


  • Project Programmability and Business Intelligence

    Reading Assignment Enterprise Custom Field Values with VBA



    There has been a number of people asking how to read assignment enterprise custom field values with VBA. In fact, we ran into this issue internally with our dogfood efforts and fixed it in SP1. So, if you need to get/set assignment custom field values, the first step is to download SP1:

    Once you have SP1 installed, it is fairly easy to read assignment enterprise custom fields. When you read and set task and resource enterprise custom fields, you use the GetField and SetField methods in VBA. To read and set the assignment values you don't use the GetField and SetField, but instead use the name of the enterprise custom field as a property of the assignment.  There are a couple of caveats, however:

    • The field name can't contain spaces in the name
    • When you're writing your code, you won't get auto complete to show you the field name. This is because the property isn't a part of the type library and therefore isn't early bound.  As long as you pass in a valid field name, however, then the code will late bind to it. 

    Here is a short example. Suppose your custom Field name is "ecfName", here is how you would read it:

    For Each T in ActiveProject.Tasks

      If Not (T is Nothing) Then

        For Each A in T.Assignments

          assignCFVal = A.ecfName

        Next A

      End If

    Next T

    Chris Boyd

  • Project Programmability and Business Intelligence

    Security in Project Server 2010–What about Custom Permissions?


    SharePoint Server 2010 handles user authentication through claims processing, which is a new feature for SharePoint and Project Server. SharePoint handles both Windows authentication and Forms authentication for Project Server users. For authorization, you can use the ReadResourceAuthorization and SetResourceAuthorization methods in the Resource service of the PSI. Because you probably don’t often change security authorization settings for users, you would normally go to the Manage Users page in Project Web App to select a user and set the global and category permissions.

    The Security business object in Project Server (with programmatic access through the PSI Security service) manages security groups, categories, templates, and the global Project Web App permissions. The Security service can add existing permissions or remove permissions from the sets available for Project Server users. However, the Security service does not have a method for creating a custom permission. For example, if you created a Project Server extension that updates a Siebel CRM system, you might want a custom permission that enabled users to use that extension.

    In Office Project Server 2007, you can create custom global and category permissions by modifying security tables in the Published database. Custom permissions show in the PWA lists of permissions, where Project Server administrators can secure the 3rd-party extension the same way they secure other Project Server features. The Walkthrough: Creating and Using Custom Project Server Permissions article is the only SDK example where an exception is made for changing the Published database. 

    NOTE:  The Project team would like some feedback on the importance of custom permissions. If you need to create custom permissions in Project Server, please respond to this post.

     In Project Server 2010, that process for creating custom permissions still works as it did in Project Server 2007. In future versions of Project Server, no modifications to tables in the Published, Draft, or Archive databases will be supported. Custom permissions and secure links that rely on table modifications still work in Project Server 2010, but that process will be deprecated. As an alternative, you may have to create your own user interface to manage custom permissions, or use claims augmentation in a custom application. For more information, see Claims Provider.


    For more information about Project Server security, including a discussion of global and category permissions, see the Project Server Security Primer in the Project Server 2007 SDK and Security and protection for Project Server 2010 in TechNet.

  • Project Programmability and Business Intelligence

    Technical Resources on Workflow/Demand Management in Project Server 2010



    This blog entry is designed to try and capture all of the different pieces of information that exists right now regarding workflows in Project Server 2010.  Based on the feedback we are in process of creating a dedicated “Demand Management” resource center on Microsoft TechNet that will became the “one-stop” shop for all your technical and developer resources for Demand Management, including Workflows in Project 2010.


    Demand Management in Project Server 2010


    Developing Project Server Workflows:

    Out of the Box Sample Workflow Source Code:

    Download the SDK: and find the source code in the “Samples\Workflow\SampleProposal2” directory

    Sharepoint Office Task Class:

    Using InfoPath Forms in a Workflow:


    Introduction to Demand Management:

    End User level Walkthrough of the Out of the Box Sample Workflow:

    How to use the Skip to Stage Feature:

    Debugging a Project Server Workflow in Visual Studio 2010

    Solution Starters (Open Source Solution Starters)

     Microsoft Project 2010 Solution Starters

    Recorded Videos:

    Demand Management Overview:

    Workflow Deep Dive:


    Demand Management Overview:

    Part 1:

    Part 2:

    Part 3:

    Part 4:

    Workflow Deep Dive:

    Part 1:

    Part 2:

    Tailored Tools for Workflow Creation:

    Project Forum:,projectprofessional2010/

    Project Server 2010 Demonstration Virtual Machine Image Download:

    Hitchhiker’s Guide to Demand Management (white paper)

  • Project Programmability and Business Intelligence

    Creating Deliverable Reports


    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:

    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:

    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:


    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.

    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.

    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.

    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.

    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


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

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


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


    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:

    	ProjectName As 'Project Name', 
    	Title As 'Deliverable', 
    	StartDate As 'Start Date', 
    	FinishDate As ' Finish Date'
    	Inner Join MSP_EpmProject_UserView 
          	On 	MSP_WssDeliverableToProjectLinks_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.

    	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.

    	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:

    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



  • Project Programmability and Business Intelligence

    Update of Project 2010 SDK–Online and Download


    The Project 2010 SDK download and the MSDN online release are both updated. The updates have the same URLs as previous releases:

    New conceptual / how-to topic:

    ·Topic updates for MSDN online, since the last update on March 7, 2011:
    20 conceptual and how-to topics have updates. Most changes are relatively minor; the Change History table at the bottom of each topic shows significant changes.

    • Assn Element
    • ChangeList Elements
    • ChangeList Schema Reference
    • Developing Project Server Workflows
    • How to: Create a Project Server Event Handler and Log an Event
    • How to: Create a Proxy Assembly for WCF Services (SP1 note)
    • How to: Modify the Ribbon in PWA
    • Introduction to the ChangeList Schema and Statusing ChangeXML
    • Introduction to the SetAssignmentWorkData Schema
    • Prerequisites for ASMX-Based Code Samples (SP1 note)
    • Prerequisites for WCF-Based Code Samples (SP1 note)
    • Project 2010 SDK Documentation (overview of the Project 2010 SDK)
    • Project Server 2010 Programming Tasks
    • Project Server Error Codes
    • SetAssignmentWorkData Elements
    • SetAssignmentWorkData Schema Reference
    • Supported Project Fields and Field Information for Statusing ChangeXML (updated the valid change types for the Actual Overtime Work and Remaining Overtime Work fields)
    • Tables of VBA Object Model Changes
    • Walkthrough: Developing PSI Applications Using WCF
    • What's New for Developers in Project 2010 (programmability changes for SP1)

    600 managed code types (classes that include new descriptions for one or more properties, methods, and events) are updated. There are new code samples for the following PSI methods:

    • QueueDeleteProjects
    • ReadResource
    • UpdateStatus
    • ReadProjectStatus
    • SubmitStatusForResource
    • ReadStatusForResource
    • ReadEventHandlerAssociationsForEvent
    • UpdateEventHandlerAssociations
    • CreateEventHandlerAssociations

    New / updated items in the Project 2010 SDK download, which was last updated March 7:

    • Project2010SDK.chm is an HTML Help file that includes the same updated content that is online. In the managed code reference section, 99.8% of the types and members now have descriptions (26,530 out of a total 26,576 topics). That is up from 77.7% at RTM.
    • WINPROJ.DEV.hxs remains unchanged from the March update of VBA Help. There are instructions for replacing the local VBA Help file that was shipped with the Project RTM release.
    • IntelliSense files are updated for the PSI proxy assembly and the Project Server assemblies, to show descriptions of classes and members while programming in Visual Studio. The type and member descriptions have the same updates as in the HTML Help file (and in MSDN online).
    • The Microsoft.Office.Project.Server.Library.dll assembly is updated for distribution with third-party solutions for SP1.
    • Event handler solution: TestProjectEventHandlers.
    • Test application for the Queue System: UsingQueueSystem shows an example of when to wait for the queue, and when you don’t need to wait.
    • ProjTool has a minor update, so the functionality of the Project Details dialog matches the description in Using the ProjTool Test Application.
    • New PSI code samples include complete WCF-based solutions for the following events: ProjectEventReceiver: OnCreating, OnCreated, OnSaved. Other new solutions show the use of the following PSI methods: QueueDeleteProjects, CreateEventHandlerAssociations, UpdateEventHandlerAssociations, ReadEventHandlerAssociationsForEvent, using the ReadResources and ReadResource methods to get the RBS custom field, creating a changeXml parameter for a different resource with the UpdateStatus method, and using SubmitStatusForResource and ReadStatusForResource.
  • Project Programmability and Business Intelligence

    Installing the Project 2013 SDK download on Windows 8


    The Project 2013 SDK download is updated for the RTM release of Project 2013. In addition to articles, references, and code samples that are updated from the July release of Project 2013 Preview, the SDK also includes a local copy of VBA Help for Project Standard and Project Professional.

    You can install the downloaded Project2013SDK.msi file on computers that are running Windows 8, Windows 7 (and a couple of earlier Windows releases), Windows Server 2008 R2, and Windows Server 2012. When you install the SDK on a Windows 7 machine, the SDK contents are accessible from the Start menu. Figure 1 shows, for example, that the Microsoft SDKs folder contains the Project 2013 SDK folder, which contains links to three files. From the hierarchical context of the Start menu, it is clear that the Documentation node is contained in the Project 2013 SDK folder.

    Similarly, if you install the SharePoint 2013 SDK download and the Apps for Office and SharePoint SDK download, they each create a folder in Microsoft SDKs, and each SDK has a Documentation node within its folder.

    Figure 1. Using the Project 2013 SDK from the Windows 7 Start menu


    The problem

    Windows 8 does not have a Start menu, it has two related Start screens. After you install the Project 2013 SDK download, and scroll the main Start screen to the links for the installed files, you can see the same three links as in Windows 7. (To see the Welcome Guide on the Start screen, you can search for Welcome.rtf, open it in Internet Explorer, and then pin Welcome Guide to the Start screen.) But, the Start screen in Windows 8 is not arranged in hierarchical folders. In Figure 2, it is not clear what the Documentation link is for.

    Figure 2. Using the Documentation link to the Project 2013 SDK, on the Windows 8 Start screen


    The problem is worse if you also install the SharePoint 2013 SDK and the Apps for Office and SharePoint 2013 SDK. You would then have three Documentation links and three Welcome Guide links, each of which goes to a different SDK.

    If you right-click one of the Start screen icons, the icon shows a check mark, and the Start screen shows options at the bottom (see Figure 2). If you choose All apps at the bottom right of the screen, Windows 8 shows lists of installed apps within top-level groups. For example, the Microsoft SDKs group contains links for all of the Office, Project, and SharePoint SDKs that you install; there are no subfolders to distinguish which links go to which SDK. In Figure 3, only the Project 2013 SDK is installed, and the links have the same names as in Figure 2.

    Figure 3. Using the Project 2013 SDK links in the Apps view, in Windows 8


    The workaround (for now)

    On a machine with Windows 8, you can install one SDK at a time, and then rename the links on the Start screen, before installing another SDK.

    To install Office, Project, and SharePoint SDKs on Windows 8

    1. Log on to Windows 8 as an administrator.
    2. Install, for example, the Project 2013 SDK.
    3. On the Start screen, right-click the Documentation icon, and then choose Open file location at the bottom of the screen.
    4. On the Windows Desktop, rename the Documentation link as Project 2013 SDK Documentation, and then choose Continue in the File Access Denied dialog box (see Figure 4).

      Figure 4. Renaming the Project 2013 SDK links in the Desktop view
    5. Similarly, rename the VBA Reference link as Project 2013 VBA Reference, and rename the Welcome Guide link as Project 2013 Welcome Guide.
    6. With the mouse pointer in the lower-left corner of the screen, choose the Start pop-up icon, and then scroll to the Project 2013 SDK icons (see Figure 5).

      Figure 5. Using the renamed links in the Start view
    7. Install the Apps for Office and SharePoint 2013 SDK, and similarly rename the Start screen links.
    8. Install the SharePoint 2013 SDK, and similarly rename the Start screen links. Figure 6 shows the Microsoft SDKs group with the renamed links in the All apps view.

    Figure 6. Using the renamed links for all three SDKs in the All apps view


    In future releases, the Office, SharePoint, and Project SDK downloads will be reconfigured so that they install with non-conflicting link names on Windows 8.


  • Project Programmability and Business Intelligence

    Trials and Tribulations in Customizing the Project Center Ribbon


    The How to: Modify the Ribbon in PWA article in the Project 2010 SDK works for the cases in the article, but in other cases, you can get a JavaScript error when you try to hide some ribbon controls in PWA. For example, if you hide the Zoom and Show/Hide groups in the Project Center ribbon, a JavaScript error occurs while debugging the Visual Studio project, and all of the ribbon controls are disabled on that page. The error occurs only for controls in the Show/Hide group.

    Here is the standard Project Center ribbon. The Visual Studio 2010 project that hides the Zoom group and the Show/Hide group is in the attached file.

    Here is the solution for the Project Center ribbon customization issue - at least with three of my machines, all of which have Internet Explorer 9, with both 32-bit and 64-bit IE. I did not test with IE8. The problems may be solved, or may be different, with future updates or different versions of Internet Explorer.

    1. Make sure you are running IE9 RTM (9.0.8112.16421). 

      On my Windows Server 2008 R2 machine, there was an earlier IE9 RC (9.0.8080.16413) build installed. When the ribbon customization feature was deployed, all of the ribbon controls were disabled on the Project Center page. When debugging in IE with F12, the Script tab shows:

      SCRIPT5022: Attempting to attach to Id: Ribbon.ContextualTabs.ProjectCenter.Home.ShowHide but this id is not present in the DOM
      cui.debug.js?rev=seEKIhJXfBClfYcr46fd3w%3D%3D, line 1282 character 13

    2. On Windows Server 2008 R2 (the development machine), open Internet Options in IE. On the Security tab, make sure that Enable Protected Mode is checked. If you change that option, restart IE.
      If the Enable Protected Mode option is not checked, all of the ribbon controls on the Project Center page are disabled, and the script error shows in the F12 IE debugger. With the earlier build of IE9, it didn't matter - the ribbon controls were disabled for Enable Protected Mode checked or unchecked.
    3. Note:   When viewing the Project Center page in IE9 on Windows 7 64-bit, it doesn't matter whether the Enable Protected Mode option is checked or not - the ribbon controls are enabled as expected:
    4. When you are developing the ribbon customization in Visual Studio 2010, test hiding controls one-by-one, before hiding an entire group. Run F5 after each change, to see the effect. That is fairly quick to do by using F5 in Visual Studio. For example, hiding the three Navigate controls works fine in the VS debugger, but hiding either of the ShowHide group controls results in the following error, as shown in the Visual Studio debugger. The error is in the dynamic cui.debug.js script:
      $3v: function() {ULSpEN:;
              var $v_0 = $get(this.$6_0);
              if (!CUI.ScriptUtility.isNullOrUndefined($v_0)) {
              else {
                  throw Error.create('Attempting to attach to Id: ' + this.$6_0 + ' but this id is not present in the DOM');
    5. Break if you get that error, exit IE, and you can still deploy the solution and try it outside of the Visual Studio debugger. In the Solution Explorer, right-click the project, and then click Deploy. In my case, the ShowHide controls were hidden, and the ribbon controls were enabled as expected – so long as the Enable Protected Mode is checked in IE, and IE9 is the RTM build.

    I don't have an explanation of why this happens, but the previous steps are what works (on my machines – the famous WOMM test). You might find different issues, depending on your software versions and whether the Sun is having a coronal mass ejection that day. I suspect it is a combination of problems of the Javascript implementation in the Internet Explorer build version (naturally, it couldn't be Project Server's problem!).

  • Project Programmability and Business Intelligence

    Publishing Projects based off a Project Custom Field Value



    As part of the development process for the next version of Project, we are heavily relying on Project to manage all the work that is happening across team. As part of the process, we require up to date status reports. All the work is spread across 40 project plans, so we cannot rely on PMs publishing projects daily. To ensure the status reports are up to date, we publish all the projects nightly by running a custom application as a scheduled Windows task:



    Since we only want to publish our team's projects and cannot publish other teams projects on the server, we tag all of our team projects with text custom fields that have associated lookup table to ensure that everyone on the team is using the same tags:


    To setup the custom field and lookup tables:

    1. Log onto Project Server as an Administrator
    2. Click on "Server Settings" then click on "Enterprise Custom Field Definition"
    3. Scroll to the bottom of the page and click "New Lookup Table". We created a lookup table called "Office Division", which has all the divisions within Office:

    4. Next, create a custom field, which we called "Office Division", and associate it to the lookup table:


    The application to publish the projects nightly is attached to this post. It is a fairly simple application. At the top of the program class, we have set a few constants. If you wanted to try out this application, you should just have to change these constants and compile the application. These constraints are self explanatory:

    const string ls_projURL = "http://Office/PWA/";     // Server URL
    const string ls_CustomField = "Office Division";    // Custom Field 
    const string ls_LookupTableValue = "Project";       // Value in the lookup table

    Once we had the application built, we simple set up a scheduled task in Windows to run the application nightly:


    Chris Boyd

  • Project Programmability and Business Intelligence

    Creating a Task Hierarchy


    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;


    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;


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


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

    Chris Boyd


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

    Improve the Performance of Building the Project Server Data Analysis Cube


    Jack Li has passed along a great tip on how to improve the performance of building the Project Server Data Analysis Cube: 


    Recently we have worked with a big enterprise customer using Project Server. They ran into an issue where Project Server reporting component which uses Analysis Server ran very slow during cube population. Eventually, the issue was tracked down to a single query.  The query would take 17 hours to finish for 1 year of data. nHowever, if one applied force order to the query, it would finish within minutes.


    Customer had tight deadline and need a solution fast. It would be easy if one could just modify the query to add force order option.  Unfortunately, the query was generated by Analysis Service based on the cube definition. In other words, it couldn’t  be changed.


    Enter into plan guide for the  rescue. SQL Server 2005 gives you various means to influence the query plan for a query without adding hints to the query itself directly. You don’t need to change the query which may not be possible without changing the application.


    The simplest one is the use use sp_create_plan_guide stored procedure to add a query hint like force order etc.


    Here is exactly what we did for our customer:


    1)    We  used profiler trace to trace the exact query

    2)    We copied the exact query text and use sp_create_plan_guide to create the plan guide:


    EXEC sp_create_plan_guide N'guide_forceorder',

        N'<exact query>',




    N'OPTION (force order)'



    Steps are fairly easy yet it is tricky to implement. If you are not doing it correctly, SQL Server may not use the plan guide you just created.  Here are a few things you need to watch for:


    1)    ensure you have the exact text.  Even if you miss a space character, the plan won’t match.   To ensure you get exact text, you should launch  profiler trace while running the application to get the query.   See for more details.


    2)    There are two ways you can verify if SQL actually uses your plan guide. The first one obviously is that your query finishes faster.  Another way is that you will see PlanGuideDB word in the xml plan. So if you do set showplan_xml on and then run the query, you will get xml showplan. If you search the text, you will find PlanguideDB.  


    There are more advanced ways to use plan guide including parameterization or use plan. Please refer SQL Server 2005 books online for these advanced topics.



  • Project Programmability and Business Intelligence

    Debugging a Project Server Workflow in Visual Studio 2010


    The How to: Install and Test a Project Server Workflow article in the Project 2010 SDK needs information about how to debug a workflow. Because Visual Studio 2010 can install a workflow solution on the local Project Server computer during development and testing, you can use Visual Studio to attach to a process that the workflow uses.

    After you deploy the workflow project in Visual Studio, on the Debug menu, click Attach to Process. In the Attach to Process dialog box, check Show processes from all users and Show processes in all sessions.

    Following are considerations to choose the correct process:

    • If the workflow creates a project or does another operation that uses one of the Queue methods in the PSI (for example QueueCreateProject), the process uses the Project Server Queue Service. In the Attach to Process dialog box, click the Microsoft.Office.Project.Server.Queuing.exe processes.

      Note:   There is a queue process for the application server and for each Project Web App instance. You can attach to all the queue processes, or check the ULS log for the ID of the queue process, and then convert that into a decimal value. For example, if the Microsoft.Office.Project.Server ID is 0x0FFC in the ULS log, the Microsoft.Office.Project.Server.Queuing.exe process ID is 4092.
    • When you submit or restart the workflow, the workflow runs in a w3wp process. Click one or more of the w3wp.exe processes . You can sometimes determine which w3wp process is involved, and find the process ID as in the previous note. Otherwise, attach to all of the w3wp processes.
    • If the workflow uses an approval process that includes many approvers, it might run under the SharePoint timer process. Click the OWSTIMER.EXE process . This is not common.

    When you attach to the correct process, a breakpoint remains as a solid red dot in the left of the code pane. If you do not attach to the correct process, the breakpoint turns into a hollow red circle with yellow caution indicator, and the tooltip for the breakpoint states, “The breakpoint will not currently be hit. No symbols have been loaded for this document.”

    When you click Attach, Visual Studio is ready to catch a breakpoint that you set in the workflow code.

  • Project Programmability and Business Intelligence

    Reading Enterprise RBS values: the Easy Way and the Not-So-Easy Way


    There are a couple of ways to programmatically get the Resource Breakdown Structure (RBS) value for a Project Server user. The simplest way is to query the Reporting database, for example:

    SELECT [ResourceUID] ,[ResourceName] ,[ResourceBookingType] ,[ResourceIsActive] ,[RBS] FROM [ProjectServer_Reporting].[dbo].[MSP_EpmResource_UserView] AS res WHERE res.ResourceName = N'Linda Jones'

    The result on my machine is:







    Linda Jones



    User Assistance.DevDocs.SDK Writers

    To get the RBS by using the PSI is a bit more work. Because the RBS is an enterprise resource custom field that uses the RBS lookup table, you can use the following steps:

    1. Call the ReadResources method and filter the primary Resources table for the user name and GUID. Alternately, you can use the ReadUserList method to get all of the active resource names and GUIDs, and iterate through the ResourceDataSet for the specific resource. The attached example uses the –userList command line argument to have the application use the ReadUserList method, and displays the elapsed time for the method used.
    2. Call ReadResource with the resource GUID to get the full ResourceDataSet.
    3. Iterate through the ResourceDataSet.ResourceCustomFields table to get the CODE_VALUE for the RBS custom field (if it exists).
    4. Use a filter with the ReadLookupTables method to get a LookupTableDataSet that contains only the LookupTableTrees table for the RBS lookup table.
    5. Iterate through the LookupTableTrees table to get the LT_VALUE_FULL string that corresponds to the CODE_VALUE in the RBS custom field.  The LookupTableTrees row where the LT_STRUCT_UID element matches the  custom field CODE_VALUE contains the correct RBS string.

    The attached example also writes the various datasets to XML files, for debugging purposes. Keep in mind that the app user must have the ManageUsersAndGroups global permission, and the other permissions specified in the SDK topics for the methods used, in order to read data of other resources.

    The attached file contains the complete Visual Studio solution for the example described in this post. To use the sample, change the server name and Project Server name for the endpoint addresses in the app.config file. The sample uses the WCF interface for Project Server 2010; however, it can be adapted to use the ASMX interface for Project Server 2007.

    For example, the following command gets the same RBS value shown by the Reporting database query:

    C:\Test>ReadRbs -name "Linda Jones"

    XML output from ReadResources:

    Using ReadResources method: 412.309 milliseconds

    XML output from ReadResource:

    XML output from ReadLookupTables:

    User name: Linda Jones
            GUID: 0D455775-01CB-42E1-A481-A6F0F1F8208A
            RBS value: User Assistance.DevDocs.SDK Writers

    Press any key to exit...

  • Project Programmability and Business Intelligence

    Adding a Project to a Category


    Brian Smith from PSS has passed along this sample that we thought might be helpful:

    The scenario here is that you have a lookup table that shows the categories you want users to select from when creating a project, and then the GUID for the "real" security category is held in the description for the lookup table value.  You make the CF that feeds from the Lookup Table a required Project Level text field.  The Project.Created event fires and the dataset is read - the custom field identified and the GUID of the security category is then used to add the project to the security category.

    No error checking or exception handling is shown - you can do this bit. You would also need to set the categories to the rule "only projects...".  I've hardcoded my lookup table and a reference required to the Microsoft.Office.Project.Server.Library and Events. A Web References to LookupTable, Project, Security and LoginWindows is also required.

    The code will run as the user running the services - so you will either need that account to have PWA permissions or to change to use impersonation.

    using System;
    using System.Collections.Generic;
    using System.Net;
    using System.Diagnostics;
    using System.Text;
    using Microsoft.Office.Project.Server.Events;
    using Microsoft.Office.Project.Server.Library;

    namespace TestEventHandler

        public class AutoCategory:ProjectEventReceiver

            public override void OnCreated(PSContextInfo contextInfo, ProjectPostEventArgs e)

                // cfGuid holds CF for Project Category
                Guid cfGuid = new Guid("9bbc698f-5c1d-4f8d-a3d0-163006416bf2");

                // ltGuid holds LT for Categories
                Guid ltGuid = new Guid("625bab60-4427-4f0b-941b-9860d1293338");

                // lt_Struct_Uid gets the id for the selected LT value
                Guid lt_Struct_Uid = new Guid("00000000-0000-0000-0000-000000000000");

                // securityCategoryGuid gets the Security Categorty Guid from the Descriptio field in the lookup table
                Guid securityCategoryGuid = new Guid("00000000-0000-0000-0000-000000000000");

                // 32 is used to just get the CF entities from the readProjectEntities
                const int PROJECT_ENTITY_TYPE_PROJECTCUSTOMFIELD = 32;

                Guid SECURITY_CATEGORY_OBJECT_TYPE_PROJECT = new Guid("1771B1C0-6E26-4FB3-A480-C798AB506E82");

                WebSvcLoginWindows.LoginWindows loginWindows = new TestEventHandler.WebSvcLoginWindows.LoginWindows();
                WebSvcProject.Project project = new TestEventHandler.WebSvcProject.Project();
                WebSvcSecurity.Security security = new TestEventHandler.WebSvcSecurity.Security();
                WebSvcLookupTable.LookupTable lookupTable = new TestEventHandler.WebSvcLookupTable.LookupTable();


                //login to Project Server - this assumes the event service has a login with permissions

                // Impersonation would be better
                loginWindows.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/LoginWindows.asmx";
                loginWindows.Credentials = CredentialCache.DefaultCredentials;

                // Get the dataset
                project.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/Project.asmx";
                project.Credentials = CredentialCache.DefaultCredentials;

                lookupTable.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/LookupTable.asmx";
                lookupTable.Credentials = CredentialCache.DefaultCredentials;

                security.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/security.asmx";
                security.Credentials = CredentialCache.DefaultCredentials;

                WebSvcProject.ProjectDataSet dsProjectDataSet = new TestEventHandler.WebSvcProject.ProjectDataSet();

                dsProjectDataSet = project.ReadProjectEntities(e.ProjectGuid, PROJECT_ENTITY_TYPE_PROJECTCUSTOMFIELD, TestEventHandler.WebSvcProject.DataStoreEnum.WorkingStore);

                for (int i = 0; i < dsProjectDataSet.ProjectCustomFields.Count; i++)
                    if (dsProjectDataSet.ProjectCustomFields[i].MD_PROP_UID == cfGuid)
                        lt_Struct_Uid = dsProjectDataSet.ProjectCustomFields[i].CODE_VALUE;

                Guid[] arrayLtUid = new Guid[1]{ltGuid};
                WebSvcLookupTable.LookupTableDataSet dsLookupTable = new TestEventHandler.WebSvcLookupTable.LookupTableDataSet();
                dsLookupTable = lookupTable.ReadLookupTablesByUids(arrayLtUid, false, 1033);

                for (int i = 0; i < dsLookupTable.LookupTableTrees.Count; i++)
                    if (dsLookupTable.LookupTableTrees[i].LT_STRUCT_UID == lt_Struct_Uid)
                        securityCategoryGuid = new Guid(dsLookupTable.LookupTableTrees[i].LT_VALUE_DESC.ToString());

    WebSvcSecurity.SecurityCategoriesDataSet dsSecurityCategories
       = new TestEventHandler.WebSvcSecurity.SecurityCategoriesDataSet();

    // Read the existing values for the security category into the dataset
    dsSecurityCategories = security.ReadCategory(securityCategoryGuid);

                // Get a new objects row to put the created project into

                WebSvcSecurity.SecurityCategoriesDataSet.SecurityCategoryObjectsRow dsSecurityCategoryObjectsRow
    = dsSecurityCategories.SecurityCategoryObjects.NewSecurityCategoryObjectsRow();

                //Set the values
                dsSecurityCategoryObjectsRow.WSEC_OBJ_TYPE_UID = SECURITY_CATEGORY_OBJECT_TYPE_PROJECT;
                dsSecurityCategoryObjectsRow.WSEC_CAT_UID = securityCategoryGuid;
                dsSecurityCategoryObjectsRow.WSEC_OBJ_UID = e.ProjectGuid;

                // Add the row to the dataset and then pass to SetCategories to update

                // Create an EventLog instance and assign its source.
                EventLog myLog = new EventLog();
                myLog.Source = "Project Event Handler";

                // Get information from the event arguments, and

                // write an entry to the Application event log.
                string userName = contextInfo.UserName.ToString();
                string projectName = e.ProjectName.ToString();
                string secCatUid = securityCategoryGuid.ToString();
                int eventId = 3652;
                string logEntry;


                logEntry = "User: " + userName +

                        "\nProject: " + projectName +

                        "\nSecurity Category Uid: " + secCatUid;

                    myLog.WriteEntry(logEntry, EventLogEntryType.Information, eventId);




Page 2 of 11 (255 items) 12345»