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

    Getting a Project GUID on the Cheap


    Many people have requested for a complementary method to GetProjectNameFromProjectUid where they can pass in a project name and it returns the project's GUID. Unfortunately it did not make it into the Project API. Since it has been requested many times, I figured that it would be useful to post a method, GetProjectUidFromProjectName, which did just that:

    public static Guid GetProjectUidFromProjectName(string projectName)
      Guid projectGUID;

      WSProject.ProjectDataSet readProjDs = projWS.ReadProjectStatus(

      if (readProjDs.Project.Rows.Count == 1)
        projectGUID = new Guid(readProjDs.Project[0].PROJ_UID.ToString());
        throw new Exception("No Project by the name: " + projectName + " Found");

      return projectGUID;


    First thing to note is that projWS is defined elsewhere and it is a connection to the Project Web Service. See my earlier post on Getting Started with the PSI on how to make the connection to the Project Web Service.

    To get the GUID we are going to use the ReadProjectStatus method, as it is the cheapest call to make to get the project's GUID. As you will see, this method takes 4 parameters. The first parameter is the project GUID. In this case, we pass in an empty GUID because that is what we are looking for. Passing in an empty GUID tells Project Server not to search for a project by the GUID.  

    The second parameter is the store to get the project GUID from. Here we have the option of getting the GUID from the working, published or archived store. In this example, I am getting it from the working store, since I want to get the GUID for projects that are actively being worked on and may or may not have been published. Note that this will also get projects that have been published, since they exist in both stores with the same GUID. You may want to change the store based on your requirements.

    The third parameter is the name of the project. This is passed into our GetProjectUidFromProjectName method.

    The last parameter is the type of project. There a number of different project types:

    In most cases 0, which represents standard projects, will be the correct project type.

    The ReadProjectStatus returns a project dataset. If the number of rows returned for the project table is equal to 1, than we have found a project with the given name and we can return the GUID for the project. If the number of rows is not equal to one, than no project by the name was found and we throw an exception.

    Hope this helps,

    Chris Boyd

  • Project Programmability and Business Intelligence

    Project Server 2007 Timesheet Data Population Tool

    Technorati tags: , ,


    The Project Server 2007 timesheet data population tool has been released on CodePlex:

    The Project Server 2007 Timesheet Data Population Tool enables you to simulate timesheet entries in your farm. This tool can help you perform scalability studies of your PS architecture and validate the sizing of an existing architecture (by measuring timesheet queue throughput for instance). This tool can also be used to test timesheet customization, for instance the Timesheet Tied-mode code sample on Codeplex. This powerful tool should not be run a production environment!

    The Timesheet Data Population tool is a console application written in C#.  It uses standard PSI (Project Server Interface) web service methods to populate timesheets.

    Please refer to the latest version of the Project Server Software Development Kit (SDK) for more information on the PSI methods and how to use them.

    This solution starter was written initially to perform Project Server 2003 vs. Project Server 2007 benchmarks, and has been used since to perform scalability studies and to test timesheet customizations.

    A special thank you to Isabel Bernardos and Steven Haden for helping me write and test the tool during PS 2007 scalability studies for large European customers. 

    If you are aware of any useful tools/solutions for Project and Portfolio Server 2007 and you would like to share them with the community via CodePlex please send me an email.

    About CodePlex

    CodePlex is Microsoft's open source project hosting web site. You can use CodePlex to create new projects to share with the world, join others who have already started their own projects, or use the applications on this site and provide feedback. A word about Microsoft’s role: Microsoft does not control, review, revise, endorse or distribute the third party projects on this site. Microsoft is hosting the CodePlex site solely as a web storage site as a service to the developer community.


    Q: Is the tool supported?

    A: There is no support in terms of CSS/PSS. We expect the support being a CodePlex community effort. Please note that the customization code uses standard supported web service calls available out of the box in EPM2007.

    Q: Is the tool free?

    A: Yes.

    Q: Can I distribute the tool and the source code to customers and partners?

    A: Customers and Partners can use both. Please go to CodePlex to agree on the license terms

    Q: Can a partner distribute the tool and code as is?

    A: No, but partners can point their customer to the website to download it.

    Q: Can a customer install the customization and use it?

    A: Yes the customer can, but he/she is responsible for testing it and running it.

    Q: Can I suggest changes to it?

    A: Yes, join the CodePlex community or send us an email:

    Q: Will this tool be distributed in other ways (i.e. DVDs)?

    A: No.

    Q: What skills do I need to modify or change the tool?

    A: C#, Project Server Interface, and a good understanding of the EPM 2007 data schema.

    Q: I’m trying to modify the code and do have questions. Who do I ask?

    A: Go to the Discussions forums on CodePlex.

    Q: What are all the EPM projects released on CodePlex?

    A. Check this:


    Christophe Fiessinger

  • Project Programmability and Business Intelligence

    Timesheet Solutions for Webcast: Understanding the PSI in Project Server 2010


    The MSDN Webcast: Understanding Project Server Interface (PSI) in Project Server 2010 (Level 300) presentation is available for download, when you register for the event. Several of the sample applications that were demonstrated in the webcast are available in the Project 2010 SDK download. The file that is attached to this blog post includes the two example solutions for timesheets. (Steven Haden and Olivier Laymand, Microsoft Corporation, did the webcast and created the sample code.)

    • MyTsSample shows how to update a  timesheet for a resource, where the timesheet is in single entry mode.
    • TimesheetEventHandler shows how to use impersonation with a TimesheetEventReceiver.OnCreated event handler, to update the timesheet name.
  • Project Programmability and Business Intelligence



    When working with Project Server Events, you may need to reference Microsoft.Office.Project.Server.Schema. Unfortunately, you will not find it in \Program Files\Microsoft Office Servers\12.0\Bin where you can find other common DLLs for Project Server.  For this DLL, you will need to copy it from the GAC:

    1.      Open Visual Studio Command Prompt

    2.      cd %windir%\assembly\GAC_MSIL\Microsoft.Office.Project.Schema\*"

    3.      Copy Microsoft.Office.Project.Schema.dll to a directory outside of the GAC

    4.      Go back to Visual Studio and create a reference to Microsoft.Office.Project.Schema.dll from the directory you copied it to

    Just one thing to keep in mind; you may need to copy the DLL from the GAC again if a Project Server update is installed.

    Hope this helps,


  • Project Programmability and Business Intelligence

    Code samples from the Project 2010 SDK webcast


    The attached file contains three complete Microsoft Visual Studio 2010 projects for code samples that were briefly discussed in the Project 2010 Software Development Kit (SDK) Drilldown webcast:

    • ProjectGuideAddIn:  Uses Microsoft Office development tools (VSTO) in Visual Studio 2010 to add a toggle button to the Project Standard or Project Professional 2010 ribbon, which turns the Project Guide on or off. You can install the compiled add-in by running setup.exe in the ProjectGuideAddIn\publish subdirectory. The add-in uses a hard-coded location of the Project Guide files, C:\PG\DefaultProjectGuideFiles. To install the Project Guide files, install the Project 2010 SDK download, and then extract to C:\PG.
    • ASMXLogon_MultiAuth:  Shows how to make ASMX web service calls to the PSI on a Project Server installation that uses both Windows authentication and Forms authentication. The solution uses the LoginWindows and Project proxy source files, available in the Project 2010 SDK download, and derives Web service classes that override the Web request headers. ASMXLogon_MultiAuth is a complete solution that implements the Using Claims Multi-Authentication discussion in the Prerequisites for ASMX-Based Code Samples article.
    • WCFLogon_MultiAuth:  Shows how to make Windows Communication Foundation (WCF) service calls to the PSI on a Project Server installation that uses both Windows authentication and Forms authentication. The WCF solution does the same job that the ASMX solution does, but uses the ProjectServerServices.dll proxy assembly for the PSI services instead of proxy source files. The Project 2010 SDK download includes a script to compile the proxy assembly from the proxy source files. The WCF solution also uses an App.config file to configure the WCF client endpoints. Because the solution uses the ProjectServerServices.dll proxy assembly, you can see Intellisense descriptions while programming the PSI by using the ProjectServerServices.xml file from the SDK download. WCFLogon_MultiAuth is a complete solution that implements the Using Claims Multi-Authentication discussion in the Prerequisites for WCF-Based Code Samples article.

    The ASMXLogon_MultiAuth and WCFLogon_MultiAuth solutions are useful to compare the differences in developing a basic application with ASMX web services and WCF services for the PSI -- as well as to learn how to handle Project Server installations that use both Windows and Forms authentication.

  • 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

    Project and Portfolio Management (PPM/EPM) VPC v4 – based on Project 2007


    Hi there! Just released the next version of the VPC based on Project 2007. It also includes Visual Studio 2008!


    • New SSRS reports to showcase the upcoming report pack (will be announced later in August)
    • SP2 and April Cumulative Update for Project, Project Server, WSS, and SharePoint Server
    • Usage must adhere to EULA terms: “…You may use the software in the virtual hard disk image only to demonstrate and internally evaluate it.  You may not use the software in a live operating environment…
    • Recommended HW configuration is 2 GB RAM (available for the VPC) and performance Hard Drive

    Hosted Version

    VPC and script are accessible as hosted TechNet Virtual Lab

    Image download

    Please email for download links.


    • Q: Where is the demo script?
      • A: It’s part of the compressed archive. Alternatively you can use the original v2 demo script.
    • Q: Is the SP2 for Project Portfolio Server 2007 installed in this VPC final?
      • A: Project Portfolio Server (PPS) SP2 installed in this VPC v4 is not the version of officially released SP2 for PPS, but on parity feature-wise.
    • Q: Why is the http://epm2007demo URL not in the IE’s Trusted sites anymore?
      • A: Feel free to add the server URL into the IE Trusted sites list, especially if you are demoing the “out-of-the-box” reporting capabilities of Project Server that are using the Office Web Components. Else we discourage you to  do so as you might get prompted for credentials on every time you try to access any sub-site in the http://epm2007demo location.
    • Q: Where can I learn more about Project 2010?



  • Project Programmability and Business Intelligence

    Does your DEMO laptop with Intel Core i3/i5/i7 CPU bluescreens?


    Thanks to my friend Hans Bellen from UMT and the Microsoft Virtualization Team – we have the final solution for your issues:


    Running Windows 2008 R2 w/ Hyper-V on Core i3/i5/i7 CPU and performance graphic card with WDDM driver installed. After Hyper-v is started the machine bluescreens.


    1. Disable the Intel Turbo Boost in the BIOS or in the Windows 2008R2 if you are dual-booting or the option is not available in the BIOS. To disable the Turbo Boost in Windows Server 2008 R2 – open Device Manager, locate Intel(R) Turbo Boost Technology Driver, disable it.
    2. Get the Windows 2008 R2 SP1 – now available in Beta here

    10/20/10 – UPDATE

    I just have been setting up the Lenovo T410 for the Hands-on Lab for our upcoming Barcelona event and here is some learning:

    • After native nVidia driver from Lenovo site was installed the machine constantly blue screened with Hyper-v enabled even with the “Intel(R) Turbo Boost Technology Driver” disabled
    • Installing SP1 did fix the problem and I’m running my VM and multi-monitor config successfully now
    • Small trick I learned – once you install the nVidia driver you are not able to see anything on the screen or the machine blue screens (nvlddmkm.sys) when booted - to recover – just disable the virtualization in the BIOS and reboot. As Hypervisor service will not run the machine will behave ok and it gives you chance to install SP1 Smile
  • Project Programmability and Business Intelligence

    You can use Project with SharePoint Composites, too


    The Guide to creating SharePoint Composites includes a 73-page download that explains SharePoint Composites. To quote from the download, “A SharePoint Composite combines data, documents, and business process in a useful, productive way. … A SharePoint Composite is a ‘do-it-yourself’ business solution.” The SharePoint Composites.docx handbook includes a detailed introduction along with common design patterns that can help provide ideas on how to integrate and present data for collaboration and BI, by using SharePoint.

    Although the Office 2010 integration points section includes only a brief mention of Microsoft Project, the handbook overall is a useful introduction to the concepts. For those who work with Project and Project Server, the handbook can help to expand your view of what is possible – as well as what is useful.  Similar terms you might have heard in the past include mashup and OBAs (Office Business Applications). The SharePoint Composites handbook extends those ideas specifically for SharePoint-based solutions.

  • Project Programmability and Business Intelligence

    Project 2010 Beta Virtual Lab is available as hosted experience in your browser!


    Dear all, please let me share few important resources regarding Project 2010 Beta -

    Preconfigured Demo Virtual Image of Project 2010 Beta

    Hosted “Virtual Lab”

       - This Virtual Lab is the same version of the below downloadable image, but available as hosted experience directly in your Internet Explorer. Please click here to proceed.

       - Please pay close attention to System Requirements – especially if you are in a corporate environment behind firewall!

    Download (requires 64bit OS and Hyper-v)

       - Download includes requirements, demo script and setup instructions - proceed here.

       - Please pay close attention to Setting up the Host Machine document as it includes important steps to restore the Virtual Machine and get going!

       - For optimal performance we recommend 8GB RAM and Solid State Drive (SSD) for your host machine.

    Project Server 2010 Beta – including download

       - Information and download here.

    Project Professional 2010 Beta – including download

       - Information and download here.

    Additional Project 2010 Beta resources

    - Product information


        - Project Team Blog

    - Videos & Project Conference sessions


    IT Professional related

        -  TechCenter @ TechNet

        - Admin Blog

    - Developer related

       - Developer center @ MSDN

       - Programmability blog

    - Additional questions? Project 2010 Forums!


  • Project Programmability and Business Intelligence

    Project 2010 SDK


    The Project 2010 SDK download and the MSDN online release are both published, and the Project Developer Center portal is updated.

    What's New

    Following are the major content sections in the Project 2010 SDK.

    Articles online:

    Note: Some JS Grid articles are currently in the Project SDK, but are updated in the SharePoint Foundation SDK. The basic articles will be removed from the Project SDK in the next update, and we will add Project-specific articles over time. See JS Grid Control and Microsoft.SharePoint.JSGrid Namespace for the JS Grid documentation.


    For the first time, the Project SDK download is installed with an MSI, in [Program Files]\Microsoft SDKs – along with the SharePoint Foundation, SharePoint Server, and Windows SDK downloads.  The installation also creates entries on the Start menu:


    Major content in the download:

    • pj14SDK_beta.chm is an HTML Help file that includes the same conceptual topics and managed code reference that is online.
    • pj14ReportingDB_beta.chm documents the RDB schema.
    • OLAPCubeSchemas2010.xlsx
    • mspdi_pj14.xsd is the Project Standard / Professional XML schema for data interchange.
    • WINPROJ.DEV.hxs is an update of VBA Help that includes the latest changes in the VBA OM documentation and (at last) F1 Help IDs that work right. There are also instructions for replacing the VBA Help file shipped with the Project beta.
    • Intellisense subdirectory: includes updates of the XML files for Intellisense descriptions, instructions for installing, and scripts for creating a PSI proxy assembly using either the WCF or ASMX interface. With a proxy assembly, developers can see Intellisense descriptions for the PSI Web service objects and members.
    • BranchingWorkflow includes the complete Visual Studio 2010 project for creating a Project Server workflow, as described in the online SDK.
    • WCFHelloProject includes a Visual Studio 2008 project that programmatically configures a WCF application for Project Server. It also includes a Visual Studio 2010 project that configures the WCF application using the app.config file, which enables many of the flexible features of WCF (for example, the same app can be configured to run over HTTP SOAP for the Extranet, or with NET.TCP for faster Intranet access – without recompiling).


  • Project Programmability and Business Intelligence

    Visual How To: Creating a Custom Web Parts for Project Server 2007


    We’ve added a “Visual How Tos” section to the online SDK, with the first video:  Creating Custom Web Parts for Project Server 2007.


    A Web Part is a modular unit of information that has a single purpose and is a basic building block of a Web Part Page. Project Web Access uses many Microsoft Office Project Server 2007 Web Parts and can be easily extended with custom Web Parts.

    Web Parts in Windows SharePoint Services 3.0 improve upon earlier versions of Web Part technologies. You can use Windows SharePoint Services 2.0 Web Parts and ASP.NET 2.0 Web Parts. You can also use Web Parts in shared Web Part Page documents in a project workspace or team site. The shared documents are stored and managed on a computer running Windows SharePoint Services that is provisioned by Project Server. This Office Visual How To article shows the creation of a custom Web Part for Project Server 2007 that you can use to display the upcoming tasks for a specified project. The code presented in this article is based on the "No PWA Reference" Web Part sample that is included in the Microsoft Office Project 2007 SDK download.

  • Project Programmability and Business Intelligence

    IE9 Jump List for Project Server


    Alex Burton, a Project MVP in Australia, wrote a blog post, Creating a Internet Explorer 9 Jump List for Project Server.


    Very handy and easy to deploy by a Project Server administrator, to be available to any PWA user with IE9 who pins the PWA landing page to the Windows 7 taskbar.

  • Project Programmability and Business Intelligence

    MSDN Webcast: Project 2010 JS Grid Extensibility: Project Web App


    Pat Malatack (Program Manager, Microsoft Corporation) has a comprehensive series of webcasts on extending the JS Grid in Project Web App.

    Note:  This blog post was originally created Monday, April 19, 2010. The webcasts were missing for the past several months, but have been reinstated on the Microsoft Events site (

    MSDN Webcast: Project 2010 JS Grid Extensibility: Project Web App (Part 1 of 3) (Level 400)
    Tuesday, April 20, 2010
    1:00 P.M.-2:00 P.M. Pacific Time
    This is the first webcast in a three-part series on extending grid pages in Microsoft Office Project Web App. In this webcast, we focus on initializing custom grid code and interactions between the grid and the Project Web App Ribbon. In particular, we show you how to add additional functionality to the Project Center.
    MSDN Webcast: Project 2010 JS Grid Extensibility: Project Web App (Part 2 of 3) (Level 400)
    Thursday, April 22, 2010
    1:00 P.M.-2:00 P.M. Pacific Time
    This is the second webcast in a three-part series on extending grid pages in Microsoft Office Project Web App. In this webcast, we focus on the use of events and delegates to interact with the timesheet grid in a way that allows for the addition of custom features and functionality to that page.
    MSDN Webcast: Project 2010 JS Grid Extensibility: Project Web App (Part 3 of 3) (Level 400)
    Tuesday, April 27, 2010
    1:00 P.M.-2:00 P.M. Pacific Time
    This is the third webcast in a three-part series on extending grid pages in Microsoft Office Project Web App. In this webcast, we walk you through more grid customizations, including adding custom commands to the column header dropdown and custom row header states.

  • Project Programmability and Business Intelligence

    Microsoft Project 2010: Powershell Cmdlets for Project Server PSI


    Mike Shughrue – one of our top Project consultants just published samples on how to use PowerShell in Project 2010 to call PSIs:


    Why would you care? Want to expose additional PSI objects? Then grab this and follow the “theme”. Your IT friends would love you :) As they e.g. can bulk edit or publish all project using a command line :)

  • Project Programmability and Business Intelligence

    Local Custom Fields



    This post contains interim documentation for tables in the Published database of a RTM-edition Project Server 2007 installation. Entities in this schema are subject to change without notice. This documentation is made available on a “best efforts” basis, Microsoft Product Support Services have not been trained in its usage and will not offer technical support for issues related to custom queries against this schema. This schema should not be updated by custom code, any updates to the data may break Project client cache code.

    This post contains two sections:

    - Schema fragments, showing the core tables required to traverse local custom field data

    - Sample queries, showing how to extract custom field and lookup table data for a specific project

    Many of the principles of local custom fields were initially developed in earlier Project Server versions and further questions may be answered by the server schema documentation supplied with those versions, for example:

    DOCS/PJDB.HTM // Primary source of custom field schema documentation

    DOCS/PJSVR.HTM // Additional enumerations

    Both of these files are supplied on the Project Server 2003 installation media.

    Joining Publishing and Reporting

    It is recommended that you implement views in the Reporting database that reference the four tables below as well as MSP_PROJECT_RESOURCES (as local resources are aggregated in the Reporting database)

    For example:


    Note that if the Reporting Database has been installed on a separate SQL Server instance that you will have to use a four part name in the views including the linked server name.

    Schema (Fragment)

    This section documents the relevant attributes and entities required to extract local custom field data for reporting purposes.

    This table holds the master list of all local custom fields in use, keyed on the Proj_UID. Obtain the MS_PROP_UID (which keys the custom field values in the next two tables) from the MD_PROP_NAME or by using the MSP_WEB_VIEW_FIELDS table (get the WFIELD_UID that corresponds to the WFIELD_NAME_OLEDB of the local custom field that you want) – we use “Magic GUIDs” to identify local custom fields (ie 000039b7-8bbe-4ceb-82c4-fa8c0b400033 is always TaskText1)

    This table contains the values (or a pointer to a lookup table value) for task local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

    Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry MD_LOOKUP_TABLE_UID in the previous table data is held in the appropriate *_VALUE column.

    This table contains the values (or a pointer to a lookup table value) for resource local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

    Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry, MD_LOOKUP_TABLE_UID in the Project Custom Fields table, data is held in the appropriate *_VALUE column.

    This table contains lookup table values pointed to by the previous two tables. Assuming either text or hierarchical selection the LT_VALUE_TEXT and LT_VALUE_FULL are most interesting. (_FULL contextualizes the selection and corresponds to the value stored in the enterprise equivalents in the Reporting database.

    Working With Project Task and Resource Custom Fields

    This section contains a series of queries to get you started. These all return tables that can be joined (use an outer join to ensure that data doesn’t get stripped) onto our Reporting USERVIEW views to obtain custom field data.

    Note 1: I’m assuming that the majority of data will be in text custom fields or outline codes, the *_custom_field_values tables contain other buckets for the other field types.

    Note 2: I haven’t qualified these query snippets with a PROJ_UID so they will return all data, you should remember to add this field for project-specific reports otherwise performance will be sub optimal.

    Note 3: I’ve packaged the resource outline code query in two ways, the second as a sample function to make usage a little bit simpler, as the function abstracts out all the joining to get the values.

    Join Table of Task_UID and a text Custom Field
    SELECT task.task_uid, tlcf.text_value 
    FROM dbo.msp_tasks AS task 
    INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
    ON (task.task_uid = tlcf.task_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'CustomFieldName'
    Join Table of Res_UID and a text Custom Field
    SELECT res.res_uid, rlcf.text_value 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'RescourceCustomFieldName'
    Join Table of Task_UID and an Outline Code
    SELECT task.task_uid, tloclt.lt_value_full, tloclt.lt_value_text 
    FROM dbo.msp_tasks AS task 
    INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
    ON (task.task_uid = tlcf.task_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS tloclt 
    ON (tlcf.code_value = tloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'TaskOutlineCodeName'
    Join Table of Res_UID and an Outline Code
    SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
    ON (rlcf.code_value = rloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'RescourceOutlineCodeName'
    Sample Function for Ease of Use
    CREATE FUNCTION dbo.Custom_ResOC (@OutlineCodename NVARCHAR(50)) 
    RETURN (SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
    ON (rlcf.code_value = rloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = @OutLineCodename) 
    -- Can be used in a join clause
    SELECT * FROM dbo.Custom_ResOC(N'ResourceOutlineCodeName') 

  • Project Programmability and Business Intelligence

    Statusing for Manually Scheduled Tasks in Project 2010 SP1


    Service Pack 1 (SP1) for Project Server 2010 enables you to use the UpdateStatus method to edit timephased data for manually scheduled tasks. Before SP1, you could update status only for automatically scheduled tasks. The attached file contains the Visual Studio 2010 solution that will be included in the next update of the Project 2010 SDK download.

    The UpdateStatus_ManualTasks example is a test application that does the following, for a manually scheduled task or an auto-scheduled task:

    • Parses the project name, task name, number of hours worked, the optional assigned resource, and an optional status comment.

    • If no resource is specified, assumes the application user is assigned to the task.

    • Gets the GUID for the user assigned to the specified task.

    • Gets and validates the assignment data. Assumes only one resource is assigned.

    • Creates the changeXml string for the UpdateStatus method. For a simple test, converts reported hours to percent complete (maximum 100). If the application user specifies a different resource, adds the ResID attribute to the Assn element in the changeXml string.

    • Calls UpdateStatus, and then calls SubmitStatusForResource, for the single assignment.

    • Calls ReadStatusForResource to get an updated StatusingDataSet.

      The application also writes the datasets and the changeXml value to XML files, for use in testing.

    Note:  Until you install SP1, the UpdateStatus test application will work only with automatically scheduled tasks. SP1 will be available the end of June, 2011.

    To use the UpdateStatus test application, do the following:

    1. Use Project Professional to create a test project, add two manually scheduled tasks, set the task duration and start date, and then add yourself and one other user as resources. Assign one task to you and the other task to the other user.

      For example, name the project Test project, name the tasks T1 and T2, and then set the duration of each task to three days. Assume your user name is User 1, and the other user is User 2. Assign T1 to User 1 and assign T2 to User 2.

    2. In the app.config file, change the WCF endpoint address for the Project Web App instance you are using.

    3. Run tests, using various parameters. See the Usage method in the attached Program.cs file for parameter information. For example, run the following tests in a Command Prompt window:

      • UpdateStatus -p "Test project" -t "T1" -hours 6 -c "This is a comment"

        The output shows:
        Updating status for User 1 on task 'T1': 6 hours
                Manually scheduled task
      • UpdateStatus -p "Test project" -t "T1" -hours 6 -r "User 2" -c "This is a comment"

        User 2 is not assigned to task T1, so the output shows:
        The assignment on task 'T1' is for User 1, not for User 2
      • UpdateStatus -p "Test project" -t "T2" -hours 6 -r "User 2" -c "This is a comment"

        The output shows:
        Updating status for User 2 on task 'T2': 6 hours
                Manually scheduled task

    After each test, check the Approval Center in Project Web App for status updates.

    When you add a manually scheduled task that does not have a start date or duration, the default is eight hours of work. You can still assign a resource to the task and update the status.

    For example, if a task T3 has no start date or duration, and you use the UpdateStatus test application to set six hours of work for the assigned resource, Project Server sets the start date for T3 to the project start date, and then adds six hours of actual work. After accepting the status update, you can add the Actual Work column and the Remaining Work column to the Gantt Chart view in Project Professional . The work for T3 is eight hours, actual work is six hours, and remaining work is two hours.


    If you add the Actual Work row to the Details pane in the Resource Usage view or the Task Usage view, you can also see the six hours of actual work.


    For more information about changes in SP1, see Announcing Service Pack 1 for Microsoft Project & Project Server 2010 in the Project team blog.

  • Project Programmability and Business Intelligence

    Upcoming WebCast: Project 2010 Virtual Image, Getting Started Quickly


    Join us with Ben Tuesday - we will be showing and talking about the “Project 2010 Demonstration and Evaluation Installation Pack” – WORLD’S premiere! You don’t want to miss that :)

  • Project Programmability and Business Intelligence

    Adding a Web Reference Using Visual Studio Orcas Beta 2


    Lately I have been playing around with VS Orcas Beta 2; primarily to play around with VSTO support for Project Client. When trying to add a web reference to our web services (PSIs), at first I could not find the option to do it in the VS IDE. After digging around, I figured it out. Here are the steps to adding a web reference to one of the PSI web services in Visual Studio Orcas Beta 2:

    1. Right Click on Service Reference and Select Add Service Reference...


    2. Click the Advanced Button:


    3. Click the Add Web Reference Button:


    This will bring up the familiar web reference UI from Visual Studio 2005 and you can add the web reference just like you did in the past.

    Chris Boyd  

  • Project Programmability and Business Intelligence

    Reporting Database Extensions: Local Custom Fields Custom Code



    This custom solution builds on the Project Server 2007 server-side event model to cache a report-friendly copy of Project Local Custom Fields in the Reporting database. The following data is cached:

    • Task Text Custom Fields (with and without lookup tables) by Project
    • Lookup table values by Project
    • Task Outline Code values by Project

    The design pattern used means that addition of other custom field types is a trivial matter requiring edits to a SQL Server stored procedure, avoiding event code change.

    Some customization is required to install this solution – refer to the “Customization” section.

    Solution Components (Local Custom Fields in

    • PostReportProject – Event Handler for the three Reporting events that fire when handling projects, this is shipped as an executable and as source in Visual Studio 2005 form.
    • CustomLocalCustomFields.sql – Stored Procedure to process local custom fields
    • LocalCustomFieldsTables.sql – Table definitions to hold local custom field data
    • EventParameters.ini – INI file containing RDB connect string


    There are two main customizations required:

    1. EventParamters.ini - RDB Connect String

      Alter the string to point to the RDB database server and database
    2. LocalCustomFields.sql

      Search for “CUSTOMIZE”, there are three occurrences where “[pwa_Published]” should be replaced with the name of your Published database.

      Note that if the Reporting database is on a separate server you will need to define a linked server and use a four part name for the Published tables.

    There is an additional customization – the EventParameters.ini file is expected in the C:\Windows directory – this location can be changed by editing the event handler code (“PostReportHandlers.cs”) and recompiling & re-gac’ing


    ** Do not install the event handler until the SQL Components have been installed and tested, and the .INI file has been placed in (default) C:\Windows directory on each application server **

    It is important that these steps are followed in the order below.

    1. Install Local Custom Field Cache tables (do this once)

      Connect to the RDB and run the LocalCustomFieldsTables.sql to create the cache tables.
    2. Install customized Stored procedure (do this once)

      Connect to the RDB and run the customized CustomLocalCustomFields.sql to create the main stored procedure.
    3. Test SQL Components

      Select a project (you can get its ProjectUID from MSP_EPMProject_UserView in the Reporting database) with local task text custom fields, and/or local task text custom fields connected to a lookup table and local task outline codes.

      Replacing the guid in the statements with the ProjectUID guid run the following commands to test the data in the local custom field cache.
    4. exec Custom_LocalCustomFields 'f799fad0-f896-4731-90ab-2fa740f43e88',2 -- Create (1st publish) 
      select * from dbo.Custom_TextCustomField -- Mix of nulls, text fields and lookup table guids 
      select * from dbo.Custom_OutlineCode -- Mix of nulls & lookup table guids 
      select * from dbo.Custom_ProjectLookupTable -- Contains all the lookup table (value list entries) 
      exec Custom_LocalCustomFields 'f799fad0-f896-4731-90ab-2fa740f43e88',3 -- Normal publish 
      exec Custom_LocalCustomFields 'f799fad0-f896-4731-90ab-2fa740f43e88',1 -- Delete
    5. Install customized INI file

      Copy the customized EventParamters.ini to the (default) C:\Windows folder on each application server (not the SQL Server)
    6. Recompile & GAC Event handler on all application servers

      Make any changes to the event code (e.g. ini file path) and recompile. Copy to each application server. On each application server Start: Run: assembly to load the Global Assembly Cache(GAC) view, copy the PostReportProject.dll into the Assembly view to “gac” it. (See the appendix on Event Handlers for an alternate method using GACUTIL from the Net 2.0 Framework SDK)

      In the assembly view right mouse the installed dll and obtain its key, use this in step 6.

    7. Reporting ProjectChanged
      Reporting ProjectCreated
      Reporting ProjectDeleted

    8. Define Event handler in PWA

      From the Server Settings page, select Server-Side Event Handler Configuration

      For each of the three Reporting events add the event handler, the three events are listed in the figure above.
    9. Sample Event settings:


      Assembly name field sample:

    10. Test the Event Handler (end to end)

      The event handler appendix article explains how to debug event handlers.

    Appendix 1: Working with Project Server Event Handlers

    Project Event Handlers can be installed by a variety of methods – these are documented in the reference article at:

    SDK (contains GACUTIL.EXE and SN.EXE) at:

    ** Note use the OS appropriate version 32 or 64 bit **



  • Project Programmability and Business Intelligence

    Using the Project API with Required Enterprise Custom Fields


    Chris Elwell from the Project Test team wanted me to share the following details around the Project API:


    The Project API (the SOAP methods contained within project) enables the user to create and manage projects on Project Server without the need for the Project Professional client application. Project API functionality includes the ability to create, update, and delete Enterprise Custom Field data within a project; however, the API imposes some restrictions that are not intuitively obvious, especially when the server contains Required Custom Fields.

    We’ll cover each one of the major methods within the Project API and discuss the main concerns related to their use with Enterprise Custom Fields. We won’t discuss every method within the Project API; rather, we’ll focus solely on commonly-used methods which have heavy integration with Enterprise Custom fields. Our discussion focuses on the behavior of regular Text fields; although other Custom Field types may impose additional restrictions, the fundamental concepts will remain largely the same.


    When calling QueueCreateProject, you must provide values for any required project custom fields. If the initial project data set contains any tasks, you must also supply values for any required task Custom Fields. However, the API does not require that you supply values for required resource Custom Fields if you have added local resources to your project. This behavior is consistent with Project Professional 2007.

    Note that the project data set passed into QueueCreateProject is not required to contain a Project Summary Task. If the data set contains no Project Summary Task, one will be created automatically; however, in subsequent calls to QueueAddToProject, QueueUpdateProject, and so forth, you will have to provide any required Custom Field value s for this task.


    When calling QueueAddToProject, it is usually sufficient to add your tasks, resources, or assignments, add any applicable required Custom Field values, and then call GetChanges() on the data set. However, you should first verify that the project stored on the server already meets requirements.

    Specifically, a Project Summary Task must have values for all required task Custom Fields before any modifications to the project can be processed successfully. Project Professional does not enforce this requirement, and Project Summary Tasks that were automatically generated by QueueCreateProject also do not necessarily have values for these fields. If you do not set values for all required Custom Fields on the Project Summary Task, calling QueueAddToProject will fail with a SOAP exception. This applies to any tasks you’ve added, as well.

    QueueAddToProject expects that any “core” Project data must have the “Added” DataRowState. However, it does not enforce this requirement on any of the Custom Fields tables; rather, it uses DataRowState to determine whether the user intends to add, update, or delete values. For example, the only task changes permitted in QueueAddToProject are additions. However, you can modify an existing task Custom Field value, and a call to QueueAddToProject will update the corresponding data on the server.

    Using DataRowState, QueueAddToProject will permit you to add, delete, and modify Custom Field values within a Project. You may not, however, put the project into a state where it is missing any required Custom Field values. As noted, the API will also not permit you to use QueueAddToProject to add data to a project that is already in such a state (missing Custom Field values for the Project Summary task, for example) unless you supply the missing data.


    Similar to QueueAddToProject, this method strictly enforces that all changes to the “core” project data must have the “Modified” DataRowState. As long as you do not violate this restriction, though, you’re allowed to add, delete, or modify Custom Field values as you see fit, and these changes (as in QueueAddToProject) are applied based on their DataRowState. As with the QueueAddToProject method, this method will fail and emit a SOAP exception if the project is already missing required Custom Field data and the data set you’ve sent doesn’t supply the missing information.


    This method simply takes an array of GUIDs containing entities you wish to delete from a project. When you delete one of the “core” entities (such as a task or resource) from a project, the related Custom Field values should be deleted automatically. However, using this method to directly delete Custom Field values has no effect. If you wish to delete a Custom Field value from a project, you must call the DataRow.Delete method on the row you wish to delete, then use GetChanges to produce a data set appropriate for use with QueueAddToProject or QueueUpdateProject.

    Technorati Tags: ,,
  • Project Programmability and Business Intelligence

    Project Server Database Timeout



    Since it is the end of the year, I am slowly working my way through my Outlook tasks to be able to start fresh in the new year. I seemed to have missed posting a change we made to the PSI in the SP1 Rollup. In the SP1 Rollup, we added two new methods to the Admin PSI. One allows you to read the current database timeout and the second allows you to set it:

    • SetDatabaseTimeout(DatabaseTimeoutType.Core, timeout);
    • GetDatabaseTimeout(DatabaseTimeoutType.Core);

    We added these methods because, in some deployments, the default timeout was not long enough. If your Project Server deployment is experiencing database timeouts, you may want to try changing the default timeout. Attached is sample code on how to use these methods.

    Chris Boyd


    Technorati Tags: ,,
  • Project Programmability and Business Intelligence

    Creating a Web Part that Lists Projects with a Specified Custom Field Value


    To list all projects that have a specified value of a custom field, the easiest solution is to develop a PSI extension that queries the Reporting database (RDB) in Project Server 2010. When you install the attached file, the ListProjectsWebPart_JSGrid\ListProjects_PSIExtension subdirectory includes complete code samples to create and test the ListProjects PSI extension. The code sample in the ListProjectsWebPart_JSGrid\ListProjects_WebPart subdirectory creates the ListProjects Web Part that calls the PSI extension and displays a list of projects in a JS Grid control.

    The same ListProjects Web Part can be used in Project Web App pages, project detail pages (PDPs), and project site pages (a project site was called a project workspace in Project Server 2007).

    The Web Part to List Projects with a Custom Field Value.docx article in the attached is a draft of procedures that will be included in new articles in the next update of the Project 2010 SDK.

  • Project Programmability and Business Intelligence

    Update of Impersonation Article and Code for Project Server 2010


    Because there have been several recent changes in the post-beta version of Project Server 2010 and Office SharePoint Server 2010, the article How to: Use Impersonation with WCF in the January 2010 release of the SDK is not correct for the 4730.1010 and later builds of Project Server. The article in the March 2010 release of the SDK is correct. The attached file includes the following:

    • Revised article: How to: Use Impersonation with WCF.docx
      • Changes required to use a Project Server installation that has multi-authentication (both Windows and Forms authentication)
      • Procedures for setting permissions for impersonation (requires permissions in both the SharePoint Service application and in Project Web App)
    • Complete Visual Studio 2008 solution (can be opened and converted with Visual Studio 2010)
    • Proxy files for the PSI services (build 4730.1010), including:
      • Scripts to update the proxy files on a Project Server computer and build a PSI proxy assembly
      • Intellisense files
      • Temporary web.config file and instructions, to use when updating the proxy files


  • Project Programmability and Business Intelligence

    New MSDN Article: Using Project Server Security in SQL Server Reporting Services (SSRS) Reports


    Do you have a desire to secure the data from Reporting database based on user rights in the Project Server? You might be interested in this:


    Big thanks to Stephen C. Sanderlin, MSProjectExperts, who wrote this article and extensive code sample. It’s an excerpt from his forthcoming book, Developer's Guide to Microsoft Project Server 2010.


    High-level logic overview of ReportingPSISecurity

Page 3 of 11 (255 items) 12345»