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

    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

    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

    Adding a Project to a Category

    • 3 Comments

    Brian Smith from PSS has passed along this sample that we thought might be helpful:

    The scenario here is that you have a lookup table that shows the categories you want users to select from when creating a project, and then the GUID for the "real" security category is held in the description for the lookup table value.  You make the CF that feeds from the Lookup Table a required Project Level text field.  The Project.Created event fires and the dataset is read - the custom field identified and the GUID of the security category is then used to add the project to the security category.

    No error checking or exception handling is shown - you can do this bit. You would also need to set the categories to the rule "only projects...".  I've hardcoded my lookup table and a reference required to the Microsoft.Office.Project.Server.Library and Events. A Web References to LookupTable, Project, Security and LoginWindows is also required.

    The code will run as the user running the services - so you will either need that account to have PWA permissions or to change to use impersonation.

    using System;
    using System.Collections.Generic;
    using System.Net;
    using System.Diagnostics;
    using System.Text;
    using Microsoft.Office.Project.Server.Events;
    using Microsoft.Office.Project.Server.Library;

    namespace TestEventHandler
    {

        public class AutoCategory:ProjectEventReceiver
        {

            public override void OnCreated(PSContextInfo contextInfo, ProjectPostEventArgs e)
            {

                // cfGuid holds CF for Project Category
                Guid cfGuid = new Guid("9bbc698f-5c1d-4f8d-a3d0-163006416bf2");

                // ltGuid holds LT for Categories
                Guid ltGuid = new Guid("625bab60-4427-4f0b-941b-9860d1293338");

                // lt_Struct_Uid gets the id for the selected LT value
                Guid lt_Struct_Uid = new Guid("00000000-0000-0000-0000-000000000000");

                // securityCategoryGuid gets the Security Categorty Guid from the Descriptio field in the lookup table
                Guid securityCategoryGuid = new Guid("00000000-0000-0000-0000-000000000000");

                // 32 is used to just get the CF entities from the readProjectEntities
                const int PROJECT_ENTITY_TYPE_PROJECTCUSTOMFIELD = 32;

                Guid SECURITY_CATEGORY_OBJECT_TYPE_PROJECT = new Guid("1771B1C0-6E26-4FB3-A480-C798AB506E82");

                WebSvcLoginWindows.LoginWindows loginWindows = new TestEventHandler.WebSvcLoginWindows.LoginWindows();
                WebSvcProject.Project project = new TestEventHandler.WebSvcProject.Project();
                WebSvcSecurity.Security security = new TestEventHandler.WebSvcSecurity.Security();
                WebSvcLookupTable.LookupTable lookupTable = new TestEventHandler.WebSvcLookupTable.LookupTable();

     

                //login to Project Server - this assumes the event service has a login with permissions

                // Impersonation would be better
                loginWindows.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/LoginWindows.asmx";
                loginWindows.Credentials = CredentialCache.DefaultCredentials;
                loginWindows.Login();

                // Get the dataset
                project.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/Project.asmx";
                project.Credentials = CredentialCache.DefaultCredentials;

                lookupTable.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/LookupTable.asmx";
                lookupTable.Credentials = CredentialCache.DefaultCredentials;

                security.Url = @"https://SERVER_NAME/ProjectServer/_vti_bin/PSI/security.asmx";
                security.Credentials = CredentialCache.DefaultCredentials;

                WebSvcProject.ProjectDataSet dsProjectDataSet = new TestEventHandler.WebSvcProject.ProjectDataSet();

                dsProjectDataSet = project.ReadProjectEntities(e.ProjectGuid, PROJECT_ENTITY_TYPE_PROJECTCUSTOMFIELD, TestEventHandler.WebSvcProject.DataStoreEnum.WorkingStore);

                for (int i = 0; i < dsProjectDataSet.ProjectCustomFields.Count; i++)
                {
                    if (dsProjectDataSet.ProjectCustomFields[i].MD_PROP_UID == cfGuid)
                    {
                        lt_Struct_Uid = dsProjectDataSet.ProjectCustomFields[i].CODE_VALUE;
                    }
                }

                Guid[] arrayLtUid = new Guid[1]{ltGuid};
                WebSvcLookupTable.LookupTableDataSet dsLookupTable = new TestEventHandler.WebSvcLookupTable.LookupTableDataSet();
                dsLookupTable = lookupTable.ReadLookupTablesByUids(arrayLtUid, false, 1033);

                for (int i = 0; i < dsLookupTable.LookupTableTrees.Count; i++)
                {
                    if (dsLookupTable.LookupTableTrees[i].LT_STRUCT_UID == lt_Struct_Uid)
                    {
                        securityCategoryGuid = new Guid(dsLookupTable.LookupTableTrees[i].LT_VALUE_DESC.ToString());
                    }
                }           

    WebSvcSecurity.SecurityCategoriesDataSet dsSecurityCategories
       = new TestEventHandler.WebSvcSecurity.SecurityCategoriesDataSet();

    // Read the existing values for the security category into the dataset
    dsSecurityCategories = security.ReadCategory(securityCategoryGuid);

                // Get a new objects row to put the created project into

                WebSvcSecurity.SecurityCategoriesDataSet.SecurityCategoryObjectsRow dsSecurityCategoryObjectsRow
    = dsSecurityCategories.SecurityCategoryObjects.NewSecurityCategoryObjectsRow();

                //Set the values
                dsSecurityCategoryObjectsRow.WSEC_OBJ_TYPE_UID = SECURITY_CATEGORY_OBJECT_TYPE_PROJECT;
                dsSecurityCategoryObjectsRow.WSEC_CAT_UID = securityCategoryGuid;
                dsSecurityCategoryObjectsRow.WSEC_OBJ_UID = e.ProjectGuid;

                // Add the row to the dataset and then pass to SetCategories to update
                dsSecurityCategories.SecurityCategoryObjects.AddSecurityCategoryObjectsRow(dsSecurityCategoryObjectsRow);

                // Create an EventLog instance and assign its source.
                EventLog myLog = new EventLog();
                myLog.Source = "Project Event Handler";

                // Get information from the event arguments, and

                // write an entry to the Application event log.
                string userName = contextInfo.UserName.ToString();
                string projectName = e.ProjectName.ToString();
                string secCatUid = securityCategoryGuid.ToString();
                int eventId = 3652;
                string logEntry;

     

                logEntry = "User: " + userName +

                        "\nProject: " + projectName +

                        "\nSecurity Category Uid: " + secCatUid;

                    myLog.WriteEntry(logEntry, EventLogEntryType.Information, eventId);

                security.SetCategories(dsSecurityCategories);           

            }
        }
    }

     

  • Project Programmability and Business Intelligence

    Improve the Performance of Building the Project Server Data Analysis Cube

    • 3 Comments

    Jack Li has passed along a great tip on how to improve the performance of building the Project Server Data Analysis Cube: 

     

    Recently we have worked with a big enterprise customer using Project Server. They ran into an issue where Project Server reporting component which uses Analysis Server ran very slow during cube population. Eventually, the issue was tracked down to a single query.  The query would take 17 hours to finish for 1 year of data. nHowever, if one applied force order to the query, it would finish within minutes.

     

    Customer had tight deadline and need a solution fast. It would be easy if one could just modify the query to add force order option.  Unfortunately, the query was generated by Analysis Service based on the cube definition. In other words, it couldn’t  be changed.

     

    Enter into plan guide for the  rescue. SQL Server 2005 gives you various means to influence the query plan for a query without adding hints to the query itself directly. You don’t need to change the query which may not be possible without changing the application.

     

    The simplest one is the use use sp_create_plan_guide stored procedure to add a query hint like force order etc.

     

    Here is exactly what we did for our customer:

     

    1)    We  used profiler trace to trace the exact query

    2)    We copied the exact query text and use sp_create_plan_guide to create the plan guide:

     

    EXEC sp_create_plan_guide N'guide_forceorder',

        N'<exact query>',

        N'SQL',

        NULL,

        NULL,

    N'OPTION (force order)'

     

     

    Steps are fairly easy yet it is tricky to implement. If you are not doing it correctly, SQL Server may not use the plan guide you just created.  Here are a few things you need to watch for:

     

    1)    ensure you have the exact text.  Even if you miss a space character, the plan won’t match.   To ensure you get exact text, you should launch  profiler trace while running the application to get the query.   See http://msdn2.microsoft.com/en-us/library/ms188255.aspx for more details.

     

    2)    There are two ways you can verify if SQL actually uses your plan guide. The first one obviously is that your query finishes faster.  Another way is that you will see PlanGuideDB word in the xml plan. So if you do set showplan_xml on and then run the query, you will get xml showplan. If you search the text, you will find PlanguideDB.  

     

    There are more advanced ways to use plan guide including parameterization or use plan. Please refer SQL Server 2005 books online for these advanced topics.

     

     

  • 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

    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

    Do you EPMU?

    • 3 Comments

    Microsoft EPM University offers a comprehensive 4 to 5 days educational package for customers and partners who want to quickly ramp-up with EPM solution via rich instructor led online experience.

    The courses are designed to build and enhance the required knowledge to successfully plan, deploy, configure and extend Microsoft Office EPM solution. You can review course details, schedules and register for the course of your choice. The courses currently being offered are:

    You get access to EPMU’s virtual classroom environment that includes a professional instructor and hosted computer environment to run your labs on.

    Check out the schedule and  be sure to register soon to get a spot in the class! The very last class begins in June 2009!

  • 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

    Calling all Project Server Developers from the Expanding the Project Server Developers Community Growth Effort!

    • 3 Comments

    Announcing the GROW THE SERVER-SIDE SWEEPSTAKES to be held between April 9 and April 30, 2008.

    Enter for a chance to win * a free pass to the Tech Ed North America 2008 Developers Conference in Orlando, Florida June 3-6.

    Five other lucky winners will be drawn to receive a Project Server Developers Community plaque and pen prize package.

    The drawing for the winners will take place May 1, 2008.

    Get your registration in now and begin making your postings to the PS Developers Community Newsgroup to accumulate entries. 

    Read the official rules attached for details about entries.

    Register by:  sending an email before the close of the sweepstakes period with the subject “Server-Side Sweepstakes” to gssw@microsoft.com with the following information:

    *NO PURCHASE NECESSARY. Open to technology professionals and enthusiasts 21+. Game ends April 30, 2008. Void in Quebec and where prohibited by law. Some geographic restrictions apply. For full rules, see the attached document.

  • Project Programmability and Business Intelligence

    Canceling the Before Save Event

    • 2 Comments

    Hello,

    A good question came in yesterday; how to cancel the Project Before Save event in Project client:

    Public Sub GlobalApplication_ProjectBeforeSave(ByVal pj As Project, ByVal SaveAsUi As Boolean, Cancel As Boolean)
     
             Cancel = True
          
    End Sub

    (For demonstration and simplicity, this event handler just cancels all saves. You would most likely want to add validation and if it fails, cancel the save)

    Unfortunately the above code will not work. Instead, you will want to use ProjectBeforeSave2:

    Public Sub GlobalApplication_ProjectBeforeSave2(ByVal pj As Project, ByVal SaveAsUi As Boolean, Info As EventInfo)

             Info .Cancel = True

    End Sub

    Hope this helps,

    Chris Boyd

     

    Technorati tags: , ,
  • Project Programmability and Business Intelligence

    Search Project Server data using MOSS' BDC and Enterprise Search - CodePlex Solution Starter

    • 2 Comments

    Bonjour,

    Do you ever wonder how many projects are in a specific phase? Would you like to know the attributes of a specific resource? Are you looking when a specific milestone ends? Do you have users in your organization that are not part of the Project Server resource pool but still need to access the data? Being able to easily search valuable information is becoming a key feature in any organization. 

    This solution starter demonstrates how to leverage SharePoint Server’s Business Data Catalog (BDC) to index project server data and enable users to search the different objects (projects, tasks, resources, lookup tables…) using SharePoint Server’s Enterprise Search, and give you answers to all the questions above.  Out of the box the Project Server data is not indexed (only the SharePoint objects are indexed for instance Issues, Risks, Documents…) and thus users cannot search the content contained in the Reporting database. This solution starter will thus enable you to mine the PS 2007 gold mine and find the nuggets you were looking for very easily!

    http://www.codeplex.com/EPMBDC

    This solution starter was created for a presentation I delivered last week at the SharePoint Conference to illustrate one of the benefits of deploying PS 2007 with SharePoint Server ( and not just WSS V3).

    clip_image001clip_image002


    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

     

    Technorati tags: , ,
  • 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

    Connection Strings…

    • 2 Comments

    Hey,

    Here is a great post:

    http://projectserver.cz/cms/index.php?itemid=35

    It shows how to get the connection string to the Project Server 2007 Reporting Database (and the other three databases which are not supported programming interfaces). This is extremely helpful when creating PSI extensions. Just one caveat, this may break after a hot fix or service pack install.

    Chris Boyd

  • Project Programmability and Business Intelligence

    Project Server Database Timeout

    • 2 Comments

    Hey,

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

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

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

    Chris Boyd

     

    Technorati Tags: ,,
  • Project Programmability and Business Intelligence

    MSDN Webcast: PSI Programming Overview, Demos and Q&A

    • 2 Comments

    This is a little late, I missed that I didn’t post this.  Q&A and demos from the March 12th webcast.

    Question Answer
    Microsoft has BPOS (Business Productivity Online Services) which includes hosted Outlook and Hosted SharePoint, can SharePoint Server be installed on premise with its SharePoint components in BPOS and can the user extend this configuration using the things you are showing in this series? Project Server is not hosted in the BPOS. We are looking into this for the next release of Project Server
    Could you please send the link for good documentation on registering project server events in a farm environment. An event is failing in a farm environment but working in a single server environment The SDK has good documentation on registering events.  But there is one little detail that doesn’t jump out in the documentation, I’m not even positive it’s in there.  Events are registered with SharePoint, so when you scale out the information is in the config database and goes to all servers in the farm.  What doesn’t happen is the binary is not copied to each machine in the farm, you have to do that manually.
    Why don't I have the option of add Microsoft.office.project.server in the using? I added reference to Microsoft.office. The Project Server assemblies are deeper than Microsoft.Office.  Assemblies you would typically add then reference via “Using” when working with Project Server would be:
    Microsoft.Office.Project.Schema
    Microsoft.Office.Project.Server.Library
    Microsoft.Office.Project.Server.Events.Receivers
    So does the SDK tell me or instruct me as to what prerequistists have to exist in order to be able to use the PSI? meaning, do I have to be developing on the box that has PServer installed? or can I simply reference a few dll's pointing my uri to the project dev environment? Yeah there is a pretty good overview:
    http://msdn.microsoft.com/en-us/library/bb456485.aspx.

    The way I develop is with a client machine and a VM (with Project Server installed).  I go into the VM and run this command:
    regsvr32 /u C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\shfusion.dll
    That allows you to look at the GAC as files.  Then I copy everything that has Microsoft.Office to my client machine.  I tend to work with all the different Office products so I pull a lot of stuff I don’t necessarily need.  Once I have the files on my client machine, then I register them in the GAC.  Remember if you apply a service pack to your VM you’ll need to grab the files again.
    Oh and remove the /u from the above command and rerun to get the files system back to normal.
    Most of the samples of PSI I have seen are developed using c#, not much on VB. Do you recommend to use C# over VB? VB.Net is just as good for this as C#.  Actually any .NET language will work great.  I “cut my teeth” in C.  So my natural progression was from C to C++ to C#.
    Hi, Resource demo...did I understand correctly? You read DS of all resources, check out and update one, and send "whole' DS back across web services for one resource update? I read the DS for one resource, check it out, then send the complete dataset for the single resource back.
  • Project Programmability and Business Intelligence

    MSDN Webcast: PSI Review, Demos and Q&A

    • 2 Comments

    Hi All,

    Thank you for attending my PSI review MSDN webcasts, I really enjoyed delivering them.  I think if I deliver again it’ll need to be three webcasts, I barely finished part A, and left out three web services in part B.  Below is some of the Q&A I’ve gotten.

    Question Answer

    Create a custom timesheet import UI. So create timesheet from data in Excel for example (each row as a new timesheet maybe?)

    This is totally possible through the timesheet and statusing web services. The biggest challenge will be keeping your external line items names in sync with internal ones you define in Project Server.
    Right now the rates table, you can only put 5 rates in there. Explore ways to put more than 5 rates per resource. Your right that there are only 5 rate available. There are a couple possibilities, all involving custom fields. You would save your extra rate information in a custom field. Then by some event triggering (either client or server side) you could move the appropriate cost information into the table. It’s not the best solution, but could be the start of a workaround for you.

    I want to work on top of various projects that have a certain CustomField value. Going into every project's dataset takes waaaay too long. Is there a better way to do this with PSI?

    There really is no shortcut.  My best advice is to write some helper methods that get you to the values you want.

    Is it ok to use VB for all this stuff or is it much better to use C#?

    VB.Net is just as good for this as C#.  Actually any .NET language will work great.  I “cut my teeth” in C.  So my natural progression was from C to C++ to C#.

    I am trying to sync the values in a Look up table with an external SQL table so the two stay in sync. What is the best way to do this? the table is a customer table from the accounting system

    Going from the accounting system to Project Server should be easy with all the PSI calls available to update the business entities (Project, Resource, etc.).  Going from Project Server to the accounting system is more challenging.  The event system in Project Server will only tell you an entity changed, not the specifics of what changed.  So you’ll need to detect the change and then go into the entity to check for changes.

    What types of programming tasks are going to take existing GUIDs, and what tasks will require us to generate our own GUID? Queueing? Object Creation? It would seem like Project Server would want to own the GUID, for sync'ing and stuff, right?

    Calls that create new entities will have you create a new Guid, for instance QueueCreateProject.  The most common thing you’ll create a new Guid for is the JobUid that’ll you’ll pass to queue enabled calls.

    I don't mean to oversimplify, but if we have an admin backup scheduled daily, isn't that what you are referring to as the 'archive' (although with code you can have more control rather than an all or nothing).

    Archiving is moving entities to the archive database so they are no longer taken into account in the operational system.  BUT… they are available to restore when you need.  It’s way more complicate than that, you should look at the books online to get a good handle on the archive process.

    Do you have any customers that are automatically tweaking the queue configuration settings in a production environment?

    None that I know of.

    For AD Credentials, I use the NetworkCredentials class, which class do I use for the Forms logins? I don’t use it, just curious.... It blocks my outbound webservices connection, I can't seem to authenticate properly, any tips?

    Use the LoginForms web service and pass the username and password via that.
  • Project Programmability and Business Intelligence

    MSDN Webcast: Project Server Events and Workflows, Demos & Q&A

    • 2 Comments

    Hi All,

    Thank you for attending my MSDN webcast Project Server Events and Workflows.  Don’t forget about next weeks webcast on Project Client programmability.   Below is some of the Q&A I got during the webcast.  Attached is the demos from the webcast.

    Question Answer

    Will you chat at all about Project Server 2010? Are there any plans for changes in the new version?

    Still too early to talk about Project Server 2010 other than to to say I’m looking forward to it.  We’re a few months away from announcing the features. 

    What should you be thinking about as a developer?  Project is committed to the PSI and designed it to be extensible.  The new functionality will be extensions to existing functionality.  This means your work today should be backward compatible.  I did say should so please don’t shoot me if there are a couple “gotchas.”

    Is the command (i.e save project) already queued, when an event is raised?

    The event will be raised when the call executes from the queue.

    Based on your description of pre and post, the "project published" event is raised after save to the db. Would you say its best to eval business rules afterward, log violations, notify user and delete from db? is there an ability to only delete from PDB and leave in DDB so user can correct and attempt re-publish?

    I hate to waffle in my answers, but in this case it depends on the context.  I put together a little matrix below to help with this question.

    Is it microsoft.office.project.schema.dll or microsoft.office.project.server.schema.dll?

    microsoft.office.project.schema.dll is the correct DLL.

    Is there anything to pay special attention for (i.e. concurrency, locking, etc.) with events on heavy loaded systems or is this solved by "serialized" execution through the project server queue?

    The queue helps to solve issues with heavily loaded systems.  Specifically with events you need to pay attention to the length of processing in pre-events.  In a pre-event you are disrupting the flow of Project Server, you should keep your processing to a minimum.

    Using events to enforce business rules

      Easy Rollback Difficult Rollback
    Short Business Rule Evaluation Evaluate in pre-event, rollback unnecessary. Evaluate in pre-event, rollback unnecessary.
    Long Business Rule Evaluation Consider using a post event. This is the hardest case.  You’ll really need to evaluate the complexity of the rollback.  Maybe you could break the business rule evaluation in phases?
  • Project Programmability and Business Intelligence

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

    • 2 Comments

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

     

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

     

    High-level logic overview of ReportingPSISecurity

  • Project Programmability and Business Intelligence

    Project Standard 2010, Project Professional 2010 activation - processes and Q&A

    • 2 Comments

    In case you got Project 2010 Beta and you are interested in the activation process – this might be interesting for you.

     

    Microsoft Office 2010 - including Project Standard 2010, Project Professional 2010 and Visio 2010 use the same volume activation technology as Windows 7 and Windows Vista. If you have already set up a Key Management Service (KMS) host to activate Windows, you can use the same host to activate Office 2010 after a few steps.

    Volume Activation Methods

    http://technet.microsoft.com/en-us/office/ee691939.aspx

    You can use the following methods to activate Office 2010 by using Office Activation Technologies, which are the same methods that are used for Windows Vista and later versions of Windows:

    · Multiple Activation Key (MAK). With a MAK, clients activate Office 2010 online with the Microsoft hosted activation servers or by telephone.

    · Key Management Service (KMS). KMS uses a KMS host key to activate a KMS host computer and establish a local activation service in your environment. Office 2010 connects to the local KMS host for activation.

    · A combination of KMS and MAK.

    For detailed information, see Overview of volume activation for Office 2010 in the technical library.

    For information about when you would use each activation method, see the four scenarios described in detail in Volume activation quick start guide for Office 2010 in the technical library.

    Q&A

    · Q: How could the product be activated in non-connected environments (e.g. without Internet access)?

    · A: The option is to use MAK and activate by telephone or by a MAK proxy activation using the VAMT 2.0 – more information from Ted is available here: http://social.technet.microsoft.com/Forums/en/office2010volact/thread/2fab72e4-cd14-41ce-aa68-912c149e0529

    · Q: Could I activate the Project Professional 2010 Beta by telephone?

    · A: Unfortunately no – this option is not available for Beta products – you can however use the MAK proxy activation (see above) if your computer does not have access to the internet or your organization does not have KMS set-up.

    · Q: Where do I find the MAK or a “Product Key” for Project Professional 2010 Beta?

    · A: After registering for download on Microsoft Project Professional 2010 Beta page the key is generated for you, you can re-visit this page anytime.

    · Q: How do I enter MAK or a “Product Key” for Project Professional 2010 Beta?

    · A: run the application, click “File”, then choose “Help” and on the right hand side click “Change Product Key”.

  • 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

    Synchronizing Membership Provider for Project Workspaces

    • 2 Comments

    Back on May 17th I posted code that showed how to update the workspace data with the RDB:

    http://blogs.msdn.com/project_programmability/archive/2007/05/17/syncing-project-workspaces-with-the-rdb.aspx 

    Here is a similar program to synchronize project workspace membership. The interesting twist is that I show how to be kinder to the Project Queue by adding a few jobs at a time and waiting for them to complete.

    Here is the code: 

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net;
    using System.Data;
    using System.Web.Services.Protocols;
    using System.Diagnostics;
    using PSLibrary = Microsoft.Office.Project.Server.Library;

    namespace WorkspaceUpdate
    {
        class Program
        {
            static void Main(string[] args)
            {
                int count = 0;
                bool verbose = false;           // Verbose Output switch
                Guid job = Guid.Empty;          // The latest job submitted to the queue.
                int timeOut = 60;               // Default timeout before terminating the queue job

                string ls_projURL = "";
                const string PROJECT_SERVICE_PATH = "_vti_bin/psi/Project.asmx";
                const string WSSINTEROP_SERVICE_PATH = "_vti_bin/PSI/WSSInterop.asmx";
                const string WSQUEUESYSTEM_SERVICE_PATH = "_vti_bin/PSI/QueueSystem.asmx";

                if (args.Length == 0 || args.Length > 3)
                {
                    Message();
                }
                else if (args[0] == "/?")
                {
                    Message();
                }
                else
                {
                    ls_projURL = args[0];

                    if (args.Length > 2 && args[2].ToLower() == "verbose")
                    {
                        verbose = true;
                    }

                    try
                    {
                        timeOut = Convert.ToInt32(args[1]);
                    }
                    catch
                    {
                        Event("Warning: Invalid timeout, defaulting to 60 seconds.", EventLogEntryType.Warning, verbose);
                    }
                   
                    WSProject.Project ws_Project = new WSProject.Project();
                    WSSInterop.WssInterop ws_WssInterop = new WSSInterop.WssInterop();
                    WSQueueSystem.QueueSystem qsWS = new WSQueueSystem.QueueSystem();
                  
                    if (!ls_projURL.EndsWith("/"))
                    {
                        ls_projURL += "/";
                    }

                    try
                    {


                        ws_Project.Url = ls_projURL + PROJECT_SERVICE_PATH;
                        ws_Project.Credentials = CredentialCache.DefaultCredentials;

                        ws_WssInterop.Url = ls_projURL + WSSINTEROP_SERVICE_PATH;
                        ws_WssInterop.Credentials = CredentialCache.DefaultCredentials;

                        qsWS.Url = ls_projURL + WSQUEUESYSTEM_SERVICE_PATH;
                        qsWS.Credentials = CredentialCache.DefaultCredentials;
                       

                        Guid lo_projGUID;
                        string ls_projName;
                       
                        WSProject.ProjectDataSet lo_projs = null;
                        WSProject.ProjectDataSet lo_projDS;

                        try
                        {
                            lo_projs = ws_Project.ReadProjectList();

                            DataRowCollection lo_projects = lo_projs.Tables[lo_projs.Project.TableName].Rows;

                            for (int i = 0; i < lo_projects.Count; i++)
                            {
                                lo_projGUID = new Guid(lo_projects[i][0].ToString());
                                ls_projName = lo_projects[i][1].ToString();

                                try
                                {
                                    lo_projDS = ws_Project.ReadProjectEntities(lo_projGUID, 1, WSProject.DataStoreEnum.PublishedStore);

                                    // Check if the Project has a Workspace
                                    if (lo_projDS.Tables[lo_projDS.Project.TableName].Rows[0][lo_projDS.Project.WSTS_SERVER_UIDColumn.ColumnName] != null && lo_projDS.Tables[lo_projDS.Project.TableName].Rows[0][lo_projDS.Project.WSTS_SERVER_UIDColumn.ColumnName].ToString() != "")
                                    {
                                        Message("Synchronizing Workspace for Project" + ls_projName, verbose);
                                       
                                        //Wait to let the server process the work
                                        if (count % 4 == 3)
                                        {
                                            if (WaitForQueue(timeOut, job, qsWS) == false)
                                            {
                                                Event("Warning: Queue Job not Processed for Project:" + ls_projName + " Check Project Server Queue.", EventLogEntryType.Warning, verbose);
                                            }
                                        }

                                        job = Guid.NewGuid();

                                        ws_WssInterop.QueueSynchronizeMembershipForWssSite(lo_projGUID, job);


                                        count++;
                                    }
                                    else
                                    {
                                        Message("Notice: Project" + ls_projName + " does not have a workspace.", verbose);
                                    }
                                }
                                catch (SoapException lo_ex)
                                {
                                    PSLibrary.PSClientError psiError = new PSLibrary.PSClientError(lo_ex);
                                    PSLibrary.PSErrorInfo[] psiErrors = psiError.GetAllErrors();

                                    if (psiErrors.Length == 1)
                                    {
                                        if (psiErrors[0].ToString() == "ProjectNotFound")
                                        {
                                            Message("Notice: Project" + ls_projName + " is not published.", verbose);
                                        }
                                    }

                                }
                            }

                            Event("Successfully Synchronized Membership for Workspaces", EventLogEntryType.Information, verbose);
                        }

                        catch (WebException lo_ex)
                        {
                            Event("Error:" + lo_ex.Message, EventLogEntryType.Error, verbose);
                        }
                        catch (Exception lo_ex)
                        {
                            Event("Unknown Error:" + lo_ex.Message, EventLogEntryType.Error, verbose);
                        }
                    }
                    catch (UriFormatException lo_ex)
                    {
                        Event("Unknown Error:" + lo_ex.Message, EventLogEntryType.Error, verbose); ;
                    }
                }
            }

            private static bool WaitForQueue(int timeOut, Guid jobId, WSQueueSystem.QueueSystem qsWS)
            {
               

                int sleep = 3;
                int timeSlept = 0;        // Total time slept (seconds)
                bool jobSuccess = false;
                string xmlError;

                WSQueueSystem.JobState jobState; // Status of the queue job

                timeOut = timeOut * 1000;

                while (true)
                {
                    jobState = qsWS.GetJobCompletionState(jobId, out xmlError);

                    if (jobState == WSQueueSystem.JobState.Success)
                    {
                        jobSuccess = true;
                        break;
                    }
                    else if (jobState == WSQueueSystem.JobState.Unknown
                        || jobState == WSQueueSystem.JobState.Failed
                        || jobState == WSQueueSystem.JobState.FailedNotBlocking
                        || jobState == WSQueueSystem.JobState.CorrelationBlocked
                        || jobState == WSQueueSystem.JobState.Canceled)
                    {
                        jobSuccess = false;
                        break;
                    }
                    else if (timeSlept > timeOut)
                    {
                        jobSuccess = true;
                        //qsWS.CancelJobSimple(jobId);
                        break;
                    }

                    System.Threading.Thread.Sleep(sleep * 1000);

                    timeSlept = +sleep * 1000;
                }

                return jobSuccess;
            }

            static private void Message()
            {
                System.Console.WriteLine("");
                System.Console.WriteLine("WorkspaceUpdate url timeout [verbose]");
                System.Console.WriteLine("  url - The URL to the project server.");
                System.Console.WriteLine("  timeout - Seconds to wait for the queue job to process the User Sync");
                System.Console.WriteLine("  verbose - An optional parameter that outputs progress.");
            }

            static private string Message(string as_msg, bool verbose)
            {
                as_msg = DateTime.Now.ToString() + ":" + as_msg;

                if (verbose)
                    System.Console.WriteLine(as_msg);

                return as_msg;
            }


            static private void Event(string as_msg, EventLogEntryType eventType, bool verbose)
            {
                EventLog lo_eventLog = new EventLog();
                lo_eventLog.Source = "WorkspaceUpdate Sync Job";

                as_msg = Message(as_msg, verbose);
               
                lo_eventLog.WriteEntry(as_msg, eventType, 3652);

            }
        }
    }

  • Project Programmability and Business Intelligence

    Office Project Server 2007 Developer Training

    • 2 Comments

    Microsoft  Office Enterprise Project Management University (EPMU) is offering a range of new courses in Project Server and Project Portfolio Sever 2007 including Developer Training.

    Office Project Server 2007 Developer Training

    • Designed for Developers extending, developing and integrating Office Project Server 2007
    • 3 Day Course
    • USD$550

    For full details of all courses visit www.msepmu.com

  • Project Programmability and Business Intelligence

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

    • 2 Comments

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

    Overview  

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

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

  • Project Programmability and Business Intelligence

    Scalable Issues & Risks Report

    • 2 Comments

    Introduction

    Out of the box the Issues & Risks components of Project Server 2007’s collaboration functionality have an architectural problem which surfaces when 100’s of Project Server Workspace sites are created. This problem is forced by the need to allow flexibility in PWS placement within the farm and the ability to assign issues and risks to any user within the farm, not just specific project team members.

    This results in a non-scalable multi-site query whenever a team member hits the home page of the Issues and Risks page in Project Web Access. The home page issue is mitigated by removal of the functionality; however the team member still needs a place to see all issues and risks across the Project Server site. These instructions detail how to implement such a place in a scalable manner using the data held in the Project Server Reporting Database in combination with SQL Server Reporting Services.

    Caveat: this solution is provided as a workaround; it is not intended to become part of the Project Server 2007 solution and as such will not be localized or migrated/upgraded in future releases. The source of this solution is provided to the customer as-is and should be tested within the customer’s pre-production environment prior to production deployment. Microsoft Product Support Services will not be able to offer full support for this software.

    Architectural Notes

    This solution uses the flexibility of the Microsoft Office Project Server 2007 architecture to leverage the following technologies:

    - Project Server Reporting Database
    This database contains Issue, Risk, Project & Resource data used by this solution. Note that this data is updated on project publish (or explicit WSS site synchronization) so after new Issues & Risks are created the project should be published or the site synchronized in order to make this data available in the reporting database.
    Team members should subscribe to the Issues & Risks lists for their projects in order to get earlier notification of new data.

    - SQL Server Reporting Services 2005 SP2
    The reports used to provide this solution were built using the Report Designer (available in the “Business Intelligence Workbench”) that ships with SQL Server 2005. The SP2 version of the software was used. Note that the installation section refers to the non-integrated Report Viewer web parts, used to demonstrate the integration. It is recommended that the integrated mode be used for a cleaner interface.
    The solution uses the Report Viewer web part that must be installed into the farm as described in the installation section.
    The solution is built as a master report that hosts two sub-reports, one for issues and one for risks – this makes individual reformatting and editing a little bit easier.

    - SharePoint Web Part Pages
    The report developed to display the list of issues & risks is hosted within the Reporting Services report viewer web part within a web part page that is added to the Project Web Access site. The report assumes that flat lists are being used for Issues and Risks, if folders within the list are to be used then further testing is recommended.

    Security Notes

    The report should be run using a data connection with credentials that have read-only access to the Reporting database, specifically the following tables:

    - MSP_EpmProject: Used to obtain project name and URL details

    - MSP_EpmResource: Used to filter data based on the caller (see more below)

    - MSP_WssRisk: Data on Risks, updated when the project is published or the site is explicitly refreshed, not when an issue or risk is added to the list.

    - MSP_WssIssue: Data on Issues, updated when the project is published or the site is explicitly refreshed, not when an issue or risk is added to the list.

    A design aim for the report is to avoid having to register every team member in the reporting database. This is met via SQL Server Reporting Services parameterized filtering on the caller’s userid (this is passed into the query so that only data directly assigned to the caller is returned) – This report was not tested in a farm configuration, it is recommended that it be fully tested in a multi-server configuration before live deployment to ensure that data is filtered.

    This report does not respect SharePoint list-item security (which is not enabled by default) – if an issue or risk is assigned to a team member and then secured individually in a manner that denies the team member access they will continue to see the data pertaining to the item in this report, however the link will not display the item form.

    Scalability and Performance Notes

    This solution will be considerably more performant than the cross-site query that it replaces. However it is recommended that an additional index be created in the reporting database to facilitate the filtering, specifically:

    MSP_EpmResource:

    CREATE NONCLUSTERED INDEX [IX_FastAccountLookup] ON [dbo].[MSP_EpmResource] 
    ( 
    [ResourceName] ASC 
    ) 
    INCLUDE ( [ResourceNTAccount]) 
    Go
    

    Installation Notes

    Assumptions:

    SQL Server 2005 Reporting Services 2005 is already installed in the farm, in non-integrated mode as specific website (in the example below: http://pconlan08:81/ReportServer) – it is recommended that integrated mode be used.

    The SQL Server 2005 Reporting Services web parts are installed on every SharePoint server in the farm, installation instructions:

    http://msdn2.microsoft.com/en-au/library/ms159772.aspx (note that the CAB file is located in a different location on an X64 server, search for RSWebParts.cab on the Reporting Services server(s))

    Installation Steps:

    1. Customize and Deploy the Reports

    Load the attached Reporting Services solution into Business Intelligence Workbench. Edit the Shared Data Source to point to the correct SQL Server and Reporting Database; then set the appropriate credentials (Reporting Services implements a secure credential store)
    Review the form design and make any customer-requested changes (see Report Configuration Notes below)
    In Business Intelligence Workbench edit the Project Properties to point to the correct Reporting Services server and Deploy the solution. Use the http://xxxxx:nn/Reports url to validate that the report works as intended before proceeding to the next step.
    Once tested, use the SQL Server Management Studio to connect to the Report Server and grant appropriate access to the reports to all team members. Test this with a subset of team members to ensure they can access the report.

    2. Create a web part page to host the web part for the report

    - Navigate to the PWA site as a SharePoint administrator, and use the Site Options drop down to select Site Settings/Modify All Site Settings which will bring up the Site Settings page.
    - Select Site Libraries and Lists which will bring up the Site Libraries and Lists page.
    - Select Create New Content which will bring up the Create page.
    - From the menu select: Web Part Page

    - Fill in the dialog as below:

    Note that you may want to place this page in a new document library rather than the Documents library as depicted as you will be granting read access to team members to this library.
    Note that once created the page can be “hidden” in the library to prevent accidental erasure (to do this edit its properties in the library view).
    This creates the page, click the “Add a Web Part” orange bar to add the web part. Use the “Advanced Web Part gallery and options” link, select the “Server Gallery” and then the “Report Viewer” web part.

    This will add the web part to the page. Now configure it as below:
    Menu4.JPG
    Note that the Toolbar and chrome are turned off and a fixed height of 12” is used. No width need be set. The Report Path can contain spaces (no need to %20 escape the string)
    Save the changes and the web page, navigate to the URL to ensure that the report web part displays as expected and that it contains the report as expected. (Note that if you edit and redeploy the report you will need to restart your IE session to avoid caching the report format)

    3. Connect the page to the Project Web Access menu

    In PWA Server Settings choose Quick Launch from the Look and Feel set of options.
    Add a new Entry to the Work Item Group: “Issues and Risks Report”, connect it to the page you created above, as depicted below:

    The final step in this task is to hide the un-scalable Issues & Risk option. In the Quick Launch dialog click the Issues & Risks link and set its hidden property.

    The final result should look like:

    You have now deployed the report, and added it to the PWA menu for team members to use.

    Report Implementation Notes

    Several SQL Server 2005 Reporting Services features have been used to deliver the reports, these are briefly discussed below.

    Team members will click on the new Issues and Risks Report link, this will take them to the page below:

    1. Issues and risks are grouped by status (to put Active at the top of each) – a filter could be added to remove items that have been postponed and/or closed if required, this is a trivial change to the reports. Note that if the types of status have been customized this may look slightly different.

    2. Within the grouping data is sorted by Due Date and Project Name. If Due data is not set then “Now” is assumed (ie the current date will be used)

    3. The Title is set up to be clickable to take the team member to the detailed item form, where the item can then be viewed and/or edited.

    4. Dates have had their time element stripped to save space on each row.

    5. The report displayed above is actually three reports – the master (that contains the Microsoft Office logo and sub-report objects) and two sub reports.

    6. Data is filtered on items assigned to the caller using Reporting Services filtering capabilities – the report has a hidden parameter (a standard Reporting Services feature) that is populated with the callers Windows account, care should be taken to test this in your environment.

    7. Further customization could add graphs/additional data to the view.

    Patrick Conlan

  • Project Programmability and Business Intelligence

    EPM 2007 Test Data Population Tool

    • 2 Comments

    Christophe just passed along this information:

    The EPM 2007 Test Data Population Tool solution starter was published on CodePlex today: http://www.codeplex.com/EPMDatapop

    MWSnap048 2007-11-26, 19_26_50.jpg

    The EPM 2007 Test Data Population Tool enables you to load large amounts of EPM data: resources, projects, tasks, assignments into a Project Server 2007 database. You can then use this data to test loads and help your organization plan for your Project Server 2007 deployment. This tool is similar to the EPM 2003 Data Population utility but contains more features plus since the sources are provided you can customize it to fit your specific needs.  It was written by EPM World Wide Center of Excellence (WW COE) to generate data needed to perform performance and scalability labs for large and complex EPM deployments.

    The EPM 2007 Test Data Population Tool is a Win Form application that leverages the Project Server Interface (PSI) and/or WinProj (Project Professional).

    This solution starter includes a 15 pages document, a setup.exe to deploy it, as well as all source code.

    A special thank you to Mike Shughrue and Michael Jordan from the EPM WW COE for creating and testing the tool extensively during scalability labs, as well as Boris Scholl from the Product Marketing Group for reviewing the code and documentation. 


    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 EPMDatapop discussion forum on CodePlex.

    Regards,

    Christophe Fiessinger

Page 2 of 11 (255 items) 12345»