October, 2007

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);

            changeXml.AppendFormat("<Changes>");
            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));
                    changeXml.Append("</Assn></Proj>");
                }
            }
            changeXml.Append("</Changes>");

            return changeXml;
        }

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

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
            LoadSettings();

            // 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.

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

• How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases http://support.microsoft.com/default.aspx?scid=kb;en-us;943345&sd=rss&spid=11373
• Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint databases http://support.microsoft.com/kb/932744

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.

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"/>
<SoapAction>http://schemas.microsoft.com/office/project/server/webservices/Project/ReadProjectList</SoapAction>
 <ElementPath IgnoreNamespaces="true">ReadProjectListResponse/ReadProjectListResult/diffgram/ProjectDataSet/Project</ElementPath> 
</Query>

• 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.

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

  20:   

  21:              return false;

  22:          }

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:

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

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