January, 2008

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

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

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

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:

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

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

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:

For impersonation, you will want to use:

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

Chris Boyd

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)

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

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.

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

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

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.

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

4. Install customized INI file
   
   Copy the customized EventParamters.ini to the (default) C:\Windows folder on each application server (not the SQL Server)
   
5. 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.
   
   

Reporting	ProjectChanged
Reporting	ProjectCreated
Reporting	ProjectDeleted



6. 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.
   
7. Sample Event settings:
   
   
   
   Assembly name field sample:
   
   PostReportProject,Version=1.0.0.0,Culture=neutral,PublicKeyToken=e0fd07f9ec9c9c3f
   
8. 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