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

    Silverlight Blueprint for SharePoint

    • 1 Comments

    Hey,

    If you are interested in creating PWA webparts that make use of Silverlight, I suggest you take a look at the "Silverlight Blueprint for SharePoint" which was just released last night:

    For some more general information on SilverLight:

    Chris Boyd

  • Project Programmability and Business Intelligence

    Custom Field and Lookup Table Webcast

    • 6 Comments

    Hello,

    Attached is my source code and power point presentation of today's webcast on custom fields and lookup tables:

    • CustomFieldCRUD - This a very simple application that shows how to create, read, update and delete custom field definitions on Project Server 2007 using the PSI.
    • PublishProjects - This is an application that was written for our internal dogfood effort. We use this application to publish all the Project team's project plans on our Project Server. It shows how to read project custom fields and lookup table values from the Project PSI.
    • Custom Fields and Lookup Tables - This is a PDF version of the slide deck used for the presentation.

    We will be publishing the presentation to the web in the next few days. Once it is available, I will post a link to it.

     

    Chris Boyd

     

  • Project Programmability and Business Intelligence

    Project Server 2007 Timesheet Data Population Tool

    • 1 Comments
    Technorati tags: , ,

    Bonjour,

    The Project Server 2007 timesheet data population tool has been released on CodePlex: http://www.codeplex.com/EPMTSdatapop

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

    Project Portfolio Server 2007 SP1

    • 1 Comments

    Hello,

    The Project Portfolio Server 2007 SP1 is now available:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=7cf44f2c-49d5-4431-bd3e-a10035ec1a39&displaylang=en

    For a list of what is fixed in SP1, check out:

    http://support.microsoft.com/?kbid=942722

    Chris

    Tags:

  • Project Programmability and Business Intelligence

    Project Initiation Phase using InfoPath & Forms Services

    • 1 Comments

    Christophe just passed along some information about a new solution starter on CodePlex:

    Hello,

    A new solution starter that demonstrates how to leverage InfoPath & Forms Services as well Windows Workflow Foundation (hosted by SharePoint) to create your own project initiation phase has been released on CodePlex.

    The project initiation phase is different for every customers and organizations. It typically requires a set of custom attribute to be filled in a web based custom form; and then it needs to go through a custom approval process workflow reviewed by all key decision makers. InfoPath Forms services combined with a SharePoint’s sequential workflow enables you to achieve this business need. This solution starter also demonstrates the added value of deploying Project Server 2007 in a SharePoint Server farm (using InfoPath Forms Services).

    The goal of this solution starter is thus to provide the following two code samples:

    • InfoPath Form with managed code that issues Project Server Interface calls to populate and submit form data
    • SharePoint sequential workflow that leverages the form created earlier to create a custom workflow

    This solution starter requires Project Server 2007, InfoPath and Forms Services 2007, and Visual Studio 2008.

    Step 1 – Fill out and submit Project Initiation Form

    Step 2 – Approve data submitted in Step 1 and create Project in PS 2007

    Resulting data in Project Server 2007

    clip_image001 clip_image002 clip_image003

    This solution starter was created for a presentation I delivered at the 2008 Office Developer Conference.

    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

    Update: Statusing Transaction (Partial Documentation)

    • 1 Comments

    Hello,

    Just a small update to the Statusing Transaction (Partial Documentation) post. The ASSN_TRANS_ACTION_ENUM enum was missing a couple of values:

     

       enum StatusTransactionState

       {

          /// <summary>Saved by team member.</summary>

          Saved = 0,

          /// <summary>Submitted by team member, awaiting action by manager.</summary>

          Submitted = 1,

          /// <summary>Submitted for processing by manager, awaiting processing by message handler.</summary>

          InProcess = 2,

          /// <summary>Applied to working store.</summary>

          ApplySuccessful = 3,

          /// <summary>Failed to be applied to working store.</summary>

          ApplyFailed = 4

       }

     

    Chris Boyd

  • Project Programmability and Business Intelligence

    Version 2 of the Office Interactive Developer Map

    • 1 Comments

    Just got an e-mail from Jim that Version 2 of the Microsoft Office Interactive Developer Map was published today, by the Office Developer Center folks.

    clip_image001

    The new version includes lots of new stuff (new products and new interactive links) – such as VS2008, Communications Server, Performance Point Server, Office Business Application (OBA) resources, and more.

    After you install it, the shortcut is in the 2007 Microsoft Office System Developer Resources folder in your Start menu.

     

  • Project Programmability and Business Intelligence

    Project Server 2007 VSTS Connector

    • 1 Comments

    Hello,

    There have been a number of people that have asked me about connecting Project Server to VSTS. Here is a pointer to a CodePlex project that will do just that:

    http://www.codeplex.com/pstfsconnector

    "The Visual Studio Team System Project Server 2007 Connector is designed to integrate the project management capabilities of VSTS with Project Server 2007. It's been developed by the Visual Studio Team System Rangers in response to significant customer demand for a connector solution. Future versions of Team System will have native integration with Project Server, in the meantime this Connector solution is the best way to integrate the two Microsoft products. This solution builds on the previous PS2003 VSTS Connector, published on GotDotNet. This solution is intended to provide guidance, provided as source code that can be used "as is," extended, or modified by developers to use on enterprise development projects."

     

    Chris Boyd

  • Project Programmability and Business Intelligence

    Project Server 2007 - Project Server Developer Community Expanding

    • 1 Comments

    Leveraging the development aspects of Project Server 2007 spans a wide range of skills.  PWA customizations (ASP.NET), workflow development, line of business applications integration using the PSI, extending Project Server functionality using server side events, specialized reporting from the Project Server databases and experience using VSTO are all areas of discussion. 

    The Project Server Product Group currently supports these efforts through channels such as the this Blog, Project Support WebLog, Project.Server Discussion, Project.Developer Discussion, and the Project 2007 Team Blog.

    To streamline and augment the existing efforts, we are seeking developers to join the already established Project.Developer Discussion Group

    By joining we invite you to share and discover knowledge about Project Server 2007's highly extensible out-of-the-box developer's platform, access Project Server Developer best practices, utilize a technical window back to the MS Project Server Development Group, and take part in several specialized webcasts on Development topics coming up in the next couple months.

    To identify yourself as someone that would like to join the Development Community, please contact Joyce Bileau, v-joyceb@microsoft for more information.

    To join, go to http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.project.developer

  • 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

    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

    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

    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

  • 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

    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

    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

    Microsoft Office Project Conference 2007 – Security Wiper

    • 1 Comments
    NOTE: Please do not run this on a production environment without careful thought and planning. Always test it on a backup and verify everything is okay before doing anything

    Hi, Phil Smail here again. Again some more code I wrote for the MOPC that I wanted to put up onto the blog.

    This code originally came about because a internal Microsoft group had got to the stage where their security permissions were out of control. This was mainly due to an initial lack of understanding of the security model and they wanted to get to a state where all the users, other than Admins have their permissions wiped so a proper security model can be put in place.

    The tool looks like the following when run:

    image

    First thing is to enter the URL of the site you want to connect to then click on the 'Connect to Server' button.

    This then populates the 'Admin Group' dropdown with the list of Groups on the server. When you click 'Reset Perms' then it will run through every user and remove them from all Groups and Categories they belong to as well as removing all Global Permissions set on users directly. This is the case except for the group that you select as the Admin group. All users that belong to that group will continue to belong to that group.  Once the tool is run then the Admins, who should still belong to the chosen 'Admin Group' can go about giving users the permissions following security best practice.

    A Dry Run checkbox is checked by default and this will run the test to see how much will be removed and display it in the textbox below. You should definitely run it in Dry Run mode before removing any permissions for real.

    In this case the output would look something like the following:

    image

    NOTE: Please do not run this on a production environment without careful thought and planning. Always test it on a backup and verify everything is okay before doing anything
  • Project Programmability and Business Intelligence

    Two new Project Server 2007 tools have been released on CodePlex today

    • 1 Comments

    Hello,

    Two new Project Server 2007 tools have been released on CodePlex today:

    EPM 2007 Log File Report tool EPM 2007 Queue Watch tool
    http://www.codeplex.com/EPMLogFileReport http://www.codeplex.com/EPMQueueWatch
    The Project Server 2007 Log File Report Tool enables the import and the reporting of log file generated by your EPM & SharePoint farm. The Log File Report Tool will import log files (in an SQL database) from all servers in your farm with the ability to filter them by date. Once the import is complete you can easily search for specific log file messages using the following filtering criteria: Area, Category, Level, Process, Server as well as date and created reports.

    The Project Server 2007 Queue Watch Tool will help you monitor all queue activities for a specific Project Web Access (PWA) instance. This tool leverages the standard Project Server Interface publically documented web services to query and retrieve jobs in the Project Server queues, further you can configure the tool to filter the information retrieved by Message Types and Job types.

    The Queue Watch Tool is available as a WinForms application. It was written by EPM World Wide Center of Excellence (WW COE) to efficiently monitor and troubleshoot queue activities for Project Server 2007 farms.

    MWSnap052 2007-12-12, 10_44_28 MWSnap071 2007-12-14, 12_45_33
    This release contains documentation, setup, and source code. This release contains documentation, setup, and source code.


    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

    Update on the ODC 2008

    • 1 Comments

    Hello,

    This is a follow up from my previous post about the ODC 2008. Just wanted to let you know that the ODC 2008 will have some specific Project Server sessions:

    Session Title Speaker Name Abstract
    Building a custom Webservice to query the EPM datastore and render the data in Excel 2007 Christophe Fiessinger This session will teach you how to develop a custom wss webservice querrying the Project Server database and displaying the resulst in an Excel 2007 AddIn
    Creating projects in Project Server using Forms Server and WinWF Christophe Fiessinger This session will show you how to develop a solution that creates projects in Project Server using Forms Server and WinWF
    Displaying Project Server data in an Ajax.NET WebPart Boris Scholl This session shows you how to develop an Ajax webpart displaying data from Project Server in a Windows Live Map.


    You can view all the session abstracts by track following these links:

    Client Track - http://blogs.msdn.com/odc2008clienttrackblog/archive/2007/11/30/list-of-sessions-in-odc-client-track.aspx

    Architect Track - http://blogs.msdn.com/odc2008architecture/archive/2007/11/07/breakout-session-abstracts.aspx

    Real World Track - http://blogs.msdn.com/odcrealworldtrackblog/archive/2007/11/30/odc-2008-real-world-session-tracks-ready-for-review-a-work-in-progress.aspx

    Server Track - http://blogs.msdn.com/odcservertrackblog/archive/2007/12/01/server-track-draft-session-list.aspx

    Services Track - http://blogs.msdn.com/rbarker/archive/2007/12/03/99-7-there-for-the-odc-services-track-sessions.aspx

    Chris Boyd

  • Project Programmability and Business Intelligence

    December update of the Project 2007 SDK

    • 1 Comments

    Hello,

    Jim Corbin has just passed along the following:

    The December update of the Project 2007 SDK download and the MSDN online release are both published. This is the third SDK update published since the RTM release of the SDK in November 2006. The first update was published in April and the second in July, 2007.

    Project Developer Portal: http://msdn2.microsoft.com/en-us/office/aa905469.aspx

    SDK Online:  Project 2007 SDK in MSDN online library: http://msdn2.microsoft.com/en-us/library/ms512767.aspx

    SDK Download:  http://www.microsoft.com/downloads/details.aspx?FamilyId=2672F6F9-7028-4B30-99A2-18CB1EED1ABE&displaylang=en

    What’s New

    In addition to several documentation fixes, the following content is new or extensively updated.

    Articles:

    · ERP Connector Solution Starter (20 topics)

    · How to: Customize E-Mail for Project Server Notifications  – this is an update of the project_programmability blog post.

    · Project XML Data Interchange Schema Reference includes the following topics:

    · New XML Elements

    · Custom Field Data in XML

    · Saving and Opening Projects in XML Format

    · How to: Use XSLT Transformations with Project XML Data Interchange Files

    · Project Data Interchange Elements includes reference topics for all of the schema sections, XML structure, and XML elements.

    PSI Managed Code Reference:

    · Additional descriptions of row properties in the following datasets in the Project Server Interface (PSI): LookupTableDataSetLookupTableMultiLangDataSet, AlertSubscriptionInfoDataSet, ReminderSubscriptionInfoDataSet, QueueStatusDataSet, QueueStatusRequestDataSet, StatusingSettingsDataSet.

    · Additional descriptions for event receiver methods.

    Download:

    · ERP Connector:   Complete sample source code and test application for the ERP Connector.

    · XML Schema:  Revised XML data interchange schema, mspdi_pj12.xsd.
    Note: The Project XML schema is also now published on http://schemas.microsoft.com/project/2007.

    · ProjTool:   Now includes a dialog box for backup and restore of selected projects.

    · SP1 library assembly: Redistribution license for the SP1 build of the Microsoft.Office.Project.Server.Library.dll assembly, and 32- and 64-bit copies of the assembly.

  • Project Programmability and Business Intelligence

    Project 2007 Service Pack 1 has Released Today!

    • 0 Comments

    Hello,

    I know a lot of you are interested in Project 2007 Service Pack 1. It has been released to the web!

    Project Professional and Project Standard SP1:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=CEC3E1E2-D802-4A03-BC78-05C48472559B&displaylang=en

    Here are the instructions for installing Project Server SP1:

    http://technet2.microsoft.com/Office/en-us/library/c3b86049-7cba-4b9c-8335-e37fb6e7518a1033.mspx?mfr=true

    Also, the Office SP 1 is available here:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=9EC51594-992C-4165-A997-25DA01F388F5&displaylang=en

     

    Chris

  • Project Programmability and Business Intelligence

    Publishing Projects based off a Project Custom Field Value

    • 3 Comments

    Hello,

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

     

    image

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

    image

    To setup the custom field and lookup tables:

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

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

      image

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

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

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

    image

    Chris Boyd

  • Project Programmability and Business Intelligence

    Microsoft Office System Developer Conference 2008

    • 3 Comments

    Hi,

    We are pleased to announce the dates and location for the 2008 Office Developer Conference!

    When: February 10-13, 2008

    Where: San Jose Convention Center, San Jose, CA

    Who: 2000 developers and architects who build solutions on the Office platform (Office clients, servers, and services)

    ODC 2008 will bring together architects, developers, industry technical experts, Microsoft insiders and key partners in a public forum for the first time! Together, we will redefine what it means to be focused on Microsoft Office development, exchange ideas on how to continue innovating in this ever-changing space, and share best practices on how to craft the next generation of Office Business Applications. Microsoft Chairman Bill Gates will deliver a special keynote that is not to be missed.

    We are giving you advance notice that our public site http://www.odc2008.com is going live on Monday morning, Sept. 17th. We will keep you updated when registration opens and as we have more information to share about the event. In the meantime, please help to spread the word about the conference and visit the site on Monday so you can subscribe to updates as we post them.

    See you there,

    - The 2008 ODC Team

    Answers to some Frequently Asked Questions about the conference:

    Who is the Microsoft Office System Developer Conference for?

    Those involved or interested in using the Microsoft Office system (clients, servers, and services) as an application development platform. For example, people in technical roles e.g. architects, developers, designers, and technical managers.

    Who can attend?

    The conference is open to the public.

    Is this the first time for this conference?

    No, Microsoft has held this conference three times in the past (2004, 2005, 2006). However, those conferences were only open to a select group by nomination/invitation. This is the first conference that is open to the public.

    Will it cost to attend the conference?

    Yes, there will be a reasonable registration fee for the conference. For those that register early, we will have a special early bird rate as well.

    How many people are you expecting?

    We’re expecting between 1800-2000 attendees

    Which products/technologies will this conference cover?

    Obviously, the conference will cover everything in Microsoft Office system, including clients, servers and services, but business productivity applications don’t exist in isolation. More and more, we’re finding that people are using others parts of the technology stack and bringing together various technologies to make applications more compelling. So a great number of sessions will have topics involving Office application development in the context of leading-edge technologies such as VOIP, Silverlight, integration with third-party line of business applications, etc.

    What tracks will you offer?

    We are still finalizing the organization of the more than 60 sessions planned. Track information will be available as registration opens in October.

    I’ve heard there’s also a SharePoint Conference. What’s the difference?

    The SharePoint conference will take place in March 2008 in Redmond, WA. Unlike the Microsoft Office System Developer Conference, the SharePoint conference is primarily intended for those in IT who use, deploy, and configure SharePoint. If you are focused on SharePoint deployment you should attend the SharePoint Conference. If you are a SharePoint developer you should attend Office System Developer Conference. Here you will find the most information about how to develop solutions that not only build on the SharePoint platform, but also take advantage of the other technologies in the Office system.

    Why didn’t you just have the conference at the same time/location with SharePoint Conference so people could attend both?

    We did look into this but couldn’t make this happen due to lack of suitable space.

    Who is delivering the keynote?

    Microsoft Chairman Bill Gates will be delivering a special keynote presentation. Visit our site www.odc2008.com in October to find out more about the other keynote speakers!

    Why did you choose San Jose instead of having the conference in the Seattle area?

    We looked at several options. Lack of suitable space was a key factor. Also, average temps in San Jose in Feb are Low: 45°F (7°C) High: 63°F (17°C) with 2.84 in (7.21 cm) of precipitation, whereas in Seattle the averages are Low: 37°F (3°C) High: 51°F (10°C) and 4.09 in (10.39 cm). It wasn’t a difficult decision to make.

    Who will present sessions?

    Sessions will be presented by Microsoft experts and MVPs, as well as other industry/technical experts.

    Will you have non-Microsoft staff presenting sessions?

    Yes. If you or someone you know would like to present on a relevant technical topic, we’ d be happy to consider them, though slots are very limited. To submit a session idea, please send all relevant information to odcspkr@microsoft.com

    Where can I find more information?

    Our web site www.odc2008.com will have more information about the conference as it becomes available. Check back often or sign up to get notified!

  • Project Programmability and Business Intelligence

    Microsoft Office Project Conference 2007 - Simple Effective Rights

    • 2 Comments

    I'm Phil Smail, one of the Program Managers from the Product Group. One of my presentations during the MOPC 2007 was one of security programmability. As part of that Presentation I demonstrated some code that I wanted to get onto this blog

    This code is a Simple Effective Rights tool. It's essentially a cut down version of the View Effective Rights tool we shipped with the Project 2003 Project Resource Kit (and will be shipping soon in the Project 2007 Project Resource Kit)

    It's a Winforms application that uses the PSI to brute force calculate why a user does/doesn't have the correct permissions. This version only does Project Permissions but could be very easily modified to handle Resource and Global permissions. It uses the PSI to work out the permissions

    When you first start the application you'll get the following logon screen:

    image

    When you enter the URL you are presented with the main Form page:

    image

    There are three drop downs at the top of the page. One to select the User, the second to choose the relevant permission, and the third to choose the Project Name. One those details have been entered you just need to hit the 'EffectivizeName Possible my Rights' button. It then looks like the following:

    image

    The columns mean the following:

    Column Name Possible Values Purpose
    Right Allow/Deny The right specified for the selected Permission
    Object Present True/False Whether the object is explicitly added to the category. If not then it may be accessible through the Category rules
    Category Name N/A The name of the category giving the user permissions
    User/Group Name User name or the Group name If this is via the User then the User Name is displayed else we just display the name of the Group
    Applicable Rules

    · All current and future projects in Project Server database

    · Only the projects indicated

    · The User is the Project Owner or the User is the Status Manager on assignments within that Project

    · The User is on that project's Project Team, The Project Owner is a descendant of the User via RBS

    · A resource on the project's Project Team is a descendant of the User via RBS

    The Project Owner has the same RBS value as the User

    The rules specified by the Category that may be relevant to the effective Permission

    The rules specified by the Category that may be relevant to the effective Permission

    As you can see in the case above the following can be understood from this:

    · The effective right is Deny for this user, this permission on this project

    · The user is denied in 4 categories, 3 directly on the user, one through a group

    What it’s doing under the covers is going through the user object and working out what categories they have permissions on and whether the object exists and what permission the user has on those objects. It then goes through all the groups the user belongs to, works out what categories they have permissions on and calculates the same information. This will give the effective right the user has.

    Why do we have to go through every item to work out the effective right of the user? Unfortunately there isn’t a PSI that tells us the effective permission of a user other than the logged on user.

Page 8 of 11 (255 items) «678910»