Author: Farhad Naderipour

Farhad Naderipour, based in Tehran, Iran, works as the IT manager for Fanamoj Co. There he has established a project management office in order to train and help R&D project managers prepare monthly reports of the projects, deployed Project Server 2007 in the organization, analyzed and implemented a document management system using Project Server 2007 and SharePoint 2007, and established security using Microsoft Right Management Server. He's the author of A Guide for Delphi Programmers published by Naghoos Press in Persian. Farhad has a B.S. in industrial engineering from Islamic Azad University. Contact him at f_naderipour@yahoo.com.

Lock Down Microsoft Project Progress Data

An advantage of using Microsoft Project Server 2007 is the ability to have people involved in the work update the progress of the project using My Tasks and My Timesheets pages. The My Timesheets page allows project team members to enter their actual work data on each assignment and submit it to their direct or timesheet manager at the end of period — such as every week. The My Tasks page enables team members to update task status in order to help project managers keep track of project progress. Figure 1 showshow these functions work. Figure 1. The process for using the My Tasks and My Timesheets pages. After the user updates the remaining work, actual work (which can be imported from the timesheet), and percent of work complete, Microsoft Project and Project Server recalculate other fields based on embedded formulas. Each time one side of the equation changes, other elements of the formula will be adjusted to maintain the equation. For example, the percent of work completed and total work of each task calculates as: Assume that a user submits 30 hours of actual work for an assignment with 100 hours of remaining work. The program will calculate total work and percent of work completed like this: Then let’s say the user turns to the assignment details page accessible from both the My Tasks page and My Timesheets page and updates the percent work complete field to 60 percent. Consequently, the program will recalculate and adjust actual work and total work to maintain the equation; the original actual work submitted by the assignment owner or user will be changed and overridden. In my company team members have to report actual time and remaining work for the assignments. The “assignment detail” page provides percent work complete and actual work (depending on the server setting), information that’s accessible by all users. Users are supposed to enter the actual work data in their time-sheets (for their direct managers) and update the assignment status (for the project manager). We have found that the actual work data submitted by users is valuable for our project managers. But having the percent work complete field exposed increases the chance of deliberate, accidental, or hasty changes in this field’s data. Besides, the financial department carries out payment after timesheets are approved and processed. To prevent variances from appearing between the data maintained by the project managers and the financial department, which causes confusion and delays, my company has decided to prevent users from changing the percent of work completed field. Users can only update the remaining work, task health, actual start, actual finish, and notes field to inform project managers of task progress. Fully completed tasks are those with zero work remaining and a “Completed” task health. To disable the percent of work completed field for users to maintain the “purity” of the actual work data and keep it unchangeable by team members, here’s the procedure. First, find “details.aspx,” the page denoting where Project Server is installed. In a default installation this page can be found in following path: C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12TEMPLATELAYOUTSPWASTATUSING Second, using Notepad or some other text editing program, find the “PWA:TextConvBox” tag and add the “enabled = false” attribute. Figure2 shows the details page after applying this custom attribute. Figure2 The Detals.aspx page after modifying it. Third, save your changes and look at your assignment details page. As shown in Figure 3, the field becomes disabled for users. Figure 3. The Assignment details page after saving the changes. Now, your project managers will know that what has been reported by team members communicates the original actual work. And the financial department will be in complete agreement with them. Farhad Naderipour, based in Tehran, Iran, works as the IT manager for Fanamoj Co. There he has established a project management office in order to train and help R&D project managers prepare monthly reports of the projects, deployed Project Server 2007 in the organization, analyzed and implemented a document management system using Project Server 2007 and SharePoint 2007, and established security using Microsoft Right Management Server. He’s the author of A Guide for Delphi Programmers published by Naghoos Press in Persian. Farhad has a B.S. in industrial engineering from Islamic Azad University. Contact him at f_naderipour@yahoo.com.

Project Programming: Integrating Project Server’s Timesheet with an Access Control System

