October, 2007

Christophe Fiessinger's Blog
Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings

  • Christophe Fiessinger's Blog

    Figuring out the ChangeXML format when using the 'UpdateStatus' method

    The Statusing web service (Project Server Interface) in EPM2007 contains a powerful method called UpdateStatus. This method can be used to make changes to tasks and assignments; for instance: Actual Work, % Work Complete, ... please refer to the ChangeList Schema Reference in the SDK for a complete list.

    To help you generate the XML string required to achieve your customization needs the latest version of the SDK has a powerful tool that will help you generate the ChangeXML command: How to: Generate ChangeXML for Statusing Updates

    Your customization will ideally generate the proper ChangerXML command dynamically based on the entities you need to update.

    A sample method to generate the ChangeXML string could look like this:

      public static StringBuilder CreateChangeXml(TimeSheetWS.TimesheetDataSet timesheetDs)
                StringBuilder changeXml = new StringBuilder();
                string pidRegularWork = PSLibrary.AssnConstID.s_apid_regular_work.ToString(System.Globalization.CultureInfo.InvariantCulture);
                for (int i = 0; i < timesheetDs.Lines.Count; i++)
                    if (timesheetDs.Lines[i].TS_LINE_VALIDATION_TYPE == (int)PSLibrary.TimesheetEnum.ValidationType.Verified)
                        changeXml.AppendFormat("<Proj ID=\"{0}\">", timesheetDs.Lines[i].PROJ_UID.ToString());
                        changeXml.AppendFormat("<Assn ID=\"{0}\">", timesheetDs.Lines[i].ASSN_UID.ToString());
                        changeXml.AppendFormat("<Change PID=\"{0}\">{1}</Change>", pidRegularWork, timesheetDs.Lines[i].TS_LINE_ACT_SUM_VALUE.ToString(System.Globalization.CultureInfo.InvariantCulture));
                return changeXml;
  • Christophe Fiessinger's Blog

    Project Server Extended Report Pack from Project Conference

    Following my presentation yesterday on reporting at the Project Conference in Seattle, please find below the "Extended" EPM report pack I demoed (leveraging SQL Server Reporting Services).

    Reports added are:

    • Deliverables and Dependencies
    • Issues and Risks Report
    • Project Workspaces
    • ProjectList PSI XML
    • ResourceList PSI XML
    • Risks - XML Data Srouce
    • SSAS Timesheet
    • Timesheet Audit 2
  • Christophe Fiessinger's Blog

    How to track EPM Resource field changes in your Reporting Database

    Leveraging the EPM 2007 Eventing service as well as the Reporting database here is a way of tracking all changes made to a Resource's fields. A common request I heard from customers is the ability to track when a Resource changes from one node to another node in the RBS.

    Steps to do it (code download below!!!):

    • Created a custom table in the Reporting database called: MCS_EpmResource_UserView_Tracking
    • Created a custom stored procedure in the Reporting database called: MCS_EPM_InsertResourceChange that takes the RES_UID as a parameter
    DECLARE @ResourceTimesheetManagerUID uniqueidentifier
    DECLARE @ResourceModifiedDate datetime 
    SELECT    @ResourceModifiedDate = ResourceModifiedDate,
            @ResourceTimesheetManagerUID = ResourceTimesheetManagerUID
    FROM MSP_EpmResource
    WHERE ResourceUID = @ResourceUID 
    IF NOT EXISTS(SELECT * FROM dbo.MCS_EpmResource_UserView_Tracking WHERE ResourceUID = @ResourceUID AND ResourceModifiedDate = @ResourceModifiedDate) 

    Note that I only INSERT a new row in my history table if the Modified Date has changed.

    • Created a custom event handler that gets called every time a Resource is changed (OnResourceChanged)
            public override void OnResourceChanged(PSContextInfo contextInfo, ReportingPostResourceChangedEventArgs e)
                // Standard processing
                base.OnResourceChanged(contextInfo, e);
                // Load settings from Microsoft.Office.Project.Server.Eventing.exe.config
                // Build T-SQL command
                string cmdText = string.Format("EXEC MCS_EPM_InsertResourceChange '{0}'",e.ResourceUid.ToString());
                // Store Resource field update
                ConnectAndExecuteNonQuery(connectionString, cmdText);
    • Deploy event handler in GAC and define an entry in the PWA configuration (refer to SDK for more details)
    • and voila!

    From now on, every time a Resource's field is changed (for instance it's name, RBS tec ...) and entry will be inserted in the tracking table mentioned above.

    Eventually you will want to build a report that leverages that historical data to show you a view before and after the change was made for instance.

  • Christophe Fiessinger's Blog

    EPM Database Maintenance Tasks

    After you deploy EPM 2007 in your production environment, I highly recommend you have a SQL maintenance plan in place.

    Maintenance tasks that should be in this plan include:

    • Backup
    • Check DB integrity
    • Rebuild Index (-> update statistics)
    • History cleanup
    • Maintenance cleanup

    For more information on this subject please check the standard SQL documentation as well as these two articles for SharePoint databases (thank you Chris Gideon for the pointers):

  • Christophe Fiessinger's Blog

    Blog Using Windows Live Writer

    Thanks to Brian Smith, I started using this tool today: Windows Live Writer instead of Copy & Paste to update my Community Server blog on MSDN.

  • Christophe Fiessinger's Blog

    Using a PSI Data Source in SQL Server Reporting Services

    SQL Server Reporting Services 2005 (SSRS) has the ability to query data from XML data sources and thus from Web Services, for more information on this subject refer to Reporting Services: Using XML and Web Service Data Sources.

    Since EPM 2007 is architected around Web Services and specifically around the Project Server Interface (PSI), I will demonstrate how to build a SSRS report using a PSI as a data source.

    • Start Visual Studio 2005
    • Add a Data Source of type XML and enter the URL of the Project Web Service


    • Add a report without using the Report Wizard
    • Go to the Data Tab and enter the following XML query
    • <Query>
         <Method Namespace="http://schemas.microsoft.com/office/project/server/webservices/Project/" Name="ReadProjectList"/>
       <ElementPath IgnoreNamespaces="true">ReadProjectListResponse/ReadProjectListResult/diffgram/ProjectDataSet/Project</ElementPath> 
    • Add a Table control and pick some fields, for instance Fields!PROJ_UID.Value and Fields!PROJ_NAME.Value, then Preview report
    • and voila!


    This is a basic example to demonstrate how it works, what you would probably end up doing is a PSI extension that queries your Reporting database leveraging the EPM Out Of the Box security model. I will demonstrate more useful sample at the Project Conference next week.

    For more information on PSI Extension please refer to the EPM SDK and to past posts on my blog.

  • Christophe Fiessinger's Blog

    Sample Code to Update a Resource’s Custom Field

    Please find below a code sample I wrote that updates a Resource's Custom Field of type Flag.

    You can call this method in a loop for instance to automatically reset a CF for all resources.

       1:          public static bool UpdateResourceCustomField(ResourceWS.ResourceDataSet resDS, Guid cfUID, bool flagValue)
       2:          {
       3:              Guid cfExists = GetCUSTOM_FIELD_UID(resDS, cfUID);
       4:              if (cfExists != Guid.Empty)
       5:              {
       6:                  for (int cf = 0; cf < resDS.ResourceCustomFields.Count; cf++)
       7:                  {
       8:                      if (resDS.ResourceCustomFields[cf].MD_PROP_UID == cfUID)
       9:                      {
      10:                          resDS.ResourceCustomFields[cf].FLAG_VALUE = flagValue;
      11:                          return UpdateOneResource(resDS);
      12:                      }
      13:                  }
      14:              }
      15:              else
      16:              {
      17:                  resDS.ResourceCustomFields.AddResourceCustomFieldsRow(AddResourceCustomField(resDS.Resources[0].RES_UID, cfUID, flagValue, resDS));
      18:                  return UpdateOneResource(resDS);
      19:              }   
      21:              return false;
      22:          }


  • Christophe Fiessinger's Blog

    Project Server Virtualization

    A hot IT trend these days is running software applications on virtual environment, but before deploying EPM on a virtual environment please review the following KB article published on the subject:

  • Christophe Fiessinger's Blog

    How to extract and view Timesheet Audit Data?

    In EPM 2007 you have the ability to audit all timesheet transactions using the following flag:

    Server Settings >Time and Task Management > Settings and Defaults > Enable Timesheet Auditing

    Once audit has been activated, all timesheet changes get recorded in the following table in the Published database: MSP_TIMESHEET_ACTUAL_AUDIT

    Since the data is not in the Reporting db, I’ve created a query that uses data from both the Published and Reporting DB to extract and view timesheet audit data:


         DECLARE @CMD NVARCHAR(4000)

         SET @DBNAME = REPLACE(DB_NAME(),'_Reporting','')


         SET @CMD=

         'SELECT       TSR.ResourceName AS ''Resource'',

                      TS_ACT_AUD_SEQUENCE ''Audit Sequence'',

                      TaskName AS ''Task'',

                      CONVERT(Decimal(9,2), TS_ACT_AUD_DELTA_VALUE/60000) AS ''Delta Value'',

                      TS_ACT_START_DATE ''Start'',

                      TS_ACT_FINISH_DATE ''Finish'',

                      MOD_DATE ''Modified'',

                      TSS.Description AS Status



         INNER JOIN MSP_TimesheetLine AS TSL

         ON TSAA.TS_LINE_UID=TSL.TimesheetLineUID

         INNER JOIN MSP_TimesheetTask AS TST

         ON TST.TaskNameUID=TSL.TaskNameUID

         INNER JOIN MSP_Timesheet AS TS

         ON TS.TimesheetUID=TSL.TimesheetUID

         INNER JOIN MSP_TimesheetResource AS TSR

         ON TSR.ResourceNameUID=TS.OwnerResourceNameUID

         INNER JOIN MSP_TimesheetStatus AS TSS

         ON TSS.TimesheetStatusID=TS.TimesheetStatusID



         EXEC (@CMD)


    A sample SQL Server Reporting Services report looks like this (RDL attached below):

    Timesheet Audit Data


    To enforce the EPM security model I recommend you write a PSI extension.

  • Christophe Fiessinger's Blog

    See You All at the Microsoft Office Project Conference!

    The Project Conference in Seattle is three weeks away.

    I’m sure you have all registered! If not do so since we have great content lined up

    I’ll also be in Madrid for the EMEA Project Conference in December.

    Safe Travel.

Page 1 of 2 (14 items) 12