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

    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

    VBA Event Handler Example

    • 4 Comments

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

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

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

       

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

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

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

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

    6. Open the ThisProject Object:

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

      Sub Initialize_App()

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

      End Sub

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

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

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

      Call Initialize_App

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

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

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

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

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

     

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

    Chris Boyd

  • Project Programmability and Business Intelligence

    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

    Preliminary version of the Scrum Solution Starter for Project 2010 is available for download!

    • 5 Comments

    We have published preliminary version of the Scrum Solution Starter for Project 2010 on MSDN Code gallery http://code.msdn.microsoft.com/P2010Scrum please participate actively in the Discussion to notify us about any issue you may encounter or post any feedback!

    This solution starter focuses on the Project 2010 desktop client, and on the individual Scrum team experience.

    Scrum is an iterative, incremental methodology for project management often seen in agile software development. Although Scrum was intended for management of software development projects, it can be used to run software maintenance teams, or as a general project/program management approach.

    There are 3 main items in Scrum:

    • Product backlog: A product backlog is dynamic—Items may be deleted or added at any time during the project. It is prioritized—Items with the highest priority are completed first. It is progressively refined—Lower priority items are intentionally course-grained.
    • Sprint backlog: A sprint backlog is a negotiated set of items from the product backlog that a team commits to complete during the time box of a sprint. Items in the sprint backlog are broken into detailed tasks for the team members to complete. The team works collaboratively to complete the items in the sprint backlog, meeting each day (during a daily scrum) to share struggles and progress and update the sprint backlog and burn down chart accordingly.
    • Burn down: The sprint burn down chart is a publicly displayed chart showing remaining work in the sprint backlog. Updated every day, it gives a simple view of the sprint progress. It also provides quick visualizations for reference

    Supported Scenarios:

    A Scrum Master wants to use Project for the basics of running a sprint, including:

    • Collecting and tracking status
    • Managing the product backlog
    • Managing the sprint backlog (and initial iteration planning)
    • Viewing a burn down chart
    • Easily exporting Scrum data to email/other apps

    Enjoy!

  • Project Programmability and Business Intelligence

    Using formulas in custom fields

    • 5 Comments
     

    Using formulas within custom fields has been a part of Microsoft Project for a long time, but typically they are used by the more advanced project managers and by developers of Project applications and add-ons.

     

    That doesn't mean that you shouldn't learn how to use them. After all, of all the ways you can use to customize and automate Project, formulas within custom fields remain one of the simpler ones.

     

    The first question that arises to those new to Project programming is, Why use a formula within a custom field in the first place?

     

    The primary reason is that too often Project's default fields aren't displaying information precisely as you want. The second reason is that using formulas within custom fields is a much quicker and easier way to customize and automate Project than using VBA, VSTO, PowerShell, or C#.

     

    Some Examples

    • For reporting purposes, if you'd rather have a text field and not a number field  to display on the Gantt chart how much work resources are assigned to, you could create a custom text field, and then attach this formula to it:

     

        [work]/60/8 & "hours"

     

    1. To add the above formula, click Customize on the Tools menu, and then click Fields.
    2. Select a text field to customize, such as "Text1", and click Rename to give it a meaningful name, like "Work Time".
    3. Once you've renamed the field, click Formula.
    4. Copy and paste the above formula into the Edit Formula box.
    5. You're not done yet. After the formula has been added, go back to the Gantt chart and add the "Work Time" field.

     

     

    • Here's another example,  When added to a custom number field, the following formula returns a numerical value that indicates the number of days between the current date and the finish date of the tasks in your schedule:

     

       DateDiff("d",NOW(),[Finish])

     

    This example uses another method to customize Project fields by using functions. (don't confuse formulas with functions). Learn more about Project funcitons here.

     

     

    • A more complicated example: When added to a custom text field, the following formula (with functions included) returns a value of "No baseline," "Overbudget by 20% or more," or "Under budget":

    Switch(Len(CStr([Baseline Finish]))<3, "No baseline", ([Cost]+1)/
    ([Baseline Cost]+1)>1.2,"Overbudget by 20% or more", ([Cost]+1)/([Baseline Cost]+1)>1,
    "Overbudget",True,"Under budget")

     

    An Example using VBA

     

    Here is VBA code that does the same as the first example above. You might do this if you have numerous reports that need columns set in a specific way. The code could be added as a toolbar button. Adding custom fields using formulas within VBA is a four-step process. Here is an example that adds a "Work Value" column to the Gantt chart, with work values that are calculated by a formula.

     

    1. First, set up the custom formula that the custom field should use. In this example, the constant, pjCustomTaskText1, is being used to specify that a Text1 field is being customized to contain a formula.

     

       CustomFieldSetFormula FieldID:=pjCustomTaskText1, _

        Formula:="[Work]& "" hours"""

     

    1. Now calculate the formula.

     

       CustomFieldProperties FieldID:=pjCustomTaskText1, _

        Attribute:=pjFieldAttributeFormula, _

        SummaryCalc:=pjCalcFormula

     

    1. Now add the field to a view.  "NewFieldName" indicates that Text1, will be used, which is equivalent to the pjCustomTaskText1 used in the CustomFieldProperties method above.

     

    TableEdit name:="Entry", TaskTable:=True, NewName:="", fieldName:="", _ 

    NewFieldName:="Text1", Title:="Work Table", ColumnPosition:=1

     

       

    1. Now apply the table to a view. I know, it sounds odd to apply the table when all you want to do is add a field, but that's the way it is with VBA code.

     

       TableApply name:="Entry"

     

    There you have it. Not so difficult. And you saved the weekend.

     

  • Project Programmability and Business Intelligence

    Creating Project Workflows using Visual Studio 2012

    • 6 Comments

    In Project Server 2010, Project developers were able to create Project Workflows using Visual Studio 2010. In Project Server 2013, we enabled creating the workflows with SharePoint Designer, which makes it much easier and faster to create Project Workflows. In that blog post, we showed how we have simplified the workflow creation for Project using SharePoint Designer 2013.

    We are, however, still supporting creating Project Workflows with Visual Studio for the more complex set of workflows, and in fact, have also made it easier to create Project Workflows with Visual Studio 2012. Below, we are going to use a sample two-stage workflow to show how you can create workflows with Visual Studio 2012:

    1. Creating the Workflow solution: File Menu>New>Project>Office/SharePoint>SharePoint solutions > SharePoint 2013 Project. Give this project a name, and hit OK:
    clip_image002

    In the customization wizard, enter the address of the PWA web you’d like this workflow to be published to. Then, pick the sandboxed solution option to limit this workflow to this particular PWA web:
    clip_image004

    2. At this point, the project you’ll see the empty canvas. In the Project Menu, click on the Add New Item, and from the Office/SharePoint tab, select Workflow, enter a name, and hit Add:
    clip_image006

    Then, in the customization wizard, pick Site workflow:
    clip_image008

    Then, pick the history list and the workflow tasks list from that site. We recommend that you use the default lists since a number of PWA UI entry points, use these default lists. Then, hit Finish:
    clip_image009

    3. Now, we need to set up the environment to use the Project Server activities. In the toolbox, right click and click on “add tab”, and call the new tab “project server”:
    clip_image011

    Then, right click on the “project server” tab and click on “choose items” from the menu, and you’ll see this dialog:
    clip_image013

    In the dialog click on Browse, and navigate to where the workflow dlls are located. They are usually located in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\WorkflowActivities
    You’ll see two activities dll there. Open the project server one (Microsoft.Office.Project.Server.WorkflowActivities.dll), and hit OK. You are now taken back to the “toolbox items” dialog, and highlights the selected corresponding activities. Hit OK to continue.
    clip_image015

    4. You might see a “sequence” in the canvas. Delete that, and from the toolbox, pick Flowchart and add it by dragging it into the main area. This flowchart will be the main container of all the stages of the workflow:
    image

    In the toolbox, click on Control flow, and add the sequence inside that flowchart. Throughout this sample workflow, we will use sequence to represent workflow stages in Visual Studio. This is similar to how SharePoint Designer handles each stage, i.e. each stage is equivalent to a separate sequence in Visual Studio:
    clip_image019

    Rename the sequence to “Create_Stage” by clicking on the “Sequence” and start typing to change the name. Drag the line from start to “Create_Stage” to connect them together:
    clip_image021

     

    5. Double click on the “Create_Stage” to drill into this sequence

    a. Under project server in toolbox, add the “EnterProjectStage” and “ExitProjectStageGate” activities to the sequence. These two activities are required in any of the PWA stages in Visual Studio.

    b. In the properties of “EnterProjectStage”, change the StageID to the Stage ID of the particular stage you’d want this sequence to represent. You can find the stage ID in the URL of that stage, and is available if you navigate to that stage in PWA Settings > Workflow Stages, and then click on the particular stage. Since stageID is a string, the ID should be provided in quotation marks.

    c. Put another sequence between “EnterProjectStage” and “ExitProjectStageGate”. Essentially, everything in this sequence is what is represented in the text-based designer in SharePoint Designer stage definition.

    d. From project server item in the toolbox, drop the “waitForProjEvent” activity in that sequence:
    clip_image022

    e. Change the EventName property to “OnProjectSubmit”. The other supported Event Names are “OnProjectCommit” and “OnProjectCheckIn”

    6. In the breadcrumb, click on Flowchart to go one level up. Add another sequence after Create_Stage and call it Finished_Stage, and connect the wire from Create_Stage to the Finished_Stage:
    clip_image023

    7. Similar to the Create_Stage, add the EnterProectStage and ExitProjectStageGate activities to the sequence as well as the WaitForProjectEvent activity in the middle, and set the properties accordingly:
    clip_image024

     

     

    8. This completes building the workflow in Visual Studio. However, in order to make sure that the workflow can be properly published to the PWA, we need to make a few more changes in the xaml files of the project: From solution explorer, pick “Elements.xaml” under the workflow node

    a. Replace the WSEventSourceGUID with the following so that the workflow is correctly identifies as a project workflow:
    <Property Name="WSEventSourceGUID" Value="5122D555-E672-4E5D-A7C4-8084E694A257" />

    b. Inject the following properties under the “Url = WorkflowStartAssociation”:
          <Property Name="Microsoft.ProjectServer.ActivationProperties.ProjectId" Value="" />
          <Property Name="Microsoft.ProjectServer.ActivationProperties.CurrentStageId" Value="" />
          <Property Name="Microsoft.ProjectServer.ActivationProperties.RequestedStageId" Value="" />
          <Property Name="WSEventContextKeys" Value="Microsoft.ProjectServer.ActivationProperties.CurrentStageId;#Instance&#xA;Microsoft.ProjectServer.ActivationProperties.ProjectId;#Instance&#xA;Microsoft.ProjectServer.ActivationProperties.RequestedStageId;#Instance&#xA;" />

    9. Now that everything is set, and the workflow is ready for publishing, click on the “Build Solution” under the Build menu, and then click on the “Deploy Solution” under the Build menu. The wsp file is now deployed to the site. You can also find a copy of the wsp file in the file system, under [project name]>bin>debug

    Now, the workflow will show up in PWA. If you navigate to PWA Settings > Enterprise Project Types, and create a new Enterprise Project Type, you will see this workflow as one of the options in the workflow dropdown list.

    For more information, see Getting started developing Project Server 2013 workflows in the Project 2013 SDK.

  • Project Programmability and Business Intelligence

    Reporting Database Diagrams

    • 8 Comments

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

     

    The pages are:

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

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

    ·         Timesheet Tables

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

     

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

     

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

                                                                                    

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

     

    --Jim Corbin

  • Project Programmability and Business Intelligence

    Adding the Project Programmability RSS Feed to Live

    • 1 Comments

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

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

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

    3. Click on "Advanced options":

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

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

       
  • Project Programmability and Business Intelligence

    Syncing Project Workspaces with the RDB

    • 2 Comments

    It has been a while since I have done a blog post, so I figured I would share with you something that I am currently working on. We have run into the scenario where users are updating information on Project Workspaces and Project Manager wants to report on the data that the users are entering in the Workspaces. That information does not make it into the RDB until after a project publishes. To keep the RDB in sync with the Project Workspace, I wrote the following app that we run as a scheduled job:

    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 WorkspaceRDBUpdate
    {
      class Program
     
    {
        static void Main(string[] args)
        {
          int count = 0;
         
    bool verbose = false;
         
    string ls_projURL = "";
         
    const string PROJECT_SERVICE_PATH = "_vti_bin/psi/Project.asmx";

          if (args.Length == 0 || args.Length > 2)
          {
            System.
    Console.WriteLine("WorkspaceRDBUpdate url [verbose]");
            System.
    Console.WriteLine(" url – The URL to the project server.");
            System.
    Console.WriteLine(" verbose – An optional parameter that outputs progress.");
          }
         
    else
         
    {
            ls_projURL = args[0];
            
           
    if (args.Length > 1 && args[1].ToLower() == "verbose")
            {
              verbose =
    true;
            }

            WSProject.Project ws_Project = new WSProject.Project();

            if (!ls_projURL.EndsWith("/"))
            {
              ls_projURL +=
    "/";
            }

            ws_Project.Url = ls_projURL + PROJECT_SERVICE_PATH;
            ws_Project.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, WorkspaceRDBUpdate.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() != "")
                  {
                   
    if (verbose)
                      System.
    Console.WriteLine("Synchronizing Workspace for Project " + ls_projName);

                    ws_Project.QueueSynchronizeProjectWorkspace(
    Guid.NewGuid(), lo_projGUID, false);
                    count++;
                  }
                 
    else
                 
    {
                    if (verbose)
                      System.
    Console.WriteLine("Notice: Project " + ls_projName + " does not have a workspace.");
                  }
                }
               
    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")
                    {
                     
    if (verbose)
                        System.
    Console.WriteLine("Notice: Project " + ls_projName + " is not published.");
                    }
                  }
                }
              }

              Event("Successfully Synchronized " + count + " Projects with the RDB", EventLogEntryType.Information);

            }
            
    catch (WebException lo_ex)
            {
             
    if (verbose)
                System.
    Console.WriteLine("Error: " + lo_ex.Message);

              Event("Error: " + lo_ex.Message, EventLogEntryType.Error);
            }
            
    catch (Exception lo_ex)
            {
              
    if (verbose)
                System.
    Console.WriteLine("Unknown Error: " + lo_ex.Message);

              Event("Unknown Error: " + lo_ex.Message, EventLogEntryType.Error);
            }
          }
        }

        static private void Event(string as_msg, EventLogEntryType eventType)
        {
         
    EventLog lo_eventLog = new EventLog();    
         
    lo_eventLog.Source =
    "Workspace RDB Sync Job";
          lo_eventLog.WriteEntry(as_msg, eventType, 3652);
        }
      }
    }

    This application is fairly straightforward. It reads all the projects and checks if a workspace exists. If one exists, it forces the RDB to be updated by calling QueueSynchronizeProjectWorkspace(…). It logges events to the event log, which makes troubleshooting easy if administrator decide to run it as a scheduled job.

    Chris Boyd

  • Project Programmability and Business Intelligence

    VSTO Add-In Support for Project 2003 and Project 2007

    • 9 Comments

    I am very excited about the Visual Studio Beta 2 release, as it now has support for Project 2003 and Project 2007 add-ins! This makes coding and debugging managed code very easy in Project Client. It also makes it really simple to call into the PSI from Project Client to retrieve and update data on the server because you have the complete .Net framework at your disposal.

    You can download Visual Studio Beta 2 to try it out for yourself:

    http://msdn2.microsoft.com/en-us/vstudio/default.aspx

    I plan to do a few complex blog posts with regards to VSTO over the next couple of months, but to get started, here is a basic example:

    1. Download and install VS Beta 2 from: http://msdn2.microsoft.com/en-us/vstudio/default.aspx

    2. Open VS Beta 2 and Create a New Project selecting Project Add-in:

    image

    3. Give it a name and click OK

    4. Add a Windows Form called SimpleDemoForm to the project:

    image

    5. A label to the form called: lblProjectName

    6. In the ThisAddIn.cs class file add the following code in the startup method:

                SimpleDemoForm frmSimpleDemoForm = new SimpleDemoForm(Application);
                frmSimpleDemoForm.Show();

    This code will load and show the form we created in step 4. Note that we pass the Application into the form's constructor.

    7. View the code for the form and have it match the following:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using MSProject = Microsoft.Office.Interop.MSProject;
    
    namespace SimpleDemo
    {
        public partial class SimpleDemoForm : Form
        {
            private MSProject.Application Application;
    
            public SimpleDemoForm(MSProject.Application Application)
            {
                InitializeComponent();
                this.Application = Application;
            }
    
            private void SimpleDemoForm_Load(object sender, EventArgs e)
            {
                lblProjectName.Text = Application.ActiveProject.Name;
            }
        }
    }

    Couple of things to note with this code sample. We have referenced Microsoft.Office.Interop.MSProject and we have passed the Application variable to the form so that we can manipulate the project in the same way we can manipulate it in VBA. In the form load method, we read the project name and show it on the form.

    8. Build and run the application.

    This should launch Project automatically and load the add-in for you, with no configuration work involved. You can also add any debug breaks to troubleshoot your code.

    Here is what you should see when you build and run the add-in:

    image

    Chris Boyd

  • Project Programmability and Business Intelligence

    ProjTool for Project Server 2010

    • 4 Comments

    Note: For the updated article, see Using the ProjTool Test Application in Project Server 2010 in the Project 2010 SDK. The attached ProjTool_ProjectServer2010.zip file includes the ProjTool application and the complete Visual Studio 2010 solution.

    The ProjTool test application was introduced in the Microsoft Office Project 2007 SDK. Many developers found it useful for creating, examining, and changing data by using the PSI on a test installation of Project Server, and checking actions by impersonating users on a test server. The 2007 release of ProjTool does not work correctly with Project Server 2010.

    ProjTool was originally developed by many different testers on the Project team. In the SDK release, the internal test code has been removed. The download that is attached to this post includes an update of ProjTool for Project Server 2010, along with a draft document on how to use ProjTool. The article and source code will be included in a future update of the Project 2010 SDK.

    The updated ProjTool uses the .NET Framework 3.5; all calls to PSI methods use WCF instead of the ASMX interface. ProjTool now works for Windows authentication, Forms authentication,  multi-authentication (both Windows and Forms on the same Project Web App port address), and impersonation on Project Server 2010.

    Here is an example screenshot from ProjTool, which shows part of the AssignmentDataTable in the ProjectDataSet for one project, after the user has changed the value of the ASSN_PCT_WORK_COMPLETE field in an assignment. When Update Project is clicked, ProjTool shows fields in all of the datatables that have changed as a result.

    --Jim

  • Project Programmability and Business Intelligence

    An Impersonation Web Application

    • 5 Comments

    Jim Corbin has passed along this great post:

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

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

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

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

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

     

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

    To install the Impersonation Web application:

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

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

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

    http://ServerName:5636

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

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

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

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

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

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

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

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

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

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

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

                AuthFlags="0"

                NTAuthenticationProviders="NTLM"

                ServerAutoStart="TRUE"

                ServerBindings=":5636:"

                ServerComment="Impersonation"

          >

    </IIsWebServer>

    8.     Restart IIS.

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

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

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

    ·         Microsoft.SharePoint.Search.dll

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

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

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

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

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

    CodeBehind="Global.asax.cs"

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

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

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

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

  • Project Programmability and Business Intelligence

    Project Server 2007 Report Pack II - "The Top Reports"

    • 0 Comments

    We are proud to announce that the  Project Server 2007 Report Pack II - "The Top Reports" a collection of best practices reports for SQL Server Reporting Services is available on MSDN Code Gallery.

    Project Server 2007 Report Pack II - "The Top Reports"

    This Project Server 2007 Report Pack II - "The Top Reports" addresses questions "what" reports to run in your Project Server 2007 environment and "why". It comprises of 40 reports divided into Management and Governance sections. The pack was designed by top Microsoft Consultants who deploy and enhance the Enterprise Project Management Solution (EPM) for individual customers and thus contains very valuable know-how that they have collected over the years.

    More information

    · Download: http://code.msdn.microsoft.com/EPMReportPack

    · Recorded webcast: https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032423031


    Other available solutions for Enterprise Project Management (EPM) solution

    MSDN Code Gallery

    http://code.msdn.microsoft.com/Project/ProjectDirectory.aspx?ProjectSearchText=epm

    MSDN Code Gallery solutions

     

    Project Server 2007 Sample Databases

    Project Server 2007 sample databases. The zip contains the following five databases: * Archive * Draft * Published * Reporting * Content

    EPM 2007 Project Updater InfoPath Form

    A web-enabled InfoPath 2007 Form using Project Server 2007 PSI .Net web services. The form allows project managers to easily mark tasks as complete in a sequential, process-like project plan…

    Solution Connector for Microsoft Office Project Portfolio Server 2007

    The Solution Connector is an ASP.NET web service and .NET class library which provides a set of methods for programmatically creating, updating or deleting projects and their attributes in …

    Building an AJAX Web Part for Microsoft Office Project Server 2007

    This sample contains a Project Server 2007 PSI Extension that retrieves geo data based on a custom field, and then the main feature is an AJAX Web Part that uses Virtual Earth to display the …

    Microsoft Office Project Server 2007 PSI Extension Generator

    PSI Extensions are custom web services that execute within the Project Server Interface infrastructure, sharing the same security context as other PSI web services. PSI Extensions can be very …

    Project Server 2007 Migration Rename Tool

    During a Project Server 2003 to Project Server 2007 migration, projects have "_Published" appended at the end of their name. This tool will enable you to "bulk" rename all projects and removed …

    Project Server 2007 Lookup Table Update Control

    Out of the box you cannot enforce control updates of specific lookup tables in Project Server 2007. This code sample leverages the standard Project Server interface API to limit who and what…

    Microsoft Project Fx (mPfx) for Microsoft Project 2007

    Microsoft® Office Project 2007 (Microsoft Project) underscores Microsoft’s continued commitment to developers concerned with creating robust planning and scheduling tools for organizations with…

    Project Server Interface 101 Development Samples

    10 samples of how to do 101-level PSI development tasks

    Project Server 2007 Performance Lab Kit

    The Project Server 2007 Performance Lab Kit contains two Visual Studio solutions/tools to help you test your farm environment for capacity planning purposes: the “EPMTestTools” and the “EPM Stress …

    Project Server 2007 Report Pack II - "The Top Reports"

    The Project Server 2007 Report Pack II - published in August 2009 - provides reports that can be run in your EPM environment and enable easier access to EPM information that organization can …

    CodePlex

    http://www.codeplex.com/site/search?projectSearchText=epm

    CodePlex EPM solutions

     

    Project Server 2007 Timesheet & Statusing Customization Samples

    Code samples that demonstrate how EPM (Project Server) 2007 Timesheet and Statusing functionalities could be customized using custom event handlers and Project Server Interface calls. These samples leverage the Timesheeting and Statusing API documented in the EPM 2007 SDK

    Project Server 2007 VSTS Connector

    Connector solution for Project Server 2007 and Team Foundation Server

    Project Server 2007 Timesheet Tied-Mode Service and Event Handler

    This project is used with Microsoft Project Server 2007. It consists of an NT service, an event handler, a test app and installer. The event handler is fired when a tiemsheet is saved. The handler saves the timesheet info in a SQL table. The NT service (TSAutoStatus) polls...

    EPMSync Utility

    Microsoft Office Project Server 2007 (Project Server) and Microsoft Office Project Portfolio Server 2007 (Portfolio Server) integrate via the Portfolio Server gateway. Data is exchanged by running either an import or an export from Portfolio Server. But this exchange is done...

    Project Server 2007 Test Data Population Tool

    The EPM (Project Server) 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 2

    Project Server 2007 Queue Watch Tool

    The Project Server 2007 Queue Watch Tool will help you monitor all queue activities for a specific Project Web Access (PWA) instance. This tool leverages the standard Project Server Interface publically documented web services to query and retrieve jobs in the Project Server ...

    Project Server 2007 Log File Report Tool

    The Project Server 2007 Log File Report Tool enables the import and the reporting of log file generated by your EPM & SharePoint farm. The Log File Report Tool will import log files (in an SQL database) from all servers in your farm with the ability to filter them by date. O...

    Project Server and InfoPath 2007

    This solution starter demonstrates how to leverage InfoPath and Forms Services as well Windows Workflow Foundation (WWF) hosted by SharePoint to create your own project initiation phase. The project initiation phase is different for every customers and organizations. It typic...

    EPM Custom Fields Copy

    The principal function of this project is to provide Project Server 2007 custom fields and lookuptables migration from development environment to production environment, using some PSI methods. I want to increase my inicial project, adding more funcionallities and solve some ...

    Project Server 2007 Timesheet data population tool

    The Project Server 2007 Timesheet Data Population Tool enables you to simulate timesheet entries in your farm. This tool can help you perform scalability studies of your PS architecture and validate the sizing of an existing architecture (by measuring timesheet queue throughpu...

    Search Project Server data using SharePoint Server's BDC and Enterprise Search

    Search Project Server data using SharePoint Server's BDC and Enterprise Search

    Project Reportcard

    The Project Reportcard was developed as a tool to help Project Managers assess their projects based on organizational standards implemented by their PMO. Understanding what the organizational KPIs are going to look like before their project update is published, can help a pro...

    Project Server 2007 Auditing Solution Starter

    EPM Auditing makes it easier to auditing and debug Projec Server 2007 activities. You can audit multiple activities and output them to multiple sources. It's developed in C#.

    Project Server Workspace Sync

    This tool works with Project Server. It is a very simple tool that iterates over the list of Project Workspaces and triggers user sync for the members in the WSS site and triggers the sync for issue, risks and deliverables with the reporting database.

    Earned Value Add-In

    The Earned Value Add-In has been developed as a tool to help project managers assess and visualize projects using earned value analysis.

    Project Server 2007 AD/Resource Sync Utility

    Demonstrates how to sync additional AD fields to resource custom fields for Project Server 2007.

    Persisting SSAS OLAP Roles In Project Server 2007

    During the standard Cube Building process in Project Server 2007, any manually added OLAP Roles in an OLAP database are deleted. The Cube building creates a default Role ProjectServerViewOlapDataRole that automatically adds all the Project Server users to this Role. This customiz

    Project Server 2007 Bulk Edit

    The purpose of this project is to allow the bulk edit of resource data. With large numbers of resources Resource Center can take a long time to load, primarily due to the security checks required. This tool goes directly against the Project Server Reporting store to pull back t

    Project Server 2007 Event Handler Admin Tool

    This project contains code demonstrating how to use the PSI to add and remove event handler associations for Project Server 2007. Instead of displaying all possible event handlers like the PWA admin screens, this tool uses reflection to show only the implemented handlers and all

    Project 2007 Test Framework

    Controller/client test framework can be used to test any assembly or .exe with and object model (any MS Office app).

    Project Server 2007 Timesheet AutoStatus Plus

    Project Server 2007 Timesheet AutoStatus Plus is a major rewrite of Christophe Fiessinger's EPM tied-mode work. Planned enhancements are: multiple instance support, farm support, and elimination of the Windows Service in favor of a SharePoint Timer Job.

  • Project Programmability and Business Intelligence

    Where in the SDK are the Visio diagrams for the RDB?

    • 0 Comments

    The Project 2007 SDK download includes E-R diagrams of the major tables and views in the Project Server Reporting database. The Project 2010 SDK download does not include the Visio diagrams, although it does have the new RDB_Field_Selector.xls tool, along with the Project2010_ReportingDB.chm schema reference. You can easily create database diagrams by using either Visio Professional or Microsoft SQL Server Management Studio.

    For example, with a Visio diagram, you can see the relationships between the timesheet tables:

    TimesheetTables_84

    If you have Visio Professional 2010 and access to a Project Server 2010 Reporting database, you can reverse engineer the RDB to new database diagrams. Use the Visio diagrams in the Project 2007 SDK download as an example.

    With the Reverse Engineer Wizard in Visio, select the Microsoft SQL Server driver, and then create a new data source, for example:

    VisioReverseEngineerWizard

    … and then follow through the wizard to select the tables and views you want for each diagram. In the Project 2007 SDK, there are four E-R diagrams:

    • EPM User Views:  this is a very crowded page, suitable mainly for printing on a large sheet of paper.
    • EPM Relationships:  shows the main relationships between the views in the previous page.
    • Timesheet Tables
    • SharePoint Data:  shows the links from tasks and projects to the Issues and Risks views.

    To create diagrams using SQL Server Management Studio, expand a Project Server Reporting database, right-click the Database Diagrams folder, and then click New Database Diagram. Select the tables you want, and SQL Server arranges them in a useful manner so that relationship lines don’t cross. Following are the timesheet tables in the Project Server 2010 Reporting database:

    SQLDiagram_Timesheets_84

    Hover the mouse pointer over a relationship to show the relationship details.Database diagrams can help you derive T-SQL queries to build reports.

    Note:  When you create enterprise custom fields with lookup tables, Project Server creates additional views in the RDB, and can also create more column pool tables as required, for that instance of Project Web App.

    For more information, see  Custom Fields and the Reporting Database in the Project 2010 SDK.

  • Project Programmability and Business Intelligence

    Getting the Project Guide to show up in Project 2010

    • 8 Comments

    Hello,

    With the Project 2010 release, we have deprecated the Project Guide content. For the end user, this means there is no way through the UI to show the Project Guide. However, there is away to display the Project Guide through the OM. So if you have your own custom guide, you can still use it with a bit of work. This post will show you how to do this in three easy steps.

    The first step is to write a method that turns the guide on and off. To do this, we need to have a guide, so I am going to use the one that we shipped in Project 2007.  However, the default Project Guide files need some changes:

    The folder structure should be flattened. All Project Guide files unzip to a subfolder named DefaultProjectGuideFiles.

    The gbui:// protocol is removed. The custom "goal-based user interface" protocol and Project Guide resources are not installed with Microsoft Project 2010. For example, the following line in MainPage.htm:
           <script src="gbui://mainpage.js" language="JScript"></script>

       ... is changed to:
           <script src=mainpage.js" language="JScript"></script>

    You can find the modified Project Guide files in the Project 2010 SDK download:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=46007f25-b44e-4aa6-80ff-9c0e75835ad9&displaylang=en 

    Once you have the guide downloaded and extracted into a directory, for example, C:\PG\DefaultProjectGuideFiles, you need to author the following method in VBA:

    Sub Guide()
    
        If (Application.DisplayProjectGuide = False) Then
            OptionsInterfaceEx DisplayProjectGuide:=True, _ 
                   ProjectGuideUseDefaultFunctionalLayoutPage:=False, _ 
                   ProjectGuideUseDefaultContent:=False, _ 
                   ProjectGuideContent:="C:\PG\DefaultProjectGuideFiles\GBUI.XML", _ 
                   ProjectGuideFunctionalLayoutPage:="C:\PG\DefaultProjectGuideFiles\MAINPAGE.htm"
        Else
            OptionsInterfaceEx DisplayProjectGuide:=False
        End If
    
    End Sub

    Update the ProjectGuideContent and ProjectGuideFunctionalLayoutPage to point your Project Guide content.

    The next step is to create a button in the ribbon. In this example, I will add a button to the View tab:

    image

    Here is the code to do this:

    Private Sub AddGuideRibbonButton()
    
        Dim ribbonXML As String
        
        
        ribbonXML = "<mso:customUI xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">"
        ribbonXML = ribbonXML + "   <mso:ribbon>"
        ribbonXML = ribbonXML + "   <mso:qat/>"
        ribbonXML = ribbonXML + "       <mso:tabs>"
        ribbonXML = ribbonXML + "           <mso:tab idQ=""mso:TabView"">"
        ribbonXML = ribbonXML + "               <mso:group id=""Project_Guide"" label=""Project Guide"" autoScale=""true"">"
        ribbonXML = ribbonXML + "                   <mso:button id=""Project_Guide_Btn"" label=""Guide"" imageMso=""CategoryCollapse"" onAction=""Guide""/>"
        ribbonXML = ribbonXML + "               </mso:group>"
        ribbonXML = ribbonXML + "           </mso:tab>"
        ribbonXML = ribbonXML + "       </mso:tabs>"
        ribbonXML = ribbonXML + "   </mso:ribbon>"
        ribbonXML = ribbonXML + "</mso:customUI>"
       
        ActiveProject.SetCustomUI (ribbonXML)
        
    End Sub

    The last step is to hook up loading the button to an event. For this example, I am doing on the Project Open event, however, you may want to choose a different event based on your scenario.

    Private Sub Project_Open(ByVal pj As Project)
    
        AddGuideRibbonButton
    
    End Sub

    At this point, we have a working Project Guide in Project 2010:

    image

    Note: Because the Project Guide is an add-in, Project 2010 adds the Tasks, Resources, Track, and Report drop-down menus to the Add-Ins tab on the ribbon.

    Hope this helps,

    Chris Boyd

  • Project Programmability and Business Intelligence

    Getting Started with the PSI

    • 10 Comments

    Many people have asked, “How do I get started working with the PSI?” So I figured I would blog about creating a very simple application that interacts with the PSI. For this example, I will create a simple Windows Application that connects to Project Server and retrieves a list of resources for a given project.  

    Before we begin, it is important to realize that the PSI is made up of a number of Web Services. You can find a list of all the Project Server Web Service here: http://msdn2.microsoft.com/en-us/library/ms488627.aspx These Web Services are logically separated by business objects. For this example, we will be using both the Project and Resource Web Service.

    To get started, open visual studio and create a Windows Application. The first step will be to add web references to the Project and Resource Web Services:

    1.       In the Solution Explorer, right click on References

    2.       Click on Add Web Reference.
     

    3.       Type in the URL to the Project Web Service.

    The URL for the web service is:

    http://SERVER_NAME/PWA_INSTANCE/_vti_bin/psi/project.asmx

    Where SERVER_NAME is the name of the server Project Server is hosted on and PWA_INSTANCE is the name of the Project Web Access instance you want to connect to. _vti_bin/psi is where all the Project Server PSI Web Services reside. project.asmx is specific to the Project Web Service.

    4.       Give the Web Reference a name, such as WSProject

    5.       Click Add Reference

    This will add a reference to the Project Web Service. Repeat the same steps again, except this time, on step 3 specify resource.asmx instead of project.asmx and in step for name the Web Reference WSResource.

    Now that the Web References are step up, we can start to program! When I develop against the PSI, I always create a connection object to handle the various connections to the PSI. This allows me to reuse the connection class in a number of applications. Below is the source code of my connection class:

    using System;

    using System.Collections.Generic;

    using System.Text;

    using System.Data;

    using System.Net;

    using System.Resources;

    using System.Globalization;

    using System.Web.Services.Protocols;

    using System.Reflection;

     

    namespace PSIDemo

    {

        public class Connection

        {

            public const string Resource = "Resource";

            public const string Project = "Project";

     

            private static Dictionary<string, SoapHttpClientProtocol> WSDictionary;

     

            private string ms_ProjServURL;

     

            public Connection(string as_ProjServURL)

            {

                ms_ProjServURL = as_ProjServURL + "/_vti_bin/psi/";

     

                WSDictionary = new Dictionary<string, SoapHttpClientProtocol>();

            }

     

            public SoapHttpClientProtocol GetWebService(string as_WSName)

            {

                SoapHttpClientProtocol lo_WS;

     

                if (WSDictionary.TryGetValue(as_WSName.ToString(), out lo_WS) == false)

                {

                    switch(as_WSName)

                    {

                        case Resource:

                            Auth(Resource, new WSResource.Resource());

                            break;

                        case Project:

                            Auth(Project, new WSProject.Project());

                            break;

                    }

     

                    lo_WS = WSDictionary[as_WSName];

                }

     

                return lo_WS;

            }

     

            public static void Reset()

            {

                WSDictionary.Clear();

            }

     

            private void Auth(string as_WSName, SoapHttpClientProtocol as_WS)

            {

                try

                {

                    object [] parameters = new object [1];

     

                    parameters[0] = ms_ProjServURL + as_WSName + ".asmx";

                    MethodInfo setUrlMethod = as_WS.GetType().GetProperty("Url").GetSetMethod();

                    setUrlMethod.Invoke(as_WS, parameters);

     

                    parameters[0] = CredentialCache.DefaultCredentials;

                    MethodInfo setCredentialsMethod = as_WS.GetType().GetProperty("Credentials").GetSetMethod();

                    setCredentialsMethod.Invoke(as_WS, parameters);

     

                    WSDictionary.Add(as_WSName, as_WS);

                }

                catch (Exception ex)

                {

                    throw ex;

                }

            }

        }

    }

     

     

    This connection class has a dictionary of all the available PSI Web Services. With this implementation we are only concerned with the Project and Resource Web Service, so I have not included any other web services, but it would not be difficult to add additional PSI Web Services. It would only require a couple more lines of code for each Web Service. I will save that for another post.

    The method that handles the setup to the Web Service is Auth. For each Web Service, we need to set the URL for the server that we want to connect to at run time. The URL was passed in with the constructor and this can be a different URL then the one used for the Web Reference.  The second step is to set the credentials. For this example, we will only do NT authentication, but if there is interest, I can post an extension for Forms Authentication.  Once that is done, we add the Web Service to the dictionary and it is ready to be used.

    Next, I am going to add three controls, plus a few labels to the Windows form. The first control is a text box for the URL to the Project Server (txtURL), the second control is a drop down which will be populated with all the Projects the user has access to (cboProjects) and the third control will be a list box which will contain the names of resources that belong to the selected project (lstResources). Below is a screen shot of the form:

    This form has two methods that contain all the calls to the Web Services. The first method is the Leave event for the URL textbox:

     

     

    private void txtURL_Leave(object sender, EventArgs e)

    {

    cboProjects.Items.Clear();

     

          conn = new Connection(txtURL.Text);

     

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

     

          DataTable projList = projWS.ReadProjectList().Tables[0];

     

          foreach (DataRow dr in projList.Rows)

          {

                cboProjects.Items.Add(new ProjListItem(dr["Proj_Name"].ToString(), new Guid(dr[0].ToString())));

          }

     

          if (cboProjects.Items.Count > 0)

          {

                cboProjects.SelectedItem = cboProjects.Items[0];

          }

    }

     

    In this method, we instantiate the Connection object and pass in the URL for the Project Server. This is the URL that will be used at run time. Next, we get the Project and Resource Web Services from the connection object. This allows us to read the projects and populate the drop down with all the project names.  I have created a basic object, ProjListItem, which contains the GUID and name of the project so that we can easily retrieve the GUID later on to get the list of resources. It is important to note here, that we are working with datasets. The majority of our Web Services have datasets that can be manipulated and sent back to the server to update the data.

    The second method is the select index changed for the drop down list of project names:

     

     

    private void cboProjects_SelectedIndexChanged(object sender, EventArgs e)

    {

    lstResources.Items.Clear();

               

    WSProject.ProjectTeamDataSet pds;

     

    ProjListItem projItem = (ProjListItem)cboProjects.SelectedItem;

     

    pds = projWS.ReadProjectTeam(projItem.getGuid());

    DataTable dt = pds.Tables["ProjectTeam"];

     

    foreach (DataRow dr in dt.Rows)

          {

                lstResources.Items.Add(dr["Res_Name"].ToString());

    }

    }

     

     

    Here we retrieve the GUID for the selected project from the ProjListItem object that we populated the drop down list in the first method and we get the resources on the team by calling ReadProjectTeam method and passing the selected project GUID.  ReadProjectTeam returns a dataset that contains a data table “ProjectTeam” that lists all the resources that are team members on the project.

    So, now we have a little application that is able to connect to the server and retrieve data,

    Chris Boyd

  • Project Programmability and Business Intelligence

    Custom Field and Lookup Table Webcast

    • 6 Comments

    Hello,

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

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

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

     

    Chris Boyd

     

  • Project Programmability and Business Intelligence

    Project Server 2013 reporting database (RDB) schema reference

    • 4 Comments

    The attached Project2013Reporting.exe file contains a draft release of the schema reference for the reporting tables and views in Project Server 2013. To install the files, run Project2013Reporting.exe. The next release of the Project 2013 SDK download will also include the reporting schema reference; that will be announced here when the Project 2013 SDK update is published.

    The Project Server 2013 RDB and OLAP cubes are accessible only with an on-premises installation of Project Server. For Project Online, you can use REST queries of the ProjectData OData service. The ProjectData service is also available with on-premises installations. For more information, see ProjectData - Project 2013 OData service reference in the Project 2013 SDK.

    The attached file includes the following:

    • ProjectServer2013_ReportingDB.chm is the HTML Help build of the RDB reference. It includes only the tables, views, stored procedures (SProcs), and user-defined functions (UDFs) that are in the dbo user namespace of the Project Server database. 

    Note: The draft, published, and archive database objects are not documented. Project Server 2013 combines objects from the four Project Server 2010 databases into one database. The default name of the database is ProjectService; different Project Web App instances can have different database names.

    • OLAPCubeSchemas2013.xlsx includes a worksheet for each of the 14 OLAP cubes that are available to build with Project Server 2013. Each worksheet lists the dimensions, measures, and properties that are available in the cube.

    The Project Server 2013 RDB includes three new tables, 27 new views, and a total of 33 tables and views that include new fields. You can easily find the new items by searching, for example, for “New field” (with quotes). The following screenshot shows the new ProjectVisibilityMode field in the dbo.MSP_EpmProject_UserView view:

    imgSearchNewField_84 

  • Project Programmability and Business Intelligence

    Connecting a Project Task Pane App to PWA

    • 2 Comments

    Introduction

    Apps for Office present a great new opportunity to bring data from a variety of sources into Office applications. The new Project specifically offers a very powerful way to surface both SharePoint and Project Web App data in a task pane app in Project Professional. To connect with SharePoint or PWA from the client, we use on-the-fly OAuth flow as presented in the SharePoint 2013 SDK. The App for Project described in this article connects with the same PWA site collection that Project Professional is currently connected to, and displays data about the currently selected task that is not otherwise available from within Project Professional. This will work with all versions of Project Professional (C2R, MSI, On Demand) and with all PWA environments (Project Online, Project Server).

    image

    Setup

    Prerequisites for this project are:

    • Visual Studio 2012
    • IIS or IIS Express configured to allow applications to run on your server (at localhost)
    • Office 2013 and SharePoint 2013 tools for Visual Studio, available here
    • Project Professional 2013
    • Project Online tenant or Project Server

    The first step is to launch Visual Studio 2012. Create a new App for Office 2013 project as shown below. Let’s call it “TaskLastModifiedApp”.

    image

    In the next dialog, make this a Task Pane App for Project.

    We need to add references, as this app will use a number of APIs across Office and SharePoint. These DLLs may be in a different location on your system. Most of these references are automatically added if you use the App for SharePoint template, so if you can’t find these on your system, create a quick App for SharePoint solution and note the reference paths to help you out. You should add:

    • C:\Program Files\Reference Assemblies\Microsoft\Windows Identity Foundation\v3.5\Microsoft.IdentityModel.dll
    • C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.IdentityModel.Extensions\v4.0_2.0.0.0__69c3241e6f0468ca\Microsoft.IdentityModel.Extensions.dll
    • C:\Program Files\Reference Assemblies\Microsoft\Windows Identity Foundation\v3.5\Microsoft.IdentityModel.WindowsTokenService.dll
    • C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.ProjectServer.Client.dll
    • C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll
    • C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll
    • C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.IdentityModel.dll
    • C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.IdentityModel.Selectors.dll
    • C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.ServiceModel.dll

    Additionally, you will need to add a copy of TokenHelper.cs, which is generated when creating an autohosted or provider-hosted App for SharePoint project.

    Task Pane App Manifest

    The actual task pane app is just an XML manifest. Open up TaskLastModifiedApp.xml from the TaskLastModifiedApp project in your Solution Explorer. Replace its contents with the following:

     1: <?xml version="1.0" encoding="UTF-8"?>
     2: <OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="TaskPaneApp">
     3:   <Id>[leave this line alone]</Id>
     4:   <Version>1.0</Version>
     5:   <ProviderName>Microsoft</ProviderName>
     6:   <DefaultLocale>en-US</DefaultLocale>
     7:   <DisplayName DefaultValue="TaskLastModifiedApp" />
     8:   <Description DefaultValue="This app will show you when the selected task was last modified"/>
     9:   <AppDomains>
     10:     <AppDomain>https://localhost:44301/</AppDomain>
     11:   </AppDomains>
     12:   <Capabilities>
     13:     <Capability Name="Project" />
     14:   </Capabilities>
     15:   <DefaultSettings>
     16:     <SourceLocation DefaultValue="https://localhost:44301/pages/URLConstructor.aspx" />
     17:   </DefaultSettings>
     18:   <Permissions>ReadWriteDocument</Permissions>
     19: </OfficeApp>

    Replace the port after localhost (in both instances) with whatever port you have configured IIS to use for SSL. Make sure to toggle the “SSL Enabled” property on the TaskLastModifiedAppWeb project to true. Let whatever ID was originally set in the manifest remain.

    Architecture

    Next, delete the TaskLastModifiedApp.html page – we will need .aspx pages in this project. The basic architecture of the task pane app is as follows:

    • When the task pane app is launched, it loads “URLConstructor.aspx”, which pulls the PWA URL from the client and constructs a call to OAuthAuthorize with the proper tokens to request permissions for the app to access PWA data. This page loads “URLConstructor.js” to interact with the client.
    • OAuthAuthorize is launched in a new window, since we cannot predict the customer’s Project domain. After the user trusts the app, that new window is redirected to “PostOAuth.aspx”, which surfaces the auth code back to URLConstructor.aspx.
    • Once URLConstructor.aspx has the auth code, the task pane app is redirected with this as a token to “Default.aspx”, which has the functional code for the app. This page uses Project CSOM code in its code-behind page to read data from PWA, as well as “TaskLastModifiedApp.js” to interact with the client.

    Constructing the OAuthAuthorize URL

    The complete code for URLConstructor.aspx is as follows:

     1: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="URLConstructor.aspx.cs" Inherits="TaskLastModifiedAppWeb.Pages.URLConstructor" %>
     2:  
     3: <!DOCTYPE html>
     4:  
     5: <html xmlns="http://www.w3.org/1999/xhtml">
     6:  
     7: <head runat="server">
     8:     <title>TaskLastModifiedApp</title>
     9:     <meta http-equiv="X-UA-Compatible" content="IE=9" />
     10:     <script type="text/javascript" src="..\Scripts\Office\1.0\MicrosoftAjax.js"></script>
     11:     <script type="text/javascript" src="..\Scripts\Office\1.0\Office.js"></script>
     12:     <script type="text/javascript" src="..\Scripts\URLConstructor.js"></script>
     13:     <script type="text/javascript">
     14:         function getClientId() {
     15:             var clientId = '<%=ConfigurationManager.AppSettings["ClientId"].ToString() %>'; //read the clientID from web.config
     16:             getPwaUrl(clientId); //return to client code
     17:         }</script>
     18: </head>
     19:  
     20: <body>
     21:     Redirecting...
     22: </body>
     23:  
     24: </html>

    This page needs to be an .aspx page in order to read from web.config, but does not need anything in its code-behind. The clientId read from web.config is needed for the authorization flow. getPwaUrl() is a function within URLConstructor.js.

    The complete code for URLConstructor.js is as follows:

     1: var _projDoc;
     2: var pwaUrl;
     3: var oAuthUrl;
     4:  
     5: Office.initialize = function (reason) {
     6:     _projDoc = Office.context.document;
     7:     getClientId(); //on document load, reads the ClientId from web.config first since it is server-side
     8: }
     9:  
     10: function getPwaUrl(clientId) { //uses Office App API to read PWA URL
     11:     _projDoc.getProjectFieldAsync(Office.ProjectProjectFields.ProjectServerUrl,
     12:         function (asyncResult) {
     13:             if (asyncResult.status == Office.AsyncResultStatus.Succeeded) {
     14:                 pwaUrl = asyncResult.value.fieldValue;
     15:                 generateUrl(clientId); //creates the OAuthAuthorize URL with necessary parameters
     16:             }
     17:             else {
     18:                 logMethodError("getProjectFieldAsync", asyncResult.error.name, asyncResult.error.message);
     19:             }
     20:         }
     21:     )
     22: };
     23:  
     24: function generateUrl(clientId) {
     25:     oAuthUrl = pwaUrl + "/_layouts/15/OAuthAuthorize.aspx?IsDlg=1&client_id=" + clientId + "&scope=Projects.Read&response_type=code&redirect_uri=https://localhost:44301/pages/PostOAuth.aspx";
     26:     authWindow = window.open(oAuthUrl);
     27:     codeListener(); //start listening for the auth code
     28: }
     29:  
     30: function codeListener() {
     31:     setTimeout(function () { readCode(); }, 1000); //check for the auth code every one second
     32: }
     33:  
     34: function readCode() {
     35:     try { //if we can actually reach the authCode field on PostOAuth.aspx
     36:         authCode = authWindow.document.getElementById("authCode").value;  //pull the authCode value
     37:         if (authCode != "NA") { //if it is not the default "NA"
     38:             authWindow.close(); //close the new window
     39:             document.location.href = "/Pages/Default.aspx?code=" + authCode; //redirect task pane to the app code with the authCode token
     40:         }
     41:     }
     42:     catch (e) {
     43:         codeListener(); //if we couldn't reach PostOAuth.aspx, wait another second and try again
     44:     }
     45: }

    When the task pane app loads, it first reads web.config from the aspx page, since this is server-side code. Once it has the clientId, we read the PWA URL. We then create the full OAuthAuthorize URL with the parameters specified above. scope=Projects.Read requests read permission to projects on the current PWA site. Make sure to match the SSL port here as well, as before.

    On running the app, a new window will open up outside of Project that prompts the user to login to PWA (if they have not checked “Keep me signed in” previously). They will then be presented with a “Do you trust…” page, the same as if they were installing an App for SharePoint. This is the OAuthAuthorize.aspx page. Once trusted, that new window navigates to PostOAuth.aspx, presented below:

     1: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PostOAuth.aspx.cs" Inherits="TaskLastModifiedAppWeb.Pages.PostOAuth" %>
     2:  
     3: <!DOCTYPE html>
     4:  
     5: <html xmlns="http://www.w3.org/1999/xhtml">
     6: <head runat="server">
     7:     <title></title>
     8: </head>
     9: <body>
     10:     <form id="form1" runat="server">
     11:     <div>
     12:     <asp:HiddenField ID="authCode" runat="server" value="NA"/>
     13:         Closing...
     14:     </div>
     15:     </form>
     16: </body>
     17: </html>

    And PostOAuth.aspx.cs:

     1: using System;
     2: using System.Collections.Generic;
     3: using System.Linq;
     4: using System.Web;
     5: using System.Web.UI;
     6: using System.Web.UI.WebControls;
     7:  
     8: namespace TaskLastModifiedAppWeb.Pages
     9: {
     10:     public partial class PostOAuth : System.Web.UI.Page
     11:     {
     12:         protected void Page_Load(object sender, EventArgs e)
     13:         {
     14:             var code = Request.QueryString["code"];
     15:             authCode.Value = code;
     16:         }
     17:     }
     18: }

    This page saves the auth code token in a hidden field. The task pane app, still on URLConstructor.aspx, waits for this value and then closes the new window. The app then continues on to default.aspx with the proper code token needed to finish the OAuth flow.

    Reading the Last Modified Date of the Selected Task

    The remainder of this article is an example of what you might do in your task pane app now that you have read access to PWA data. This example will show you the Last Modified date and time of the task you have selected. On launch, it shows you data for the selected task, and as you change tasks, the data is updated using an event handler.

    The complete code for Default.aspx is as follows:

     1: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TaskLastModifiedAppWeb.Pages.Default" %>
     2:  
     3: <!DOCTYPE html>
     4:  
     5: <html xmlns="http://www.w3.org/1999/xhtml">
     6: <head runat="server">
     7:     <title>TaskLastModifiedApp</title>
     8:     <meta http-equiv="X-UA-Compatible" content="IE=9" />
     9:     <script type="text/javascript" src="..\Scripts\Office\1.0\MicrosoftAjax.js"></script>
     10:     <script type="text/javascript" src="..\Scripts\Office\1.0\Office.js"></script>
     11:     <script type="text/javascript" src="..\Scripts\TaskLastModifiedApp.js"></script>
     12:     
     13: </head>
     14:  
     15: <body>
     16:     <form id="form1" runat="server">
     17:         
     18:         <asp:HiddenField ID="projGuidHF" runat="server" />
     19:         <asp:HiddenField ID="taskGuidHF" runat="server" />
     20:         <asp:HiddenField ID="pwaUrlHF" runat="server" />
     21:  
     22:         <asp:Label ID="TaskLastModLabel" runat="server" Text="Loading..." />
     23:  
     24:         <div style="display: none;">
     25:             <asp:Button ID="hiddenTaskChangedButton" runat="server" OnClick="OnTaskChanged" />
     26:         </div>
     27:  
     28:     </form>
     29:  
     30: </body>
     31: </html>

    This page contains three hidden fields used to pass data back and forth between the client-side code and the server-side code. It also leverages a label to surface the results to the user, and a hidden button that the client-side code uses to call a server-side function, as you will see below.

    The complete code for TaskLastModifiedApp.js is as follows:

     1: var _projDoc;
     2: var taskGuid;
     3: var projGuid;
     4: var pwaUrl;
     5:  
     6: // This runs after every postback
     7: Office.initialize = function (reason) {
     8:     _projDoc = Office.context.document;
     9:     if (document.getElementById("pwaUrlHF").value == "NA") { //if this is the first run
     10:         firstRun();
     11:     }
     12:     manageTaskEventHandler('addHandlerAsync'); //need to re-register event handler after each postback
     13: }
     14:  
     15: // Only need these on the first page load, not on postbacks
     16: function firstRun() {
     17:     getProjGuid();
     18:     _projDoc.getProjectFieldAsync(Office.ProjectProjectFields.ProjectServerUrl,
     19:         function (asyncResult) {
     20:             pwaUrl = asyncResult.value.fieldValue;
     21:             document.getElementById("pwaUrlHF").value = pwaUrl;
     22:         }
     23:     )
     24:     getTaskGuid();
     25: }
     26:  
     27: // Get the GUID of the selected task, comes from SDK
     28: function getTaskGuid() {
     29:     var TaskLastModLabel = document.getElementById("TaskLastModLabel");
     30:     TaskLastModLabel.innerHTML = "Loading...";
     31:     _projDoc.getSelectedTaskAsync(function (asyncResult) {
     32:         taskGuid = asyncResult.value;
     33:         document.getElementById("taskGuidHF").value = taskGuid; //saves the task GUID to a hidden field to pass to the code-behind
     34:         document.getElementById("hiddenTaskChangedButton").click(); //runs the CSOM calls in the aspx.cs file
     35:     });
     36: }
     37:  
     38:  // Get the GUID of the current project.
     39: function getProjGuid() {
     40:     _projDoc.getProjectFieldAsync(Office.ProjectProjectFields.GUID,
     41:         function (asyncResult) {
     42:             projGuid = asyncResult.value.fieldValue;
     43:             document.getElementById("projGuidHF").value = projGuid; //saves the project GUID to a hidden field to pass to the code-behind
     44:         }
     45:     )
     46: }
     47:  
     48: // Task selection changed event handler.
     49: function onTaskSelectionChanged(eventArgs) {
     50:     getTaskGuid();
     51: }
     52:  
     53: // Add or remove a task selection changed event handler.
     54: function manageTaskEventHandler(docMethod) {
     55:     manageEventHandlerAsync(
     56:         Office.EventType.TaskSelectionChanged,      // The task selection changed event.
     57:         onTaskSelectionChanged,                     // The event handler.
     58:         docMethod                // The Office.Document method to add or remove an event handler.
     59:     );
     60: }
     61:  
     62: // Add or remove the specified event handler.
     63: function manageEventHandlerAsync(eventType, handler, operation, onComplete) {
     64:     _projDoc[operation]   //The operation is addHandlerAsync or removeHandlerAsync.
     65:     (
     66:         eventType,
     67:         handler,
     68:         function (asyncResult) {
     69:             // code here runs after event has been registered (or failed)
     70:         }
     71:     );
     72: }

    The first time this code runs, it pulls the PWA URL (just like we did in URLConstructor.js) and saves it to one of our hidden fields, registers a client-side event handler to capture when a new task is selected, and starts the process of connecting to PWA and pulling the data we need, which is mostly done in the code-behind file. After each postback, we do not need to recapture the PWA URL, as our ProjectContext is maintained as a static variable.

    We need to make one quick tweak to TokenHelper.cs first – change GetRealmFromTargetUrl from a private method to a public method.

    The complete code for Default.aspx.cs is as follows:

     1: using System;
     2: using System.Collections.Generic;
     3: using System.Linq;
     4: using System.Web;
     5: using System.Web.UI;
     6: using System.Web.UI.WebControls;
     7: using Microsoft.ProjectServer.Client;
     8: using Microsoft.SharePoint.Client;
     9:  
     10: namespace TaskLastModifiedAppWeb.Pages
     11: {
     12:     public partial class Default : System.Web.UI.Page
     13:     {
     14:         public static ProjectContext projContext;
     15:         public static PublishedProject thisProj;
     16:         public Guid projGuid;
     17:  
     18:         protected void Page_Load(object sender, EventArgs e)
     19:         {
     20:             if (!IsPostBack) //set values of hidden fields if this is the first page load
     21:             {
     22:                 projGuidHF.Value = "NA";
     23:                 taskGuidHF.Value = "NA";
     24:                 pwaUrlHF.Value = "NA";
     25:             }
     26:         }
     27:  
     28:  
     29:         protected void GetContexts()
     30:         {
     31:             var code = Request.QueryString["code"]; //pulls the code token from the request
     32:  
     33:             string targetPwa = pwaUrlHF.Value; //pulls the PWA URL from where the Office app API stored it
     34:  
     35:             Uri targetPwaUri = new Uri(targetPwa);
     36:  
     37:             var tRealm = TokenHelper.GetRealmFromTargetUrl(targetPwaUri);
     38:  
     39:             Uri rUri = new Uri("https://localhost:44301/pages/PostOAuth.aspx"); //hardcoded link to redirect_uri
     40:  
     41:             var clientContext = TokenHelper.GetClientContextWithAuthorizationCode(targetPwa, "00000003-0000-0ff1-ce00-000000000000", code, tRealm, rUri);
     42:  
     43:             projContext = GetProjectContextWithAuthorizationCode(targetPwa, "00000003-0000-0ff1-ce00-000000000000", code, tRealm, rUri);
     44:  
     45:             projGuid = new Guid("{" + projGuidHF.Value + "}"); //loads the current project through CSOM
     46:  
     47:             var projects = projContext.LoadQuery(projContext.Projects.Where(proj => proj.Id == projGuid));
     48:             projContext.ExecuteQuery();
     49:             thisProj = projects.First();
     50:         }
     51:  
     52:         protected void OnTaskChanged(object sender, EventArgs e) //determine the selected task's last modified date
     53:         {
     54:             if (thisProj == null)
     55:             {
     56:                 GetContexts();
     57:             }
     58:             
     59:             var taskGuid = new Guid(taskGuidHF.Value);
     60:  
     61:             var tasks = projContext.LoadQuery(thisProj.Tasks.Where(task => task.Id == taskGuid)); //load the selected task off of the project
     62:             projContext.ExecuteQuery();
     63:             PublishedTask thisTask = tasks.First();
     64:             string dateMod = thisTask.Modified.ToString("D"); //pull out the Modified field on the task
     65:             string timeMod = thisTask.Modified.ToString("t");
     66:             TaskLastModLabel.Text = "The selected task was last modified on " + dateMod + " at " + timeMod + ".";
     67:         }
     68:  
     69:         public static ProjectContext GetProjectContextWithAuthorizationCode(string targetUrl,string targetPrincipalName,string authorizationCode,string targetRealm,Uri redirectUri)
     70:         {
     71:             Uri targetUri = new Uri(targetUrl);
     72:  
     73:             string accessToken =
     74:                 TokenHelper.GetAccessToken(authorizationCode, targetPrincipalName, targetUri.Authority, targetRealm, redirectUri).AccessToken;
     75:  
     76:             return GetProjectContextWithAccessToken(targetUrl, accessToken);
     77:         }
     78:  
     79:         public static ProjectContext GetProjectContextWithAccessToken(string targetUrl, string accessToken)
     80:         {
     81:             Uri targetUri = new Uri(targetUrl);
     82:  
     83:             ProjectContext projContext = new ProjectContext(targetUrl);
     84:  
     85:             projContext.AuthenticationMode = ClientAuthenticationMode.Anonymous;
     86:             projContext.FormDigestHandlingEnabled = false;
     87:             projContext.ExecutingWebRequest +=
     88:                 delegate(object oSender, WebRequestEventArgs webRequestEventArgs)
     89:                 {
     90:                     webRequestEventArgs.WebRequestExecutor.RequestHeaders["Authorization"] =
     91:                         "Bearer " + accessToken;
     92:                 };
     93:  
     94:             return projContext;
     95:         }
     96:     }
     97: }

    PageLoad()

    The first time the page loads, we need to initialize the hidden field values. This enables us to not set them directly in Default.aspx (and thus lose their values after a postback) and lets the client-side code distinguish between a first run load and a postback.

    GetContexts()

    This code also only runs once, assuming the current project remains loaded. This handles the last part of the OAuth flow – we use the code token from OAuthAuthorize to generate a client context and a project context using methods in TokenHelper.cs, as well as slightly modified methods GetProjectContextWithAuthorizationCode() and GetProjectContextWithAccessToken().

    OnTaskChanged()

    This first checks to make sure we have a project loaded from which to pull data. We then read the selected task guid from the hidden field, which was updated client-side before this method was called. We use Project CSOM to load the selected task from PWA and read its Last Modified field, which is then presented to the user in a readable format using the label on Default.aspx.

    Register the App ID and App Secret

    Since we are not submitting this app to the Office Store, we need to register it on our test tenant. On your tenant, navigate to https://[your PWA site]/_layouts/15/appregnew.aspx. Generate a new App Id and App Secret, set the Title to “TaskLastModifiedApp”, set the App Domain to localhost:44301 (or wherever the app code is running), and set the Redirect URI to match the redirect_uri token value in the oAuth URL created in URLConstructor.js.

    image

    Hit Create, and then add the App Id and App Secret to web.config in your Visual Studio solution. It should look like the following when you are done, with your values for ClientId and ClientSecret:

     1: <?xml version="1.0"?>
     2:  
     3: <configuration>
     4:     <system.web>
     5:       <compilation debug="true" targetFramework="4.0" />
     6:     </system.web>
     7:   <appSettings>
     8:     <add key="ClientId" value="a9ce3d5a-bb14-4aad-9c27-41a05c473b4d" />
     9:     <add key="ClientSecret" value="hL0C8wt2PPaBYNYRMZzcUcu3C/Vv0fbm48djGzyIXOw=" />
     10:   </appSettings>
     11: </configuration>

    Time to Test!

    To run the app, just hit F5 in Visual Studio, which will launch Project Professional. Make sure to connect to a PWA profile, then load a published project (or create and publish a new one). From a task view, select the Project tab in the ribbon, hit the dropdown for Apps for Office, and click TaskLastModifiedApp.

    image

    The app will launch in a task pane on the right side of the screen. It’ll prompt you to trust the app, quickly load, and then display the last modified date and time of the selected task.

    image

    Select a different task, and the data will update almost instantly.

    image

    Wrap-Up

    In this blog post, you have learned how to create a task pane app in Project Professional that can display data from PWA that would not normally be visible in the client. The app uses SharePoint’s OAuthAuthorize page to request permissions from PWA and handle the authentication handshake between the app code and your online data. For more on working with OAuth, make sure to check out the relevant node in the SharePoint 2013 SDK. For more information on PWA programmability, check out the Project 2013 SDK. To learn more about writing task pane apps for the new Project, see this node in the Office 2013 SDK.

  • 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

    Learn How To Build a Silverlight App for PWA. . .

    • 0 Comments

    . . . which is, after all, a SharePoint application.

    See the SharePoint and Silverlight Training Course. It includes 30 hands-on labs, including labs on accessing data through WCF services.

  • Project Programmability and Business Intelligence

    Customizing E-Mail for Project Server Notifications

    • 5 Comments

     

    Jim Corbin has written a draft article on how to customize e-mail notifications from Project Server:  

     

    Here is a draft of an article and source code that will be published in the Project 2007 SDK update late this year. The draft article Customizing Notifications.doc is in the attached NotificationsEventHandler.zip.

     

    The article explains how to create an OnSending event handler for notifications, and shows the code for extracting relevant information from the notification XML data. If the event handler matches a notification type (specific alerts and reminders you want to customize), it transforms the XML data to a text or HTML e-mail using your custom XSLT file, sends the e-mail to the proper recipient, and then cancels the OnSending pre-event so that Project Server doesn’t send the default notification.

     

    The article includes a discussion of how to extract the default XSLT files for notification e-mails from the Published database. The download includes the English language XSLT files, but you need to extract them for other languages.  The article also includes a discussion of how to configure an SMTP virtual server for a test installation of Project Server.

  • Project Programmability and Business Intelligence

    Implementing My Tasks “Read Only Custom Fields”

    • 1 Comments

    Using Project Server 2007’s Reporting Database and SQL Server Reporting Services 2005

    The attached report illustrates how to safely query the RDB to allow team members to see a read only view of task assignment data. We’ve picked a random task custom field to illustrate the concept. I’ve deliberately defined the report to use “drill down” by Project Name to mimic the My Tasks grid behavior and to (UI) scale for users with lots of task assignments.

    The figure below illustrates the My Tasks web part placed above the Report Viewer web part which is displaying the sample report.

    image

    Customizing and Installing the Report

    1. Use the SQL Server 2005 Business Intelligence Workbench (available with SQL Server 2005) to customize the report to add the fields and custom fields that you want to display to your users.
    2. Customize the report’s data source to connect to your Reporting Database.

      Note that when you deploy this report to the server you should ensure that the data source is customized with a cached userid and password that has access to read the Reporting Database. These credentials are used to avoid prompting the user for credentials and are stored securely in SQL Server.

       image
    3. Add the Reporting Services Report Viewer web part to the My Tasks page and edit the web part properties to point to the report.

      image

      Note the non-default web part settings for the report viewer:

      - Tool Bar: Small (the user needs to be able to enter the date filter)
      - Fixed Height: 12 Inches

    Notes on Implementation

    The report has two parameters that are used in the query:

    1. (Hidden) UserAccount is the userid – this is used to get the caller’s ResourceUID and hence filter assignments correctly, this should never be “unhidden” as the user could then enter any userid with the risk of information disclosure.
    2. TimeFilter – defaults to two weeks previous from “now”, but can then be set by the caller to any date via the calendar picker. It filters out all task assignments that started before that date. [Note that you could get creative here and add different/additional filters if you want]. Use Report/Report Parameters to edit the default setting if required.
    3. Note that the query filters out “unpublished” task assignments – when a project is published its data moves to the Reporting database. Task assignments only move to the My Tasks “statusing” tables if the Project Manager has decided that they be published.

    Other notes on deployment

    To maximize performance we avoided use of the _Userview SQL Views and referenced the underlying tables. If you want to access Task Assignment custom fields (the example show Task Custom Fields) then the query in the report will need to be reworked slightly, this should be trivial.

    The RDB is only minimally indexed. We strongly recommend the addition of secondary indexes on the following tables:

    Msp_Epm_Resource (Composite on ResourceNTAccount, ResourceUID)

    Msp_Epm_Assignment (Composite on ResourceUID, AssignmentStartDate)

    Finally…

    And if you are delivering a small sub set of custom fields you could consider an indexed view over those fields to maximize performance.

    Patrick Conlan

  • Project Programmability and Business Intelligence

    Setting the Project Owner

    • 4 Comments

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

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

    private Guid GetResourceGuid(string ls_name)

    {

      WSResource.Resource lo_resWS =

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

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

     

      string nameColumn = lo_resDS.Resources.RES_NAMEColumn.ColumnName;

      string resUID = lo_resDS.Resources.RES_UIDColumn.ColumnName;

     

      PSLibrary.Filter.FieldOperationType equal =   

        PSLibrary.Filter.FieldOperationType.Equal;

     

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

     

      lo_filter.FilterTableName = lo_resDS.Resources.TableName;

     

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

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

     

     

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

     

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

    }

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

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

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

    Chris Boyd

  • Project Programmability and Business Intelligence

    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

Page 1 of 11 (255 items) 12345»