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.
 
 

January, 2008

  • Project Programmability and Business Intelligence

    Implementing My Tasks “Read Only Custom Fields”

    • 1 Comments

    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.

    image

    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.

       image
    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.

      image

      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)

    Finally…

    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

    Statusing Transactions (Partial Documentation)

    • 7 Comments

    Introduction

    Unlike the project Web Access “My Tasks” web part, the Statusing web service API restricts team member task assignment updates that combine with the create/reassign action to one per approval cycle. This means that once a create/reassign plus updated status is submitted it cannot be further updated until accepted or rejected by the Project Manager.

    This limitation is caused by the absence of “Node Consistency” functionality in the API. This component ensures that the task is kept schedule consistent during team member updates. The project Server 2007 architecture placed this component on the Project Web Access web front end server, rather than in the Project Server Interface business object layer, so as to deliver a responsive user experience in the UI.

    Unfortunately the application developer who is tasked with synchronizing status with an external system (such as an ERP or CRM system) has no means to detect this “blocked” status. We plan to add such a method in an upcoming release of Project Server. In the interim this documentation can be used to extend the Project Server Interface to add a web service/method to deliver the status update status.

    Note: This document 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.

    Extending the Project Server Interface

    This is documented in the following Project Server 2007 Software Development Kit article.

    http://msdn2.microsoft.com/en-us/library/bb428837.aspx

    Note that any code will be required to connect to the Project Server 2007 Published database – in a single site farm this may safely be hard coded, however in a multi-site farm it is recommended that the developer implement a method for connecting to the correct published database, suggestions include:

    • Using the SharePoint site-id to index into an INI file with the correct connection string stored as an external string
    • Passing the database (and SQL Server name) as method arguments.

    Understanding the MSP_ASSIGNMENT_TRANSACTIONS Table

    All status updates are stored in the MSP_ASSIGNMENT_TRANSACTIONS table together with flags indicating the current state of the update. The flags necessary to locate a blocked task assignment are documented below.

    clip_image002[4]

    There are four ENUM fields that govern state, these are:

    Attribute Value Enumeration
    ASSN_TRANS_STATE_ENUM 0
    1
    2
    Not Submitted
    Submitted Pending Approval
    Approved or Rejected
    ASSN_TRANS_TYPE_ENUM 0
    1
    2
    3
    4
    5
    6
    7
    Task Assignment Update
    Declined Task Assignment
    Create Task Request
    Delegate Task Assignment Request
    Create Task Assignment Request
    Team Delegation Request
    Delete Task Request
    Delete Task Assignment Request
    ASSN_TRANS_ACTION_ENUM 0
    1
    2
    Pending
    Approved
    Rejected
    ASSN_TRANS_ERROR_ENUM 0
    1
    2
    3
    4
    5
    7
    Applied
    Undefined
    Conflict
    ProjectDeleted
    InvalidUpdate; InvalidDelegation
    InternalError
    Count

    So a blocked task assignment and will have ASSN_TRANS_STATE_ENUM = 1 (pending approval) and ASSN_TRANS_TYPE_ENUM = 2,3,4 (indicating a pending task assignment add/change)

    SQL Server query to test the status of a particular task assignment:
    SELECT TOP 1 ASSN_TRANS_STATE_ENUM 
    FROM MSP_ASSIGNMENT_TRANSACTIONS 
    WHERE ASSN_UID = '617633E6-8B2A-4620-BCAD-F82A95AD398D' -- Assignment UID 
    AND ASSN_TRANS_STATE_ENUM=1 
    AND ASSN_TRANS_TYPE_ENUM IN (2,3,4)
    Notes:

    If the task assignment status update is not blocked then no data will be returned; if it is blocked then a scalar value of integer 1 will be returned.

    See comments in the performance section before changing this query.

    Security Considerations

    PSI Extensions are responsible for implementing their own security. In this case there are mitigations which mean that additional permissions checks may be unnecessary:

    • All callers must be authenticated users making anonymous DoS attacks impossible
    • The proposed solution returns a single numeric result that indicates if the assignment is blocked (1) or not (null)
    • The argument is a UUID – these are very hard to predict/guess so it isn’t possible to cycle through a sequence hunting for hits
    • If an attacker discovers that a particular assignment UID is blocked then there is little they can do with that information
    • The call is extremely performant

    Note that it would be simple to make the call as the “interface user” and test for that in the PSI extension should further restrictions be required.

    Performance Notes

    The above query has been optimized for performance – strictly speaking the query is stronger than required as only the second task assignment status update is blocked by the pending approval. However checking for this first pending update would require the addition of a self-join to the query which would reduce performance considerably. This would test for the above condition AND a pending task assignment status update (ASSN_TRANS_TYPE_ENUM = 0). Provided the work is added in the same ChangeXML payload that changes the task assignment, it is superfluous.

    It is recommended that a query covering index be created on the MSP_ASSIGNMENT_TRANSACTIONS table to avoid both clustered index scans, secondary index scans or bookmark lookups as the table is indexed on a non-sequential UUID (so data access will be very random).

    The following index definition is suggested:

    CREATE NONCLUSTERED INDEX [CUSTOM_STATUS_SYNCH] ON [dbo].[MSP_ASSIGNMENT_TRANSACTIONS] 
    ( 
      [ASSN_UID] ASC, 
      [ASSN_TRANS_STATE_ENUM] ASC, 
      [ASSN_TRANS_TYPE_ENUM] ASC 
    ) ON [PRIMARY]
    

    The sample query (above) uses a TOP 1 clause to further reduce the SQL Server results processing (either no rows or a scalar result set will be returned).

    If you alter the query to return more data, or to use different search arguments then this index definition might need to change.

    Note that creating a stored procedure and using output parameters would further improve performance.

     

    Patrick Conlan

  • Project Programmability and Business Intelligence

    Reading Assignment Enterprise Custom Field Values with VBA

    • 2 Comments

    Hello,

    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:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=cec3e1e2-d802-4a03-bc78-05c48472559b&displaylang=en

    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

    Reporting Database Extensions: Local Custom Fields Custom Code

    • 2 Comments

    Introduction

    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 RDB.zip)

    • 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

    Customization

    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

    Installation

    ** 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:

      clip_image002

      Assembly name field sample:

      PostReportProject,Version=1.0.0.0,Culture=neutral,PublicKeyToken=e0fd07f9ec9c9c3f
    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: http://msdn2.microsoft.com/en-us/library/ms469450.aspx)

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

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

    X86: http://www.microsoft.com/downloads/details.aspx?FamilyID=fe6f2099-b7b4-4f47-a244-c96d69c35dec&DisplayLang=en

    X64: http://www.microsoft.com/downloads/details.aspx?familyid=1AEF6FCE-6E06-4B66-AFE4-9AAD3C835D3D&displaylang=en

  • Project Programmability and Business Intelligence

    Tied Mode between Timesheet & My Tasks Solution Starter

    • 1 Comments

    Bonjour,

    An new version of the popular Tied Mode between Timesheet & My Tasks solution starter was published on CodePlex today:

    Project Server 2007 Timesheet Tied-Mode Service and Event: http://www.codeplex.com/AutoStatusService

    This new version give you the ability to import saved Timesheets into My Task in an asynchronous manner. For instance all TS imports could be scheduled during off peak hour at night for instance.

    The functionality is the same as the initial solution starter; how and when the updates are processed is what changed. This new version significantly improves the timesheet queue throughput as well as the overall system stability by reducing the load on Statusing.

    A special thank you to Mike Shughrue and Steven Haden from the EPM WW COE for creating and testing this solution starter. 

    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&A

    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 point them to CodePlex as they have to agree on the license terms

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

    A: No, but he can point his customer to the website to download it, so he makes sure that the customer agrees with the license terms.

    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: epmssdev@microsoft.com

    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: http://www.codeplex.com/Project/ProjectDirectory.aspx?ProjectSearchText=epm

    Regards,

    Christophe Fiessinger

  • Project Programmability and Business Intelligence

    Getting the PSI URL from an Event Handler

    • 3 Comments

    Hello,

    There have been a few question with regards to how to get the PSI URL from an event handler. To get the URL, you will need to call into the SharePoint object model. So the first step is to create a reference to it:

    using Microsoft.SharePoint;

    Then you are going to need to create a SPSite object, passing in the Site GUID which is a property of the ContextInfo object that is passed into the event handler:

    SPSite ss= new SPSite(contextInfo.SiteGuid);

    From the SPSite object, you can build up the URL to the PSI:

    string.Format("{0}/_vti_bin/psi/{1}.asmx",ss.Url, wsName);

    For impersonation, you will want to use:

    string.Format("{0}//{1}:56737/{2}/psi/{3}.asmx", ss.Protocol, ss.HostName, sspName, wsName);

    Chris Boyd

  • Project Programmability and Business Intelligence

    Project 2007 Resource Kit

    • 1 Comments

    Hello,

    I just wanted to point everyone to our other team blog:

    http://blogs.msdn.com/project/archive/2008/01/25/project-2007-project-resource-kit.aspx

    The latest post is about the release of the Project 2007 Resource Kit. There are a lot of useful tools in the resource kit that you might want to check out!

    Chris

  • Project Programmability and Business Intelligence

    Scheduled Backup Settings

    • 1 Comments

    Hello,

    I was working on some SDK documentation when I realized that programmatically setting the schedule for the backup of project server entities would be difficult without reading the source code. So I have created a simple example that duplicates the PWA Schedule Backup UI:

    image

    The example that I have written is a simple Win32 application that duplicates the functionality (minus the project retention policy):

    image 

    In order to set the schedule, you need to work with the Archive Web Service. Like other PSI Web Services, it follows the CRUD (Create, Read, Update and Delete) model. It is very simple to read and update the schedule; you just need to call ReadArchiveSchedule() to get the current schedule and UpdateArchiveSchedule(...) with and updated data set to update the schedule. The part that I needed to read the source code to figure out, was how to set entities to be scheduled to be backed up or not and how to set the time for when the backup should occur.

    Each entity has a data column in the Archive Schedule data table that stores the scheduled time in ticks:

    • WADMIN_CATEGORY_GROUP -
    • WADMIN_ENTERPRISE_CUSTOM_FIELDS
    • WADMIN_ENTERPRISE_GLOBAL
    • WADMIN_ENTERPRISE_RESOURCE_POOL
    • WADMIN_PROJECTS
    • WADMIN_SYSTEM_SETTINGS
    • WADMIN_VIEW_DEFINITIONS

    To schedule the entity for backup, you need to calculate the number of ticks. To do this, you are going to want to to add the hours and minutes to the today's date and then convert that to ticks. I did that with the following line of code:

    dT = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, hours, minutes, 0);
                       
    row[itemDBCols[i]] = dT.Ticks;

    To set it to never backup the entity, simply set the number of ticks to 0.

    Once you have the ticks calculated, just set the appropriate data column in the Archive Schedule data table and return the data set to the server using the UpdateArchiveSchedule(...) method.

    See the full source code attached,

    Chris Boyd

Page 1 of 1 (8 items)