Sometimes to get what you want out of Microsoft Project Server, you have no choice but to turn to programming. In this article I describe how we handled a timekeeping-related challenge at my company by tapping into Project Server’s ability to work with event handlers. First, some basics. An event handler is simply a programming routine that handles input from an event. In the case of Project Server, you can use server-side events — a mechanism for developers to extend Project Server by adding new business logic. Project Server raises events whenever particular data changes on the server. If you associate an event handler with an event, then the handler is executed when that particular event occurs. There are two types of events: pre-events and post-events. Pre-events are raised before data is saved to the database. These events can be canceled by event handlers. Post-events can’t be canceled; they’re raised after the data changes have been saved to the database. Now for some background. I work for a high tech company in the telecommunications industry. Fanamoj has almost 200 employees, about three-quarters of which are working on research and development projects. In order to be able to track project costs, our financial department has defined cost centers for each project. Traditionally, staff filled out and submitted an Excel worksheet as a timesheet that included their daily arrival and departure times and also the time spent on working for each cost center (or project). For instance, on February 22 I filled-out that I entered the building at 08:30, left at 18:00 and spent four hours working on cost center code 457 and the rest of my time on cost center code 127. At the end of the period (we define monthly periods for submitting timesheets), employees printed out their Excel sheets and submitted it to their direct manager to be signed and sent to the human resources and financial departments. Moving from Cost Centers to Tasks However, our manager wanted to track time on a task basis — not just by project — a challenge he gave to the IT department to figure out. We recognized that there were considerable time costs related to filling out timesheets and having the HR staff transfer the data into their system and prepare reports for the financial department. Cutting down such costs could have great repercussions on total overhead expense. We also knew that our access control system — a time recorder machine — was used only to open and close the door of main entrances, and its data had no effect elsewhere. Last, the legacy timesheet system was based on honesty; however, a few members of the staff sometimes misused this trust and damaged the culture. So, we decided to introduce a new timesheet management system. At first, we figured we could build something from scratch. Initially, monthly payments to staff are based on their timesheets and can’t exceed their attendance time. In other words, a functional manager who spends 45 hours for project A and 140 hours for project B has to submit a timesheet with 185 total hours at the end of the period. This can’t be greater than his or her total attendance time. For the sake of convenience our top management accepted one hour for timesheet tolerance; if total attendance time were 185 hours, the employee could submit any timesheet up to 186 hours. Ultimately, building a new timesheet management program from scratch with the same functionality as the existing system was estimated to be too costly. To comply with the various rules, we decided to manipulate Microsoft Project Server timesheet functionality and integrate it with the time recording device. Therefore, we manipulated the “Save” and “Save & Submit” operations, which are key aspects of the Project Web Access (PWA) timesheet features. Manipulating the OnUpdated Event Figure 1 shows the process for the On_Updated event handler, the key to the process. Figure 1. The On_Updated event handler. To handle the timesheet save event, we created “FanamojTimesheetEventHandler.dll” which overrides the OnUpdated method. Afterwards, we configured operational polices of the server (the server-side event handler configuration) to enable the server to run our custom code after users save their timesheets. The following steps will allow you to sample the On Update event: Step 1. Create new class library project using Microsoft Visual Studio 2008. Step 2. Reference the namespaces below at the top of your code: using Microsoft.Office.Project.Server.Library; using Microsoft.Office.Project.Server.Events; using System.Diagnostics; //To log events using System.Web.Services; //To connect, use PSI web services. using System.Data.OleDb; //To connect your event handler to your //time recorder database. using System.Data; using System.Data.SqlClient; Step 3. Modify the OnUpdated method of your class library as follows: namespace YouCompanyNameTimesheetHandler { public class Main : TimesheetEventReceiver { public override void OnUpdated(PSContextInfo contextInfo, TimesheetPostEventArgs e) { const string Project_Server_URI = “https://Your Project Server Web Application/PWA/”; const string Timesheet_Service_Path = “_vti_bin/PSI/Timesheet.asmx”; const string Resource_Service_Path = “_vti_bin/PSI/Resource.asmx”; const string Admin_Service_Path = “_vti_bin/PSI/Admin.asmx”; //Preparation for using Timesheet.asmx to read timesheet data WebSrvTimesheet.TimeSheet MyTimesheetSrv = new WebSrvTimesheet.TimeSheet(); MyTimesheetSrv.UseDefaultCredentials = true; MyTimesheetSrv.Url = Project_Server_URI + Timesheet_Service_Path; //Preparation for using Resource.asmx to read Resource data WebSrvResource.ResourceDataSet MyResourceDS; WebSrvResource.Resource MyResourceSrv = new WebSrvResource.Resource(); MyResourceSrv.UseDefaultCredentials = true; MyResourceSrv.Url = Project_Server_URI + Resource_Service_Path; MyResourceDS = MyResourceSrv.ReadResource(contextInfo.UserGuid); //Preparation for using Admin.asmx to read timesheet period data WebSrvAdmin.Admin MyAdminSrv = new WebSrvAdmin.Admin(); MyAdminSrv.UseDefaultCredentials = true; MyAdminSrv.Url = Project_Server_URI + Admin_Service_Path; WebSrvAdmin.TimePeriodDataSet MyTimePeriodDS; MyTimePeriodDS = MyAdminSrv.ReadPeriods(WebSrvAdmin.PeriodState.All); EventLog TimesheetLog = new EventLog(); TimesheetLog.Source = “Fanamoj Timesheet”; int EventID = 3652; string LogEntry; decimal TotalValue; string Username = MyResourceDS.Resources[0].RES_EXTERNAL_ID; //Resource External ID is resource identification card code // which is different //from employee ID. Guid CTSGuid = e.TsUID; //Gets current timesheet UID Guid CurrentWRPD_ID; DateTime PeriodStartDate = System.DateTime.Now; DateTime PeriodFinishDate = System.DateTime.Now; //Reads period UID of current timesheet CurrentWRPD_ID = MyTimesheetSrv.ReadTimesheet(CTSGuid).Headers[0].WPRD_UID;   //Reads period start and finish dates for the current timesheet period. for (int i = 0; i < MyTimePeriodDS.TimePeriods.Count; i++) { if (MyTimePeriodDS.TimePeriods[i].WPRD_UID == CurrentWRPD_ID) { PeriodStartDate = MyTimePeriodDS.TimePeriods[i].WPRD_START_DATE; PeriodFinishDate = MyTimePeriodDS.TimePeriods[i].WPRD_FINISH_DATE; } } //Reads current timesheet total actual work reported by user. TotalValue = MyTimesheetSrv.ReadTimesheet(CTSGuid). Headers[0].TS_TOTAL_ACT_VALUE; //Represents timesheet total actual work in hours. decimal Timesheet_Total = TotalValue / 60000; decimal Time_Card_Total; //Here, you need to connect to your time recording system database and set //Time_Card_Total (Defined above) to total attendance time of current user. //Remember, we have resource external ID and period start and finish dates //to query this data. //Now, Prepare your handler to show timesheet total data and //attendance system //data to current user. string TS_Comment_Text; TS_Comment_Text = “Time Recording System Total: ” + ” ” + Math.Round(Time_Card_Total, 1).ToString() + ” \n” + “Timesheet Total:” + ” ” + Math.Round(Timesheet_Total, 1).ToString() + ” \n”; if (Timesheet_Total > (Time_Card_Total + 1)) //User has entered more //than his/her // attendance time { TS_Comment_Text = TS_Comment_Text + “Since Time Recording System total is less than Timesheet total your timesheet can’t be sent to your manager.”; } //Updates command text of current timesheet. string Connection_String = “data source=Your PWA Database Server;Initial catalog=ProjectServer_Published;User Id=user;Password=password;”; string Update_SQL_String; SqlCommand Comment_Update_Command = new SqlCommand(); SqlConnection Cnn = new SqlConnection(Connection_String); Cnn.Open(); Update_SQL_String = “Update dbo.MSP_TIMESHEETS”; Update_SQL_String = Update_SQL_String + ” Set”; Update_SQL_String = Update_SQL_String + ” TS_COMMENTS = ‘” + TS_Comment_Text + “‘”; Update_SQL_String = Update_SQL_String + ” Where”; Update_SQL_String = Update_SQL_String + ” (TS_UID = ‘” + e.TsUID.ToString() + “‘)”; Comment_Update_Command.Connection = Cnn; Comment_Update_Command.CommandText = Update_SQL_String; Comment_Update_Command.ExecuteNonQuery(); } //Logs current activities to windows events. LogEntry = “User: ” + Username + “\n Total Submitted Time: ” + TotalValue + “\n Start Time = ” + PeriodStartDate.Date.ToString() + “\n Finish Time = ” + PeriodFinishDate.Date.ToString() + “\n TimeCard = ” + ddd; TimesheetLog.WriteEntry(LogEntry, EventLogEntryType.Information,EventID); } Step 4. Build your solution, add it to global assembly cache (GAC), and open the server side event handler configuration in your PWA Server Settings. Step 5. Select timesheet updated event from the event list and set the properties of your assembly name. Figure 2. The user alert that shows up in the event of a conflict. As Figure 2 shows, the comment field of the timesheet will be updated after each save. However, if the timesheet total exceeds the time recording system total + 1, the handler will alert users with the note, “Since Time Recording System total is less than Timesheet total, your timesheet may not be sent to your manager.” The alert also lists the hours in conflict. Unfortunately, Project Server 2007 doesn’t offer any web service to update the timesheet’s comment. So, we had to update the TS_COMMENTS field of the dbo.TIMESHEETS table in the Projectserver_Published database using SQL commands. Manipulating the OnSubmitting Event To prevent users from submitting their timesheets while there are nonconformities with the time recorder machine, we put a bunch of code in the OnSubmitting method of our handler to override the submitting action in Project Server. The sequences of events are shown in Figure 3. Figure 3. Where the comparison of submitted time is compared to the number shown from the time recording machine. Step 6. To complete your event handler project, you just need to add an OnSubmitting operation after the OnUpdated operation. public override void OnSubmitting(PSContextInfo contextInfo, TimesheetPreSubmitEventArgs e) Step 7. Then include the following code to override the default PWA submit event. public override void OnSubmitting(PSContextInfo contextInfo, TimesheetPreSubmitEventArgs e) { const string Project_Server_URI = “https://Your Project Server Web Application/PWA/”; const string Timesheet_Service_Path = “_vti_bin/PSI/Timesheet.asmx”; const string Resource_Service_Path = “_vti_bin/PSI/Resource.asmx”; const string Admin_Service_Path = “_vti_bin/PSI/Admin.asmx”; //Preparation for using Timesheet.asmx to read timesheet data WebSrvTimesheet.TimeSheet MyTimesheetSrv = new WebSrvTimesheet.TimeSheet(); MyTimesheetSrv.UseDefaultCredentials = true; MyTimesheetSrv.Url = Project_Server_URI + Timesheet_Service_Path; //Preparation for using Resource.asmx to read Resource data WebSrvResource.ResourceDataSet MyResourceDS; WebSrvResource.Resource MyResourceSrv = new WebSrvResource.Resource(); MyResourceSrv.UseDefaultCredentials = true; MyResourceSrv.Url = Project_Server_URI + Resource_Service_Path; MyResourceDS = MyResourceSrv.ReadResource(contextInfo.UserGuid); //Preparation for using Admin.asmx to read timesheet period data WebSrvAdmin.Admin MyAdminSrv = new WebSrvAdmin.Admin(); MyAdminSrv.UseDefaultCredentials = true; MyAdminSrv.Url = Project_Server_URI + Admin_Service_Path; WebSrvAdmin.TimePeriodDataSet MyTimePeriodDS; MyTimePeriodDS = MyAdminSrv.ReadPeriods(WebSrvAdmin.PeriodState.All); EventLog TimesheetLog = new EventLog(); TimesheetLog.Source = “Fanamoj Timesheet”; int EventID = 3652; string LogEntry; decimal TotalValue; string Username = MyResourceDS.Resources[0].RES_EXTERNAL_ID; //Resource External ID is resource identification card code which is //different from employee ID. Guid CTSGuid = e.TsUID; //Gets current timesheet UID Guid CurrentWRPD_ID; DateTime PeriodStartDate = System.DateTime.Now; DateTime PeriodFinishDate = System.DateTime.Now; //Reads period UID of current timesheet CurrentWRPD_ID = MyTimesheetSrv.ReadTimesheet(CTSGuid).Headers[0].WPRD_UID; //Reads period start and finish dates for the current timesheet period. for (int i = 0; i < MyTimePeriodDS.TimePeriods.Count; i++) { if (MyTimePeriodDS.TimePeriods[i].WPRD_UID == CurrentWRPD_ID) { PeriodStartDate = MyTimePeriodDS.TimePeriods[i].WPRD_START_DATE; PeriodFinishDate = MyTimePeriodDS.TimePeriods[i].WPRD_FINISH_DATE; } } //Reads current timesheet total actual work reported by user. TotalValue = MyTimesheetSrv.ReadTimesheet(CTSGuid).Headers[0]. TS_TOTAL_ACT_VALUE; //Represents timesheet total actual work in hours. decimal Timesheet_Total = TotalValue / 60000; decimal Time_Card_Total; //Here, you need to connect to your time recording system database and //set Time_Card_Total (Defined above) to total attendance time of //current user. // Remember, we have resource external ID and period start and finish dates //to query this data. if (Timesheet_Total > (Time_Card_Total + 1)) //User has entered more than //his/her attendance time { e.Cancel = true; //*** Cancels the submitting operation and notifies user. *** } Gaining Greater Accuracy with a Bit of Code Attendance and access control systems are widely used in business. So it’s useful to be able to make actual work submitted on projects more accurate by checking the database to which these devices record before those hours are submitted to the timesheet manager. But the first requirement is to install Microsoft Project Server 2007!