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

    Setting the Project Owner

    • 4 Comments

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

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

    private Guid GetResourceGuid(string ls_name)

    {

      WSResource.Resource lo_resWS =

        (WSResource.Resource)mo_conn.GetWebService(Connection.Resource);

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

     

      string nameColumn = lo_resDS.Resources.RES_NAMEColumn.ColumnName;

      string resUID = lo_resDS.Resources.RES_UIDColumn.ColumnName;

     

      PSLibrary.Filter.FieldOperationType equal =   

        PSLibrary.Filter.FieldOperationType.Equal;

     

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

     

      lo_filter.FilterTableName = lo_resDS.Resources.TableName;

     

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

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

     

     

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

     

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

    }

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

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

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

    Chris Boyd

  • Project Programmability and Business Intelligence

    Project Conference

    • 0 Comments

    I just wanted to give everyone a heads up about the upcoming Project Conference. Details can be found here:

    http://www.msprojectconference.com/

    There will be a Developer/IT Professional track that we will be presenting on a verity of development topics that cover the PSI, Events, Reporting and Project Client. I will provide further details about my presentation as I start to develop my slide decks.

    Chris Boyd

  • Project Programmability and Business Intelligence

    Fearing the Password Expired Message

    • 1 Comments

    Since is it common practice for passwords to expire after a set number of days, this can cause a bit of a pain for developers who have setup their Project Server development environment using their own credentials. This happens to me all the time, so I figured I would share a couple of solutions that I use to get around this:

    ·         Create a local administrator and setup Project Server using that account

    ·         Or follow Christophe Fiessinger's blog post on how to change the password:

    http://blogs.msdn.com/chrisfie/archive/2006/12/15/how-to-change-an-administrator-password-on-your-project-server-2007-farm.aspx

    Chris Boyd

  • Project Programmability and Business Intelligence

    Registering with Events over the next few weeks?

    • 1 Comments

    I just want to give everyone a heads up who is developing and registering event handlers for Project Server over the next few weeks. When you create, update or delete an event handler, a WSS timer job is created to do this work. These jobs are delayed by an hour due to a DST issue in WSS.

    There are a couple of workarounds:

    ·         Wait an hour for the event to be updated

    ·         Set the clock back one hour and let the timer job run

    For more information, see this KB: http://support.microsoft.com/kb/932563

     

  • Project Programmability and Business Intelligence

    Registering with Project Server Events

    • 0 Comments

    As I am sure many of you are aware that Project Server provides an event infrastructure that developers can create their own events for key pre and post events that occur on the server. Mike Shughrue from the Project's Center of Excellence has provided the attached sample code that is a great example on how to register events and how to create event handlers. I use this tool to register my events opposed to using the UI on the server.

    For more information about events, check out the following SDK articles:

    Chris Boyd

  • Project Programmability and Business Intelligence

    Creating a PSI Extension

    • 3 Comments

    Overview

    The Project Server Interface (PSI) is a set of over 20 web services that provide a programming interface for Project Server 2007. The PSI is used by Project Professional, Project Web Access (PWA) and third party applications to communicate with Project Server. For an overview of the PSI, read: PSI Reference Overview.

    Even though the PSI is a rich set of web services that provide access to the majority of Project Server data, there are still scenarios where custom extensions to the PSI are required. The PSI provides the ability to extend the PSI with custom web services. This provides the ability to create a web service that tightly integrate with Project Server.

    Here are a few scenarios that might make use of a PSI extension:

    • A PSI extension that pulls data from the reporting database. This allows for a simple deployment story for third party applications that are deployed outside a firewall from Project Server. They will not have to do direct query to the SQL database. If you create a PSI extension that opens up the reporting database, please make sure you do the appropriate security checks.
    • PSI extension and impersonation works easily because the PSI extension will be running in the correct security context.
    • Seamless third party integration. If you write an application that extends the functionality of project server with additional functionality, a PSI extension may provide a seamless integration story.

    In this article, we will begin by creating a simple "Hello World" web service that is an extension of the PSI and have a client application access the "Hello World" web service. Then we will extend the web service to show how to get the user context information and how to call into existing PSIs from the new web service.

    Creating a Simple "Hello World" Web Service in Visual Studio

    To begin, we are going to create the "Hello World" web service. This example is written in C# using Visual Studio 2005; Visual Basic .NET provides equivalent functionality and could also be used.

    This web service has only one exposed web method, HelloWorld(). All the method does, is return the string "Hello World".

    1. Open Visual Studio 2005
    2. Click File à New Web Site
    3. From the Templates, Select "ASP.NET Web Service" and enter a location:



      In this example, the location is set to HelloWorldPSI.
    4. Click OK

    Next, you are going to want to create a new project within the HelloWorldPSI solution. This class library project is going to be used to contain the Web service logic:

    1. To create the project, right click on the solution name from the Solution Explorer, click Add àNew Project:


    2. From the Templates, Select "Class Library" and give the Class a name:


      In this example, the class name is HelloWorldPSI.
    3. In the Class Library Project, add the references to System.Web.Services and Microsoft.Office.Project.Server.Library by Right Clicking References à Add Reference in the Solution Explorer:


      • System.Web.Services should be found in the list of .Net Assemblies
      • Microsoft.Office.Project.Server.Library can be found by browsing to the bin directory in the install directory of Project Server. (C:\Program Files\Microsoft Office Servers\12.0\Bin)
    4. Replace the default Class1.cs file in the project with the Service.cs file that Visual Studio provides in the App_Code folder of the Web Service. To replace the Class1.cs file with the Service.cs file:

      • In Solution Explorer, drag Service.cs to the top node in the class library project.
      • Delete the Class1.cs file, and also delete the Service.cs file that remains in the App_Code folder.

      The Solution Explorer should look like this when you are done:

    5. Open the Services.cs file and Replace:

      [WebService(Namespace = "http://tempuri.org/")]

      With:

      [WebService(Namespace = "http://schemas.microsoft.com/office/project/server/webservices/Service/", Name = "HelloWorldPSI", Description = "Contains the Service web service for Microsoft Project Server.")]
    6. Create a strong name for the class library. In Solution Explorer:

      1. Right Click the Class Library Project à Properties dialog box
      2. Click Signing,
      3. Select Sign the assembly, and select <New> in the box for choosing a strong name key file.
      4. Provide a file name for the key
      5. Deselect Protect my key file with a password
      6. Click OK.
    7. Build only the Class Library Project, Right Click the project in Solution Explorer, and click Build.
    8. Add the assembly to the Global Assembly Cache (GAC), you can either:
      1. Drag and drop the assembly into the %windows%\assembly directory using 2 instances of Windows Explorer
      2. Use the command line utility gacutil.exe that is installed with the .NET Framework SDK 2.0.To use gacutil.exe to copy the class library DLL into the GAC:
        1. To open the Visual Studio command prompt, Click Start à All Programsà Microsoft Visual Studio 2005 à Visual Studio Tools à Visual Studio 2005 Command Prompt.
        2. Enter the following command and press ENTER:

          gacutile.exe -iF "<Full file system path to DLL>".
    9. Open %windows%\assembly in Windows Explorer
    10. Open the Properties of the assembly by Right Clicking on the assembly and Selecting Properties:


    11. In Visual Studio, open Service by Right Clicking the file in the Solution Explorer and Clicking Open
    12. Remove the CodeBehind attribute from the page directive in Service.asmx, and modify the contents of the Class attribute so that the directive matches the following format:

      <%@ WebService Language="C#" Class="Service, HelloWorldPSI, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3f8ef1d5444ca3c9" %>
    13. Rename the Service.asmx to something meaningful. In this example, it will be renamed to HelloWorldPSI.asmx.

    Generating and Modifying Static Discovery and WSDL Files

    To provide discovery and description for your custom Web service, you must create a .disco and a .wsdl file. Since Windows SharePoint Services virtualizes URLs, you cannot use the auto generated .disco and .wsdl files generated by ASP.NET. Instead, you must create a .disco page and a .wsdl that provides the necessary redirection and maintains virtualization.

    You can use ASP.NET to generate the .disco and .wsdl files by hosting your Web service in a virtual directory, such as /SharedServices1/PSI, and then using the .NET Framework Web Service Discovery tool (Disco.exe) to obtain the generated files.

    The below steps assume that you have installed project server in the default directory. To generate .disco and .wsdl follow these steps:

    1. In Windows Explorer, copy the .asmx file to C:\Program Files\Microsoft Office Servers\12.0\WebServices\Shared\PSI.
    2. Open the web.config found in: C:\Program Files\Microsoft Office Servers\12.0\WebServices\Shared\PSI.
    3. Add the following below the line <add assembly="Microsoft.Office.Project.Server.WebService, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />:

      <add assembly="HelloWorldPSI, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3f8ef1d5444ca3c9" />

      You will need to change the public key token to match the public key token for your assembly. Your public key token was determined in step 9 in the section "Creating a Web Service in Visual Studio".
    4. Restart IIS by opening a command prompt and entering: iisreset
    5. Run disco.exe in the command prompt:

      disco /o:"C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\isapi\PSI" http://localhost:56737/SharedServices1/PSI/HelloWorldPSI.asmx
    6. In Windows Explorer navigate to: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI\PSI
    7. Rename HelloWorldPSI.disco to HelloWorldPSIdisco.aspx
    8. Rename HelloWorldPSI.wsdl to HelloWorldPSIwsdl.aspx
    9. To register namespaces of the Windows SharePoint Services object model, open both HelloWorldPSIdisco.aspx and HelloWorldPSIwsdl.aspx files and replace the opening XML processing instruction <?xml version="1.0" encoding="utf-8"?> with instructions such as the following:

      <%@ Page Language="C#" Inherits="System.Web.UI.Page" %> <%@ Assembly Name="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Import Namespace="Microsoft.SharePoint.Utilities" %> <%@ Import Namespace="Microsoft.SharePoint" %><% Response.ContentType = "text/xml"; %>
    10. In the HelloWorldPSI.disco file, modify the contract reference and SOAP address tags to be like the following example, which replaces literal paths with code generated paths through use of the Microsoft.SharePoint.Utilities.SPEncode class, and which replaces the method name that is specified in the binding attribute:

      <discovery xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.xmlsoap.org/disco/">
      <
      contractRef ref=<% SPHttpUtility.AddQuote(SPHttpUtility.HtmlEncode(SPWeb.OriginalBaseUrl(Request) + "?wsdl"),Response.Output); %> docRef=<% SPHttpUtility.AddQuote(SPHttpUtility.HtmlEncode(SPWeb.OriginalBaseUrl(Request)),Response.Output); %> xmlns="http://schemas.xmlsoap.org/disco/scl/" />
      <
      soap address=<% SPHttpUtility.AddQuote(SPHttpUtility.HtmlEncode(SPWeb.OriginalBaseUrl(Request)),Response.Output); %> xmlns:q1="http://schemas.microsoft.com/projectserver/soap/Service/" binding="q1:ServiceSoap" xmlns="http://schemas.xmlsoap.org/disco/soap/" />
      <
      soap address=<% SPHttpUtility.AddQuote(SPHttpUtility.HtmlEncode(SPWeb.OriginalBaseUrl(Request)),Response.Output); %> xmlns:q2="http://schemas.microsoft.com/projectserver/soap/Service/" binding="q2:ServiceSoap12" xmlns="http://schemas.xmlsoap.org/disco/soap/" />
      </
      discovery>
    11. In the HelloWorldPSI.wsdl file, make the following, similar substitution for the SOAP address:

      Replace:

      <soap:address location="http://localhost:56737/SharedServices1/PSI/HelloWorldPSI.asmx" />

      With:

      <soap:address location=<% SPHttpUtility.AddQuote(SPHttpUtility.HtmlEncode(SPWeb.OriginalBaseUrl(Request)),Response.Output); %> />


      And Replace:

      <soap12:address location="http://localhost:56737/SharedServices1/PSI/HelloWorldPSI.asmx" />

      With:

      <
      soap12:address location=<% SPHttpUtility.AddQuote(SPHttpUtility.HtmlEncode(SPWeb.OriginalBaseUrl(Request)),Response.Output); %> />
    12. Restart IIS by opening a command prompt and entering: iisreset

    Creating a Client Application

    At this point, we have a basic web service that extends the PSI. Now we are going to write a small client application that calls the web service. Open Visual Studio and create a new project:

    • File à New Project
    • From the Templates, Select "Windows Application"
    • Give the Windows Application a name. In this example, we will use "HelloWorldPSIClient"
    • Add Web Reference to: http://localhost/pwa/_vti_bin/PSI/HelloWorldPSI.asmx?WSDL and call it WSHelloWorldPSI
    • Rename Form.cs to HelloWorld.cs
    • Open HelloWorld.cs in Deisgn Mode
    • Add a Button Called "Connect to Hello World PSI"
    • Copy the following Code:

          using System;
          using System.Collections.Generic;
         
    using System.ComponentModel;
         
    using System.Data;
         
    using System.Drawing;
         
    using System.Text;
         
    using System.Windows.Forms;
         
    using System.Net;

    namespace HelloWorldPSIClient
    {

      public partial class HelloWorld : Form
     
    {
        public HelloWorld()
        {
          InitializeComponent();
       
    }

        private void cmdHelloWorld_Click(object sender, EventArgs e)
       
    {
          
    WSHelloWorldPSI.HelloWorldPSI helloWorldWS
            
    = new HelloWorldPSIClient.WSHelloWorldPSI.HelloWorldPSI(); 
          
    helloWorldWS.Url = http://localhost/pwa/_vti_bin/psi/HelloWorldPSI.asmx;
           helloWorldWS.Credentials = CredentialCache.DefaultCredentials;

           string hello = helloWorldWS.HelloWorld(); 
           
    MessageBox.Show(hello);
       

      }
     
    }

    If you run the client application and click the button, it should connect to Project Server, get the string "Hello World" and show it in a message box:

    Now we have successfully created a web service that extends the PSI and a client application that calls into the web service. The attached file, Sample Code.zip contains PSIExtensionSample1.zip, which has the source code to the web service, the client application, HelloWorldPSI.disco and HelloWorldPSI.wsdl.

    Doing More with the Simple Web Service

    In the last example we created a simple web service that returned "Hello World". In this example, we are going to extend the "Hello World" web service. Within the HelloWorld() web method, we are going to get the user context and get the user's e-mail address from the Resource PSI.

    Starting from the HelloWorldPSI solution:

    1. Add a Reference to System.Web
    2. Add a Web Reference to the Resource PSI: http://localhost/_vti_bin/PSI/Resourcewsdl.aspx and call it WSResource
    3. Open the Service.cs to edit the code
    4. Add using System.Net;to the top of the class file
    5. In the method HelloWorld() Replace:

      return "Hello World";

      With:

      // Get the user context of the calling user

         HttpContext context = HttpContext.Current; 
         
    string pjAuthHeader = context.Request.Headers["PjAuth"];
        
    PSContextInfo contextInfo = PSContextInfo.DeserializeFromString(pjAuthHeader);

         String message = "Hello World\r\n\r\n"
              
    message += "Following user called the Custom Project Server Web Service\r\n";
              
    message += String.Format(System.Globalization.CultureInfo.InvariantCulture,
                               
    "UserName = {0}, SiteGuid = {1}, Lcid = {2}\r\n",
                               
    contextInfo.UserName, contextInfo.SiteGuid, contextInfo.Lcid);

         // Call into the Resource PSI for the user's e-mail address

         HelloWorldPSI.WSResource.Resource resWS = new HelloWorldPSI.WSResource.Resource();

         resWS.Url = http://localhost/pwa/_vti_bin/psi/Resource.asmx
         resWS.Credentials = CredentialCache.DefaultCredentials;

         HelloWorldPSI.WSResource.ResourceDataSet resDS = resWS.ReadResource(contextInfo.UserGuid);

         message += "E-Mail Address: "
           
    resDS.Tables[resDS.Resources.TableName].Rows[0][resDS.Resources.WRES_EMAILColumn.ColumnName].ToString();

      return message;

    Now we need to deploy the changes:

    1. Build only the Class Library Project, Right Click the project in Solution Explorer, and click Build.
    2. Add the assembly to the Global Assembly Cache (GAC), you can either:
      1. Drag and drop the assembly into the %windows%\assembly directory using 2 instances of Windows Explorer
      2. Use the command line utility gacutil.exe that is installed with the .NET Framework SDK 2.0.To use gacutil.exe to copy the class library DLL into the GAC:
        1. To open the Visual Studio command prompt, Click Start à All Programsà Microsoft Visual Studio 2005 à Visual Studio Tools à Visual Studio 2005 Command Prompt.
        2. Enter the following command and press ENTER:

          gacutile.exe -iF "<Full file system path to DLL>".
    3. Restart IIS by opening a command prompt and entering: iisreset
    4. Run the Client Application and click the button:

    As you can see from the message box, we have successfully got the user context of the calling user and have been able to call into an existing PSI. The attached file, Sample Code.zip contains PSIExtensionSample2.zip, which has the source code to the web service that displays the user context and the resources e-mail address, the client application, HelloWorldPSI.disco and HelloWorldPSI.wsdl.

    Best Practices for Project Server Extensions

    There are best practices to follow when developing a Microsoft Office Project Server 2007 extension for the PSI:

    1. Do not modify any Project Server database objects (tables, views, stored procedures, and so on)
    2. You can read and manipulate Project Server Data by calling into existing PSIs
    3. You can read Project Server data from the Reporting Database
    4. Incorporate security into your PSI extensions. Do not return data back to users who should not have access to the data.
    5. PSIs can throw runtime exceptions in many situations. Make sure all your code is protected with try/catch blocks so that a sensible reply message can be returned to the client.
  • Project Programmability and Business Intelligence

    Creating Deliverable Reports

    • 0 Comments

    I have had many questions with regards to the Deliverables feature and reporting. If your not familiar with Deliverables, I suggest you take a look at my blog post on the Project blog:

    http://blogs.msdn.com/project/archive/2007/02/24/deliverables.aspx


    Just like all other project data, when a project plan is published, the data makes it way to the reporting database. This allows you to create some very useful reports on deliverables and dependencies. To get started with reporting, you may want to read through this post:

    http://blogs.msdn.com/project_programmability/Default.aspx?p=2

    In this post, I am only going to provide some background information on Deliverables and a couple of queries to get you started with creating your own reports. To begin with, these are the views and tables that are most commonly used for Deliverable reports:

    MSP_EpmProject_UserView

    This view shows all the projects. Commonly you will join the Project UID in this view with the Project UID or the Relationship UID from the other views. By doing this you can get information about the project the deliverable or dependency is associated with, such as the name of the project.
    MSP_WssDeliverableToProjectLinks_UserView

    This view lists all the published deliverables, not the dependencies. In this view you can get information such the UID for the project a deliverable is associated with and the start and finish date of a deliverable.
    MSP_WssDeliverableToTaskLinks_UserView

    This is the same at the MSP_WssDeliverableToProjectLinks_UserView except that is has additional fields for deliverables that are linked to tasks. This allows you to report on task details for the associated deliverable. For example, you could use the task information to write a report that shows all deliverables where the deliverable finish date is before the task finish date.
    MSP_WssListItemAssociation

    This view shows all the different associates with risks, issues and deliverables. Here you are going to want to look at the relationship type ID. The relationship type ID tells you if it is a deliverable or a dependency and if it is linked to a task or not. It is also where you can find if a dependency exists.
    MSP_WssRelationshipType

    This table lists the different types. These types refer to risks, issues and deliverables. For deliverables and dependencies, the following types are important:

    Relationship Type ID

    Description

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

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

    13

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

    14

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

    There are a set of common queries that user tend to want when creating a report for deliverables. This first query is a simple list of all the deliverables and what project they are associated with:

    SELECT 
    	ProjectName As 'Project Name', 
    	Title As 'Deliverable', 
    	StartDate As 'Start Date', 
    	FinishDate As ' Finish Date'
      FROM 
    	MSP_WssDeliverableToProjectLinks_UserView 
    	Inner Join MSP_EpmProject_UserView 
          	On 	MSP_WssDeliverableToProjectLinks_UserView.ProjectUID =
    MSP_EpmProject_UserView.ProjectUID
    
    

    The following query lists all the projects that have taken dependencies on a deliverable for given project. For the query to work, you need to set ProjectSelect.

    SELECT     
    	DeliverableProj.ProjectName AS SelectedProject,
    DependancyProj.ProjectName AS DependentProject,
    DeliverableLinks.Title, DeliverableLinks.StartDate, DeliverableLinks.FinishDate FROM MSP_EpmProject_UserView AS DeliverableProj INNER JOIN MSP_WssListItemAssociation ON DeliverableProj.ProjectUID =
    MSP_WssListItemAssociation.ProjectUID INNER JOIN MSP_EpmProject_UserView AS DependancyProj ON MSP_WssListItemAssociation.RelatedProjectUID =
    DependancyProj.ProjectUID INNER JOIN MSP_WssDeliverable AS DeliverableLinks ON MSP_WssListItemAssociation.ListItemUID = DeliverableLinks.DeliverableUniqueID WHERE (MSP_WssListItemAssociation.ProjectUID
    <> MSP_WssListItemAssociation.RelatedProjectUID) AND (DeliverableProj.ProjectName = @ProjectSelect)

    This last query lists all the projects that a given project is dependent on. Again, you need to set ProjectSelect for the query to work.

    SELECT     
    	DependancyProj.ProjectName AS SelectedProject,
    DeliverableProj.ProjectName, DeliverableLinks.Title, DeliverableLinks.StartDate, DeliverableLinks.FinishDate FROM MSP_WssListItemAssociation INNER JOIN MSP_EpmProject_UserView AS DependancyProj ON MSP_WssListItemAssociation.RelatedProjectUID = DependancyProj.ProjectUID INNER JOIN MSP_EpmProject_UserView AS DeliverableProj ON MSP_WssListItemAssociation.ProjectUID = DeliverableProj.ProjectUID INNER JOIN MSP_WssDeliverable AS DeliverableLinks ON MSP_WssListItemAssociation.ListItemUID = DeliverableLinks.DeliverableUniqueID WHERE (MSP_WssListItemAssociation.RelatedProjectUID <> MSP_WssListItemAssociation.ProjectUID) AND (DependancyProj.ProjectName = @ProjectSelect)

    To take a look at the last two queries in real reports, check out the Project Give and Get Reports in the Report Pack:

    http://blogs.msdn.com/project/archive/2007/01/30/sql-server-reporting-services-report-pack-for-project-server-2007.aspx

    This should be a good start with creating Deliverable reports. If you come up with some interesting queries for creating Deliverable reports, please share them by posting them as comments!

    Chris Boyd

     

    Tags:

  • Project Programmability and Business Intelligence

    Working with Deliverables

    • 2 Comments

    The other day I had a request from an internal customer that wanted to create deliverables for a large number of tasks that already existed in their project plan. They wanted to be able to simply flag each task as a deliverable and have it published. They did not want to do all the steps involved with creating a deliverable. 

    They also wanted to tightly couple the task name and dates with the deliverable name and dates. Currently, if a deliverable is linked to a task, when the task’s dates change, the dates for the deliverable do not. This is by design to allow the project manager to intentionally make the change to the deliverable dates since these dates are commonly published to a large audience. In this case, the user wanted the deliverable dates to change with the task dates with minimum user intervention.

    To get started, I created a flag enterprise custom field. The custom field that I created was "Pub Deliverable" and it is a task custom field. I added the field to the Gantt Chart view in Project Professional:

    Next, I wrote the following VBA macro:

    Sub Create_Flagged_Tasks_As_Deliverables()
       
        Dim t As Task
        Dim fPub As String

        For Each t In ActiveProject.Tasks

            ' This gets the flag value from the Enterpise Custom Field
            fPub = t.GetField(FieldNameToFieldConstant("Pub Deliverable"))
           
            If fPub = "Yes" Then
               
                ' If the task has this deliverable GUID, then there is no deliverable
                If t.DeliverableGuid = "00000000-0000-0000-0000-000000000000" Then
                    DeliverableCreate t.Name, t.Start, t.Finish, t.Guid
                Else
                    DeliverableUpdate t.DeliverableGuid, t.Name, t.Start, t.Finish
                End If
           
            Else
           
                If t.DeliverableGuid <> "00000000-0000-0000-0000-000000000000" Then
                    DeliverableDelete (t.DeliverableGuid)
                End If
               
            End If
           
        Next t
           
    End Sub

    This macro loops through all the tasks. If the flag field "Pub Deliverable" is set to yes, then it either creates or updated the deliverable. If it is set to no and there is a deliverable associated with the task, the deliverable is deleted.

    Before you can run this code, you will need to publish and create a workspace for your project. To run it, follow these steps:

    1. Open your Project Plan

    2. Press Alt+F11 – This will open the VBA Editor

    3. Double click on ThisProject Object:

    4. Copy the VBA code into the Object

    5. Run it by clicking on the Play button:


    With this solution, the user can simply flag each task that they want published as a deliverable and run the macro. If you want to have this code executed regularly without user intervention, you might want to consider placing this code in an event handler (VBA Event Handler Example).

    Chris Boyd

  • Project Programmability and Business Intelligence

    Adding the Project Programmability RSS Feed to Live

    • 1 Comments

    This is a little off topic, but I thought that it might be interesting to some...

    You can easily add a RSS feed to this blog on your live.com homepage:

    1. Go to: www.live.com
    2. Click on "Add Stuff":

    3. Click on "Advanced options":

    4. In the "Subscribe to a specific feed by entering the URL" text box enter:

      http://blogs.msdn.com/project_programmability/rss.xml
    5. Click "Subscribe" and you should now have a RSS feed to the blog:

       
  • Project Programmability and Business Intelligence

    VBA Event Handler Example

    • 4 Comments

    I felt that it is time to give VBA some love. I was surfing Web and came across this post on Changing the Cell Background Color which gave me an idea for a blog post. This post is a simple example of how to write an event handler that checks if a name of a task has a prefix of “XYZ_”. If it does, it changes the background color of the Task Name cell to yellow.

    This could be useful in scenarios where there is validation when saving to the server and you want to warn the user before the save. For example, say there is a third party application that inserts tasks into project plans automatically. When tasks are inserted by this application, it is prefixed with a code, “XYZ_”. This allows project managers to quickly identify tasks that have been inserted by the third party app. To prevent project managers from inserting the task with the same prefix, a Project Server event handler has been written to prevent tasks with the prefix from any user, except for the user context used by the third party app. This event is only fired during a save to Project Server. To give an early warning to the project manager that the project will fail on saving to the server, we can do following:

    1. Open Project and then the Visual Basic Editor (Alt + F11)
    2. Create a new Class Module for the Project

       

      Note: If you want the event to be fired for all projects that are associated with a Project Server, you will need to check out the Enterprise Global and create the event handler in it. For simplicity, I am only creating the event handler for this project.
    3. Change the name of the module to something meaningful, such as EventHandlers.
    4. Copy the following Code into the class module (This is the event handler):
    5. Public WithEvents App As Application
      Public WithEvents Proj As Project

      Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)
          
          MsgBox ("Test")
         
      End Sub

      Note: This link is to the Project 2003 SDK. It is a list of all the Project Client events you can hook into:

      http://msdn2.microsoft.com/en-us/library/aa679860(office.11).aspx

    6. Open the ThisProject Object:

    7. Paste in the following code at the top of the ThisProject Object:
    8. Dim X As New EventHandlers

      Sub Initialize_App()

          Set X.App = MSProject.Application
          Set X.Proj = Application.ActiveProject

      End Sub

      This will setup the event handler to fire before a task is changed.

    9. Now select the "Project" Object and then the "Open" procedure:

    10. This will stub out the built in event handler that will fire when the project opens. Here we want to call the initialization method we created in step 6:

      Call Initialize_App

    At this point we have the event handler hooked up and every time the user changes a task, they will get an annoying test message box. To test it, run: ThisProject.Initialize_App.

    Here is what you should get when you change a task:

    Now that we have the before task change event handler working, we need get the task that changed to change the cell background color to yellow if the task name begins with "XYZ_". In step 4 we created the event handler, we will need to change the code from displaying the test message box to:

    Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)
       
        If (Field = pjTaskName) Then
            If (InStr(NewVal, "XYZ_") = 1) Then
                ActiveCell.CellColor = pjYellow
            End If
        End If
       
    End Sub

    Now every time a user changes a task name to begin with "XYZ_" they will see the background color of the cell change to yellow:

     

    My scenario may be a bit of overkill, but hopefully it illustrates how to use the Before Task Change event and how to change the background color of a cell. Maybe in a future post, I will implement the server event that checks the names of the tasks.

    Chris Boyd

  • Project Programmability and Business Intelligence

    Creating a Task Hierarchy

    • 2 Comments

    The following questions has been asked:

    "[...] is there a way to set up the task hierarchy within a single project?"

    and the answer is YES! To create a hierarchy within a single project, you have to set the outline level for the sub tasks. The below example creates two task, Summary Task and Sub Task. For the Summary Task, we do not set the outline level, we just create it as normal task. The Sub Task is where you set the outline level. In this example we set the outline level to 2:


    Connection conn = new Connection("http://chboyd01/pwa");

    WSProject.Project projWS = (WSProject.Project)conn.GetWebService(Connection.Project);

    Guid sessGuid = Guid.NewGuid();
    Guid jobGuid = Guid.NewGuid();
    Guid taskGuid = Guid.NewGuid();

    Guid projGuid = GetProjectUidFromProjectName("Excel");

    projWS.CheckOutProject(projGuid, sessGuid, "");

    WSProject.ProjectDataSet dsP;

     

    // Create a task with a constraint

     

    dsP = new WSProject.ProjectDataSet();

    WSProject.ProjectDataSet.TaskRow taskRow = dsP.Task.NewTaskRow();

     

    // Set the required fields

    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = taskGuid;
    taskRow.TASK_NAME =
    "Summary Task";

    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

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

    // Create a second task

    dsP = new WSProject.ProjectDataSet();

     

    taskRow = dsP.Task.NewTaskRow();

    Guid task2Guid = Guid.NewGuid();

    jobGuid = Guid.NewGuid();

    // Set the required fields

    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = task2Guid;
    taskRow.TASK_NAME =
    "Sub Task";

    // Set the start and finish dates

    taskRow.TASK_START_DATE = new DateTime(2007, 01, 31);
    taskRow.TASK_FINISH_DATE =
    new DateTime(2007, 02, 03);

    taskRow.TASK_OUTLINE_LEVEL = 2;

    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

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

    PublishProject(projGuid);

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

    Chris Boyd

     

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

    Task Start and Finish Dates

    • 3 Comments

    When working with the task start and finish dates via the Project PSI, you might find some strange behaviors. It is not that it is strange, it is that these two fields are used by the scheduling engine to calculate your project's schedule. Hopefully this post will give you some insight to how to work with these two fields and why they may not be set to values that you expect.  

    When you first create a task, you can set the start date and finish date for the task. The below sample code shows you how to create a new task and how to set these fields:

    dsP = new WSProject.ProjectDataSet();

    WSProject.ProjectDataSet.TaskRow taskRow = dsP.Task.NewTaskRow();
    // Set the requied fields
    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = taskGuid;
    taskRow.TASK_NAME = "Example Task 3"

    // Set the start and finish dates
    taskRow.TASK_START_DATE = new DateTime(2007, 01, 20);
    taskRow.TASK_FINISH_DATE = new DateTime(2007, 01, 20);

    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

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


    The above sample code sets the start and finish date for the task to be January 20th, 2007. When I publish the project and view it in Project Center Drill Down, this is what I get:

    You might notice that the start date and finish date are not set to January 20th, but instead January 17th. This is because the start date of the project is set to January 17th. When the scheduling engine works out the schedule, it looks at the task I just created and determines that it has no constrains, thus it can be started right when the project begins. Thus the scheduling engine changes the start and finish date to January 17th.

    Now, lets create another task that is dependent on the one we just created. This time, we will make it's start and finish date January 31st, 2007:

    // Create a second task

    dsP = new WSProject.ProjectDataSet();
    taskRow = dsP.Task.NewTaskRow();

    Guid task2Guid = Guid.NewGuid();
    jobGuid = Guid.NewGuid();

    // Set the requied fields
    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = task2Guid;
    taskRow.TASK_NAME = "Example Task 4"

    // Set the start and finish dates
    taskRow.TASK_START_DATE = new DateTime(2007, 01, 31);
    taskRow.TASK_FINISH_DATE = new DateTime(2007, 01, 31);

    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

    // Here we make it dependent on the task we created before

    WSProject.ProjectDataSet.DependencyRow dependRow = dsP.Dependency.NewDependencyRow();

    dependRow.PROJ_UID = projGuid;
    dependRow.LINK_PRED_UID = taskGuid;
    dependRow.LINK_SUCC_UID = task2Guid;
    dependRow.LINK_UID = Guid.NewGuid();

    dsP.Dependency.AddDependencyRow(dependRow);

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

    PublishProject(projGuid);

    Again you will notices that the schedule engine has moved the task forward to January 18th:

    This is because the dependency we added to the new task on the one we had previously created.

    Lets say that you have a task that you need to schedule, but you know it cannot start before a certain date, due to some external factors from your project. In this case, you do not want the scheduling engine to move your task forward beyond that date. In this case, we need to set the TASK_CONSTRAINT_DATE and TASK_CONSTRAINT_TYPE fields. The below sample shows how to do this:

    // Create a task with a constraint
    dsP = new WSProject.ProjectDataSet();

    WSProject.ProjectDataSet.TaskRow taskRow = dsP.Task.NewTaskRow();
    // Set the requied fields
    taskRow.PROJ_UID = projGuid;
    taskRow.TASK_UID = taskGuid;
    taskRow.TASK_NAME = "Example Task"

    // Set the start and finish dates
    taskRow.TASK_START_DATE = new DateTime(2007, 01, 22);
    taskRow.TASK_FINISH_DATE = new DateTime(2007, 01, 22);

    taskRow.TASK_CONSTRAINT_DATE = new DateTime(2007, 01, 22);
    taskRow.TASK_CONSTRAINT_TYPE = (short)Library.Task.ConstraintType.StartNoEarlierThan;
    taskRow.AddPosition = (int)PSLibrary.Task.AddPositionType.Last;

    dsP.Task.AddTaskRow(taskRow);

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

    PublishProject(projGuid);

    Here is what we get:

    So finally we are able to create a task and have it start on a particular date, but there is a catch. You can only put one type of constraint on your task. Here are a list of constraint types that you can use:

     

    Constraint Type Description
    AsLateAsPossible Schedules the task as late as it can without delaying subsequent tasks. Use no constraint date.
    AsSoonAsPossible Schedules the task to start as early as it can. Use no constraint date.
    FinishNoEarlierThan Schedules the task to finish on or after the constraint date.
    FinishNoLaterThan Schedules the task to finish on or before the constraint date.
    MustFinishOn Schedules the task to finish on the constraint date. Once selected the task will not be moveable on the timescale.
    MustStartOn Schedules the task to start on the constraint date. Once selected the task will not be movable on the timescale.
    StartNoEarlierThan Schedules the task to start on or after the constraint date.
    StartNoLaterThan Schedules the task to start on or before the constraint date.

     

    Hopefully you have a somewhat of an idea about creating a task and how the start and finish date is affected by the scheduling engine. Now, lets take a look at updating a task's start and finish date. If you need to update a tasks start or finish date, you will quickly learn that you cannot simply read the project data set, find the task you want to update in the task table and update the start and finish date fields like this:

    dsP.Tables[dsP.Task.TableName].Rows[1][dsP.Task.TASK_START_DATEColumn] = new DateTime(2007, 12, 03);

    You will quickly run into the following runtime exception:

    Column 'TASK_START_DATE' is read only.

    As the exception states, this is because the start date and finish date are read only fields. These fields are read only because they are calculated fields and cannot be set when updating a project data set. 

    So how can you get around this? Again, you can place constraints on the dates like we did when creating tasks. The scheduling engine will honor the constraint when calculating the schedule, but remember, there are other factors that affect the calculation. Such as the number of resource assigned to the tasks and the amount of work required to complete the task. So if you constrain your start date, it will affect your finish date. This is why you can only place one constraint on a task. The below example shows how you can update a task that must start on January 15th, 2007:

    dsP = projWS.ReadProject(projGuid, ProjOutlookConnector.WSProject.DataStoreEnum.WorkingStore);

    dsP.Tables[dsP.Task.TableName].Rows[1][dsP.Task.TASK_CONSTRAINT_TYPEColumn] = (short)Library.Task.ConstraintType.MustStartOn;
    dsP.Tables[dsP.Task.TableName].Rows[1][dsP.Task.TASK_CONSTRAINT_DATEColumn] = new DateTime(2007, 01, 15);

    projWS.QueueUpdateProject(Guid.NewGuid(), sessGuid, dsP, false);

    PublishProject(projGuid);

    Here is what you will see in PWA after publishing the project:

    We have primarily focused on the factors that affect the start date. Just like the start date, the finish date is affected by many factors that the scheduling engine takes under consideration. For example, the finish date is affected by number of resources assigned to the task, calendar exceptions, such as weekends, specific exceptions in individual resource calendars, and the amount of work assigned required to complete the task.

    These are only some basic examples. Project's schedule engine is very complex and there are a number of factors that affect the start and finish date of a task. Hopefully I have given you some insight why your start and finish dates change.

    Chris Boyd

    Technorati Profile
  • Project Programmability and Business Intelligence

    Building Web Parts using VS.NET extensions for SharePoint

    • 0 Comments

    Mike McDonogh sent us this recommendation:

     

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

     

    http://www.microsoft.com/downloads/details.aspx?familyid=19F21E5E-B715-4F0C-B959-8C6DCBDC1057&displaylang=en#Overview

     

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

     

    1.    Create a new Web Part project

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

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

    <?xml version="1.0"?>

    <WebPart xmlns="http://schemas.microsoft.com/WebPart/v2">

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

       <TypeName> DynamicProjectRegistratonModule.RegisterProjectWP</TypeName>

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

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

    </WebPart>

    4.    Click deploy in Visual Studio.NET

     

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

     

    ·         Build the Web Part assembly.

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

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

    ·         Updates the web.config safe controls settings automatically.

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

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

    ·         Add your Web Part to the SafeControls list.

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

    ·         Attach to the w3wp processes to enable debugging.

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

  • Project Programmability and Business Intelligence

    Microsoft.Office.Project.Server.Schema

    • 2 Comments

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

    1.      Open Visual Studio Command Prompt

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

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

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

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

    Hope this helps,

    Chris

  • Project Programmability and Business Intelligence

    Agile Custom Project Guide - Second File Attachment

    • 7 Comments

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

  • Project Programmability and Business Intelligence

    Agile Custom Project Guide

    • 7 Comments

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


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

     

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

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

    2.      Launch Project 2007 client,

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

    From the Tools | Options menu | Interface tab

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

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

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

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

    6.      Click Ok on the main dialog

    7.      You should now see the Agile Custom Project Guide

     

    Nada

     

    Note:

     

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

     

  • Project Programmability and Business Intelligence

    Getting your Custom Project Guide to work in Project 2007

    • 1 Comments

    Happy Holidays! This post is from Nada Alwarid, a co-worker that is responsible for the project guide:

    In Project 2007, class IDs have changed.  Because of that, your custom Project Guide solutions cannot work until you update those class ID’s.  In this post, I’ll outline what those changes are so you can get your custom solution working.

    We have objects declared like this:

    <object id="TextConv"

          classid="clsid:0F5E0D2C-54F2-41D9-835F-06B3045B7059"

          type="application/x-oleobject"

          style="display: none">TextConv failed to load!

    </object>

    The id of the object is not important, its only used for referencing it within the HTML or Jscript code. The important part is the classID. It is used to actually look up that object in the list of ActiveX objects registered with the OS and load it.

    These IDs have changed from Project 2003 to Project 2007.

    The mapping from Project 2003 to Project 2007 is:

    Object Name

    Project 2003 Class ID

    Project 2007 Class ID

    Text Converter

    1CFC2250-9B5C-4546-ABA1-1F69A06DCA12

    0F5E0D2C-54F2-41D9-835F-06B3045B7059

    Document Event Object

    494B3458-3EFF-4C66-9C86-D47670D69634

    5500517E-1890-48B4-800E-D9FC609E6DC2

    Application Event Object

    04EEB710-3EB7-4B69-9281-E9BBB7B35959

    13D338F1-AA3F-444E-A2B7-BC98EFB03484

    Datepicker

    A709EC93-E1F9-4bc4-A9CB-7FDB51CD0EF1

    03660567-F7F2-4e2b-A13C-C6607A726AF5

     

    Also, you can download the default Project Guide files that has been attached to this post.

     Nada

     

  • Project Programmability and Business Intelligence

    An Impersonation Web Application

    • 5 Comments

    Jim Corbin has passed along this great post:

    When you impersonate a user in an application for Project Server 2007, you adopt the global and category permissions of the impersonated user. This article shows how to develop a Web application for Project Server that uses impersonation. The attached pj12Impersonation.zip file includes a complete Web application that allows you to log on Project Server using Forms or Windows authentication, checks your permissions for listing and creating projects, and then lets you impersonate any other Project Server user.

    Important: The Impersonation Web application is an example for demonstration purposes only. The application is designed to run on a test installation of Project Server. It allows anyone with a Project Server account to log on, impersonate any other user, and create projects. To use any impersonation application on a production server, you must programmatically limit usage and add security checks that are appropriate for your organization.

    Most applications for Project, including Project Professional 2007 and Project Web Access, call the Project Server Interface (PSI) Web services through the Project Web Access URL. Project Server enforces the security permissions of your account when you log on through an application that uses the Project Web Access URL. Impersonation requires direct calls to the PSI through the Shared Service Provider (SSP) Web application that hosts Project Web Access.

    The Project 2007 SDK includes the section Using Impersonation in Project Server with the article How to Write a Simple Impersonation Application. The ProjTool application in the SDK download also uses impersonation.

    The following figure shows the Impersonate page in the Web application, which indicates the identity of the application user. The application user can log on Project Server using Windows authentication; in the figure, the user has clicked Forms for the Authentication Type and logged on a Project Server user named Joe. Joe has the NewProject global permission to create a project, but is denied the ManageQueue permission necessary to execute the ReadProjectList PSI method. If you (as the application user) select Joe in the Select User drop-down list and then click Impersonate, you would run the application with Joe’s permissions. If you click List Draft Projects, the application would return an exception because Project Server does not allow Joe to use ReadProjectList. If you check the Use ReadProjectStatus() checkbox while impersonating Joe, the application would call the ReadProjectStatus method instead, and Joe could get the list of draft projects.

     

    In the figure, the logged-on user Joe is impersonating the Administrator user, who does have the ManageQueue permission. Therefore, Joe can use the ReadProjectList method even though his own account does not have permission to do so. The application also enables an impersonated user (who has the NewProject permission) to create and publish a project, and then shows the new project in the list. The Draft Projects grid shows up to six projects and dynamically creates additional grid pages as needed.

    To install the Impersonation Web application:

    1.     Create a directory for the source files on your test Project Server computer, for example, C:\Project\.

    2.     Unzip all of the files in pj12Impersonation.zip to C:\Project\. The top-level folder in the zip file is named Impersonation, so the local directory for the Web application is C:\Project\Impersonation.

    3.     Using Internet Information Services (IIS) Manager on your Project Server computer, create a top-level Web site named, for example, Impersonation. Use the local path you created in Step 1 (C:\Project\Impersonation). Allow script access and executables. Disable anonymous access (use Integrated Windows Auth only). The Impersonation Web site can't be a SharePoint site, so set the port to something besides the ports that Project Web Access and Windows SharePoint Services use, such as 5636. Project Web Access typically uses port 80 for Windows authentication and port 81 for Forms authentication. Your Impersonation site URL would therefore be the following:

    http://ServerName:5636

    4.     The Impersonation Web site needs to run under a service account that is trusted by Project Server. Create a new application pool in IIS, for example, ImpersonationAppPool. On the Identity tab of the ImpersonationAppPool Properties dialog box, set the Configurable property of the Application Pool Identity to the same user account and password for the Project Web Access site administrator. To find the user account for configuring Project Web Access,  do the following:

    a.     Open the SharePoint 3.0 Central Administration site, and click Application Management.

    b.    On the Application Management page, click Create or configure this farm’s shared services.

    c.     Click the name of the SSP where Project Web Access is installed. For example, click SharedServices1 (Default).

    d.    On the Home page of the Shared Services Administration site, click Project Web Access Sites.

    e.     On the Manage Project Web Access Sites page, pause the mouse pointer over the site instance you want, click the down-arrow, and then click Edit.

    f.     On the Edit Project Web Access Site page, use the value in the Administrator Account text box. For example, set the Application Pool Identity to domain\pwaAdminName.

    5.     In IIS Manager, right-click the Impersonation Web site, click Properties, and then click the Home Directory tab. Set the Application pool  value to the ImpersonationAppPool you created in Step 4.

    6.     On the Impersonation Web site Properties page in IIS, click the ASP.NET tab, and then set the ASP.NET version to 2.0.50727.

    7.     In IIS Manager, right-click the local computer name, and then click Properties. Click Enable Direct Metabase Edit. Then use Notepad to open the metabase.xml file in %systemroot%\system32\inetsrv.  Search for the site name within an IISWebServer tag. Add the attribute NTAuthenticationProviders="NTLM”. For example, following is the complete element for the new Impersonation site.

    <IIsWebServer     Location ="/LM/W3SVC/784768436"

                AuthFlags="0"

                NTAuthenticationProviders="NTLM"

                ServerAutoStart="TRUE"

                ServerBindings=":5636:"

                ServerComment="Impersonation"

          >

    </IIsWebServer>

    8.     Restart IIS.

    9.     Copy the following files to the Bin subdirectory in the Impersonation Web site:

    ·         Microsoft.Office.Project.Server.Library.dll (copy from C:\Program Files\Microsoft Office Servers\12.0\Bin)

    ·         Microsoft.SharePoint.dll (copy from C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI)

    ·         Microsoft.SharePoint.Search.dll

    10.  Start Visual Studio 2005, and then open the Impersonation Web site from the Local IIS. When you click Save All, save the solution file as Impersonation.sln in C:\Project\Impersonation, for easy access.

    11.  The Web application needs the same validation key that Project Web Access uses for calling the PSI. The <machineKey> element in web.config sets keys to use for encryption and decryption of Forms authentication cookie data. The <machineKey> element allows developers to configure a validation key that performs message authentication checks on Viewstate data and Forms authentication tickets. <machineKey> can be declared at the computer, site, or application levels, but not at a subdirectory level . If you don't specify the correct <machineKey> attributes, then you get a Viewstate error.

    Set the <machineKey> attributes for your Project Server computer in web.config for the Impersonation site. Copy the complete <machineKey ...> line from the web.config file in your top-level site for Project Web Access (typically the default Web site), and replace the <machineKey … > line in the web.config file of the Impersonation site. The element should be all on one line, and a child of the <system.web> element, for example:

    <machineKey validationKey="7C9DF8E41A03170EFF870936E0FED824859E541C6CF5768F" decryptionKey="EAAECB67BFF6AED2F4F812ADE1967CB6AB33A94A9FDE400C" validation="SHA1" />

    12.  In the App_Code subdirectory, the Global.asax.cs file has all of the Application_Start, etc., methods that normally are in Global.asax. There’s no particular reason that Global.asax.cs is in App_Code, except that is where Visual Studio prefers to put it. You could put Global.asax.cs under Global.asax, if you add the following attribute to the Application directive in Global.asax:

    CodeBehind="Global.asax.cs"

    13.  In ImpersonationUtils.cs, change the SERVER_NAME constant to use your server name. Change the name of the Shared Service Provider (SSP) from SharedServices1 to the correct name for your SSP, and change the SSP port value if necessary. Build the Web site.

    14.  Your Impersonation Web site should now work. Test the site on the local Project Server computer and on a remote computer. If the Impersonation application works on the local Project Server computer but not on a remote computer, it is likely that the IISWebServer tag (Step 7) is not correct. If you get an HTTP 401 (unauthorized) exception when you first try to log on with a Windows account, check that the application pool owner is set properly (Step 4).

    When you log on Project Server with the Impersonation application, calls to the PSI use PROJECT_SERVER_URI for the value of the ResourceDerived.Url property. For Forms logon, the application sets the Url property to PROJECT_SERVER_FORMS_URI. During impersonation, the application sets the Url property to SSP_URI, for PSI calls to the ProjectDerived and SecurityDerived objects.

    There are comments in the code that explain several parts of the application. For an explanation of the proxy and derived classes for the PSI Web services, see How to: Write a Simple Impersonation Application in the Project 2007 SDK. The logon routines in the ImpersonationUtils.cs file are based on code in the ProjTool sample; for more information, see Using the ProjTool Test Application.

  • Project Programmability and Business Intelligence

    The MSDN WIKI is available for the Project 2007 SDK

    • 0 Comments

    Do you have something you’d like to see added or corrected in the Project 2007 SDK? Now you can add or edit Community Content on any page of the online SDK; all you need is a Microsoft Passport account.

     

    For example, the following pages have Community Content:

    ·         How to: Calculate Resource Availability in OLAP Cubes 

     

    ·         Equivalent PSI Methods

     

    For more information, see MSDN Community Content FAQ.

     

    --Jim

  • Project Programmability and Business Intelligence

    Adding a Web Reference to the PSI in the RTM Build

    • 1 Comments

    With the RTM build of Project Server, you may run into the following issue, where you are unable to add the Web Reference to the PSI:

    The workaround is to click on the “Service Description” link. This will allow you to add the Web Reference to your project.

    Chris

  • Project Programmability and Business Intelligence

    Reporting Database Diagrams

    • 8 Comments

    A number of people have asked for entity-relationship diagrams of the Reporting database (RDB) for Project Server 2007. The attached RDBSchema.zip file contains RDB Schema.vsd,a Microsoft Office Visio file with the main tables and views that most people use to make reports. The diagram pages are formatted for Tabloid (B)-size paper, 11 x 17 inches.

     

    The pages are:

    ·         EPM User Views: schemas of the Project, Resource, Task, Assignment, and related ByDay user views.

    ·         EPM Relationships: a simplified E-R diagram of the Project, Resource, Task, Assignment, and related ByDay tables.

    ·         Timesheet Tables

    ·         SharePoint Data: tables and user views for issues, risks, deliverables, and list item associations.

     

    The diagrams were created using Visio Professional 2007. Visio Standard doesn’t include the database capabilities. You can use Visio Professional 2003 SP2 or Visio Professional 2007 to reformat the pages for a larger printer or rearrange and create additional E-R diagrams. The first page, in particular, could use a larger page size to expand the views (MSP_EpmTask_UserView has a 4.8 point font size to fit on an 11 x 17 page). To see the list of all 108 tables and views in the RDB, click the Visio Database menu, click View, and then click Tables and Views. Create a new page and drag items from the Tables and Views pane to the page.

     

    If you don’t have one of the required versions of Visio, you can use the free Visio 2007 Viewer to see the diagram pages in Internet Explorer, although printouts of the large pages with the Visio Viewer are not as good as printing with Visio.

                                                                                    

    The Project 2007 SDK download includes the HTML Help version of the RDB Schema reference, pj12ReportingDB.chm. In the Visio diagram, the Notes fields for the tables, views, and columns include the same comments that are in the pj12ReportingDB.chm (with an update for the MSP_WssListItemAssociation table). To see the notes in Visio, right-click a table or view, and then click Database Properties. Click Notes in the Categories list to see the table or view notes; or click Columns to see the notes and other properties of all the columns in that table. You can modify the RDB to add your own tables, views, and columns for custom reports, and then add notes and custom content in the RDB Schema diagram with Visio.

     

    --Jim Corbin

  • Project Programmability and Business Intelligence

    Using the Reporting Database and Excel – Part 2

    • 1 Comments

    This is a continuation from my previous post. In this post, I will be illustrating how someone can import data from the Project reporting database from a SQL query. To began, we will need to open up Excel.

    The first step is to create a connection to the Project reporting database:

    1.       Click on the Data tab

    2.       Click “From Other Sources”

    3.       Click “From SQL Server”

     

    This will kick off a wizard that will guide you through the steps of setting up a connection to a SQL server. To run through the wizard you will need the following details:

    Server Name –This is the server name of the server that is hosting the reporting database

    Credentials – These are the credentials that are used to log on to the reporting database

    Database Name – The name of the reporting database, which for me is ProjectServer_Reporting

    You don’t have to connect to a table, since we are going to use the SQL query from the last post to get the data we need from the reporting database. Once we have created the connection, we need to configure the connection to use the SQL query. To change the configuration:

     

    1.       Click Connections from the Data tab

     

     

    2.       Select the Workbook Connection we just created

    3.       Click Properties

    4.       Select the Definition tab

    5.       Change the Command type to SQL

    6.       Copy and Paste the SQL Query into the Command text box

     

     

    7.       Click OK

    8.       Click Close

    Now we need to have the data show up in our workbook.

    1.       On the Data tab, click on Existing Connections

    2.       Select the connection that we created for the workbook

    3.       On the Import Data dialog, just click OK

     

     

    This will select the default settings and the returned table from our query will be returned to the selected worksheet.

    Now we have the data from the reporting database populated in the Excel worksheet where we can manipulate the data:

     

     

     

    Hope this helps!

    Chris Boyd

  • Project Programmability and Business Intelligence

    Using the Reporting Database and Excel – Part 1

    • 1 Comments

    I am not sure if this is a programmability post, but there have been many requests to do a post on writing a report in Excel that collects data from the reporting database. Since this will involve SQL and many developers who write code against Project Server will also need to write reports; this may be useful to the developer community. I am going to break this into two posts because it is fairly long and there is a logical break. In the first post, we will create the SQL query to retrieve the data from the reporting database. In the second post, I will describe the process of using the query in Excel to write reports.

    Before we begin, it should be known that there is a dedicated reporting database in Project Server 2007. This database is separate from the working and publish database that is heavily used by Project Professional and Project Server. Every time a project is published, or resource is updated, the data is pushed to the reporting database. The reporting database provides a supported interface for gathering project data via SQL queries. This reporting database has been setup to easily create reports.

    Someone posted that they wanted a report that showed the assigned work for each resource, grouped by project and then by month. This will allow project managers to better understand the allocation of resources by month. I will try my best writing the query for this, but I shell add the warning that this has not been tested. If you find a mistake, or a better way of writing the query, please post it as a comment.

    To begin, we need to write a SQL query against the reporting database to get at the data we need. I wrote the SQL query in stages. The first step was to get the assignment data from the projects. To do that, I wrote the following query:

    SELECT

    base.ResourceUID as 'Resource',

    assn.TimeByDay as 'Day',

    assn.AssignmentWork as 'Assigned'

    FROM

    MSP_EPMAssignmentByDay_UserView  AS assn

    INNER JOIN MSP_EPMAssignment_UserView AS base

                ON (assn.AssignmentUid  = base.AssignmentUID)

    This gives us all the assignments for each resource by name. We will also want the project name that the assignment is coming from. To do this, we will have to do another join on the MSP_EmpProject_UserView:

    SELECT

    base.ResourceUID as 'Resource',

    assn.TimeByDay as 'Day',

    assn.AssignmentWork as 'Assigned',

    ProjectName

    FROM

    MSP_EPMAssignmentByDay_UserView  AS assn

          INNER JOIN MSP_EPMAssignment_UserView AS base

                ON (assn.AssignmentUid  = base.AssignmentUID)   

    INNER JOIN MSP_EpmProject_UserView

    ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID

    Now we have all the assignment data for each resource. For this report we will also want to capture assignments outside of a project. An example of this would be vacation time. To get at this information, we are going to query the Timesheet tables in the reporting database.

    SELECT

    MSP_TimesheetResource.ResourceUID as 'Resource',

    ts.TimeByDay as 'Day',

    ts.ActualWorkBillable as 'Assigned'

    FROM

    MSP_TimesheetActual AS ts

          INNER JOIN MSP_TimesheetLine AS tl

                ON (ts.TimesheetLineUID = tl.TimesheetLineUID)

    INNER JOIN MSP_TimesheetClass AS tc

    ON tl.ClassUID = tc.ClassUID

    INNER JOIN MSP_Timesheet AS tsowner

    on tl.TimesheetUID = tsowner.TimesheetUID

    INNER JOIN MSP_TimesheetResource

    ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID

    WHERE

    tc.[Type] = 2

    This returns all the timesheet lines for none project work.

    Now we have all the assignments. The next step is to combine the two queries. To do this, I used the union statement. You will notice that in the timesheet query we do not have a project name in the select. We are going to add the project name to the select by adding 'None Project Time' as ProjectName to the select statement. Here are the combined queries:

    SELECT

    base.ResourceUID as 'Resource',

    assn.TimeByDay as 'Day',

    assn.AssignmentWork as 'Assigned',

    ProjectName

    FROM

    MSP_EPMAssignmentByDay_UserView  AS assn

          INNER JOIN MSP_EPMAssignment_UserView AS base

                ON (assn.AssignmentUid  = base.AssignmentUID)   

    INNER JOIN MSP_EpmProject_UserView

    ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID

     

    Union ALL

     

    SELECT

    MSP_TimesheetResource.ResourceUID as 'Resource',

    ts.TimeByDay as 'Day',

    ts.ActualWorkBillable as 'Assigned',

    'None Project Time' as ProjectName

    FROM

    MSP_TimesheetActual AS ts

          INNER JOIN MSP_TimesheetLine AS tl

                ON (ts.TimesheetLineUID = tl.TimesheetLineUID)

    INNER JOIN MSP_TimesheetClass AS tc

    ON tl.ClassUID = tc.ClassUID

    INNER JOIN MSP_Timesheet AS tsowner

    on tl.TimesheetUID = tsowner.TimesheetUID

    INNER JOIN MSP_TimesheetResource

    ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID

    WHERE

    tc.[Type] = 2

    So now we have all the assignment data together. The last step is join with the resource table to get the name of the resources and to group by various fields to roll up the data.

    SELECT

    ProjectName,

    MSP_EpmResource.ResourceName,

    CAST(YEAR(Day) as Varchar(4)) + '-' + CAST(MONTH(Day) as Varchar(2)) as Month,

    SUM(Assigned) as Assigned

    FROM

    MSP_EpmResource

    Right Join

    (

     

    SELECT

    base.ResourceUID as 'Resource',

    assn.TimeByDay as 'Day',

    assn.AssignmentWork as 'Assigned',

    ProjectName

    FROM

    MSP_EPMAssignmentByDay_UserView  AS assn

          INNER JOIN MSP_EPMAssignment_UserView AS base

                ON (assn.AssignmentUid  = base.AssignmentUID)   

    INNER JOIN MSP_EpmProject_UserView

    ON base.ProjectUID = MSP_EpmProject_UserView.ProjectUID

     

    Union ALL

     

    SELECT

    MSP_TimesheetResource.ResourceUID as 'Resource',

    ts.TimeByDay as 'Day',

    ts.ActualWorkBillable as 'Assigned',

    'None Project Time' as ProjectName

    FROM

    MSP_TimesheetActual AS ts

          INNER JOIN MSP_TimesheetLine AS tl

                ON (ts.TimesheetLineUID = tl.TimesheetLineUID)

    INNER JOIN MSP_TimesheetClass AS tc

    ON tl.ClassUID = tc.ClassUID

    INNER JOIN MSP_Timesheet AS tsowner

    on tl.TimesheetUID = tsowner.TimesheetUID

    INNER JOIN MSP_TimesheetResource

    ON tsowner.OwnerResourceNameUID = MSP_TimesheetResource.ResourceNameUID

    WHERE

    tc.[Type] = 2

     

    ) b

    on MSP_EpmResource.ResourceUID = Resource

    GROUP BY

    ProjectName,

    Resource,

    CAST(YEAR(Day) as Varchar(4)) + '-' + CAST(MONTH(Day) as Varchar(2)), MSP_EpmResource.ResourceName

    Order By

    Month

    You will notice that I have stripped out the day from the date. This is how we can roll up the assignments for each resource by month.

    We now have our completed query! The next step is to get this data into Excel…

    Chris Boyd

     

  • Project Programmability and Business Intelligence

    The Microsoft Office Project 2007 SDK

    • 1 Comments

    The Microsoft Office Project 2007 SDK has been published to MSDN:

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

    The down load version should be available in the next few days. I will post a link once is becomes available.

    Though this is the RTM release of the SDK, we will be continually updating the SDK though out the life of the product. One source of SDK articles is this blog. So, if you have samples you would like see posted, please comment with your suggestions.

    Chris Boyd

  • Project Programmability and Business Intelligence

    Microsoft Project 2007 Client Developer Help is Now Available Online

    • 0 Comments

    Jack Dahlgren  on his Project Blog talks about the release of Microsoft Project 2007 client developer documentation online.

     

    This is a great resource for Project VBA and other Project client developers.

     

Page 10 of 11 (255 items) «7891011