The following post title strike to me on TechNet forums few days back:
And here I decided to write a blog about the solution i chose some time back last year when I was involved in an EPM implementation project for one of the leading Bank in Middle East.
Once the project data is saved in the dataset, you can now evaluate each milestone in the project to check for any variance in the cost values:
sEmailStart.Append("<html><body>");
sEmailStart.Append("Dear Finance,<br> CC: PMO<br><br>");
sEmailStart.Append("Regards, <br> EPM Team <br><br><br> Note: This is an auto-generated email, please don't respond");
sEmailStart.Append("</body></html>");
Your email might look like this in the end:
//string sConnString = string.Empty;
StringBuilder sEmailStart = new StringBuilder();
int iSMTPPort = 0;
int iMilestoneCount = 0;
XmlTextReader reader = new XmlTextReader(@"C:\EPMConfigurations\Config.xml");
Hashtable configurations = new Hashtable();
while (reader.Read())
{
string lastKey = String.Empty;
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name.ToLower() == "add")
{
configurations.Add(reader[0], reader[1]);
}
}
}
reader.Close();
NotificationEventHandler.Project.Project p = new NotificationEventHandler.Project.Project();
p.Url = configurations["ProjectServerURL"].ToString() + configurations["PROJECT_WEBSERVICE"].ToString();
p.Credentials = CredentialCache.DefaultCredentials;
NotificationEventHandler.Project.ProjectDataSet ds = p.ReadProject(e.ProjectGuid, NotificationEventHandler.Project.DataStoreEnum.WorkingStore);
//ds.b
NotificationEventHandler.Project.ProjectDataSet.TaskRow[] drs = (NotificationEventHandler.Project.ProjectDataSet.TaskRow[])ds.Task.Select(ds.Task.TASK_IS_MILESTONEColumn.ColumnName + " = 1");
//sEmailStart = ds.Project[0].PROJ_NAME + Environment.NewLine + Environment.NewLine + "Below are the latest Milestone Tasks Cost changes occur:" + Environment.NewLine;
sEmailStart.Append("<html><body>");
sEmailStart.Append("Dear Finance,<br> CC: PMO<br><br>");
sEmailStart.Append(string.Format("Project Name: {0} has been modified. Please review the Project Milestones as below:<br><br>", ds.Project[0].PROJ_NAME));
sEmailStart.Append("<table border=2 cellpadding=2 cellspacing=2 width=100%><tr><td>Name</td><td>Baseline Start</td><td>Actual Start</td><td>Baseline Finish</td><td>Actual Finish</td><td>Baseline Cost</td><td>Cost</td><td>%Complete</td></tr>");
//check if there are any milestones in the project,if not simply return
//check if milestone cost changed, if not skip for this and check the other row
iTaskCostVar = (dr.IsTASK_COST_VARNull() ? 0 : Convert.ToInt32((dr.TASK_COST_VAR)));
if (iTaskCostVar == 0)
continue;
sBaselineStart = (dr.IsTB_STARTNull() ? "0" : (dr.TB_START).ToString());
sActualStart = (dr.IsTASK_ACT_STARTNull() ? "0" : (dr.TASK_ACT_START).ToString());
sBaselineFinish = (dr.IsTB_FINISHNull() ? "0" : (dr.TB_FINISH).ToString());
sActualFinish = (dr.IsTASK_ACT_FINISHNull() ? "0" : (dr.TASK_ACT_FINISH).ToString());
sBaselineCost = (dr.IsTB_COSTNull() ? "0" : (dr.TB_COST/100).ToString());
sCost = (dr.IsTASK_COSTNull() ? "0" : (dr.TASK_COST).ToString());
sPctComplete = (dr.IsTASK_PCT_COMPNull() ? "0" : (dr.TASK_PCT_COMP).ToString());
sEmailStart.Append(string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>", dr.TASK_NAME, sBaselineStart, sActualStart, sBaselineFinish, sBaselineFinish, sBaselineCost ,sCost, sPctComplete));
iMilestoneCount = iMilestoneCount + 1;
}
if (iMilestoneCount == 0)
return;
//sEmailStart = sEmailStart + Task_Cost_Var;
sEmailStart.Append("<br>Please review them by following the link: ");
sEmailStart.Append(configurations["ProjectServerURL"].ToString() + "/project.aspx" + "<br><br><br>");
sEmailStart.Append("Regards, <br> EPM Team <br><br><br> Note: This is an auto-generated email, please don't respond");
sEmailStart.Append("</body></html>");
sEmailFrom = configurations["EmailFrom"].ToString();
sToFinance = configurations["ToFinance"].ToString();
sToPMO = configurations["ToPMO"].ToString();
sEmailSubject = configurations["EmailSubject"].ToString();
sSMTPServer = configurations["SMTPServer"].ToString();
iSMTPPort = Convert.ToInt32(configurations["SMTPPort"].ToString());
}
catch (Exception err)
{
string errorMessage = "Code crashed OnPublished Method" + err.Message + " " + err.Source + err.StackTrace;
el = new ErrorLog();
el.CreateFile();
el.WriteToFile(errorMessage);
}
______________________________________________________________________________
How to monitor and control the change in Project milestone tasks?
And here I decided to write a blog about the solution i chose some time back last year when I was involved in an EPM implementation project for one of the leading Bank in Middle East.
One of the not-OOB requirements of Project Management Office is about the monitor and control they look for over the projects based on the project milestones. The first quick thing you can propose is the Dashboard reports, where you can develop indicators based on the PMO criteria and they can reflect a bird eye view of the Project status at the milestone level. But think about the scenario where you have more than 50 big project schedules and each with over dozen of milestones, the dashboard solution would not really be welcomed if the program officer has to scroll down to all the milestones of each project to see which of the indicator is turned red due to the change in milestone cost/duration/ etc.
The solution i developed and accepted by the customer with smile was:
- Generate an email notification to the PMO,
- if any project milestone task estimation varies with the baseline estimation.
- This will intimate the PMO rite away about the change with Project name, Project Manager Name, and the exact changed milestone task within the project.
This could further compliment with the SSRS dashboard report, where then program officer can go and browse that particular project for details instead of scrolling to each and every project. Also the PMO don't have to view the reports every now and then, instead they can only access when they receive any change notification - this can also be called more productive at the PMO end.
The way I managed to do that is as follows:
- Project manager changes the project schedule and publish the project
- Trap the OnPublished event
- Check for the change at the milestone level. For instance compare the Project milestone cost value with the Project milestone baseline Cost value.
- If found any variation, create and send an email to the PMO or etc.
The solution however based on the baseline of the project, and also the permission to save the baseline should not be allowed to the Project manager. Because the variance can only be determined after the comparison of current and the baseline values, customizing the event also requires coding.
You need to set you IDE environment to access the Project web service methods. There is an excellent article available on MSDN about How to Create and Debug the project custom event handler, so i will not go in that detail. We will override the OnPublished event, the reason i prefer to use OnPublished is because its a post publish event triggers after completing the required publishing activities and wont effect the publishing in-case of any abnormal termination due to the exception in the custom code.
The next step you have to do is to define a Project Object, call its method ReadProject using project GUID and save the project data in a dataset.
Once the project data is saved in the dataset, you can now evaluate each milestone in the project to check for any variance in the cost values:
foreach (NotificationEventHandler.Project.ProjectDataSet.TaskRow dr in drs)
{
iTaskCostVar = (dr.IsTASK_COST_VARNull() ? 0 : Convert.ToInt32((dr.TASK_COST_VAR)));
.
.
.
}
}
If finds any difference, save the task name with all the other details required and draft an email based on that data. For instance:
_______________________________________________________________________________
sEmailStart.Append("<html><body>");
sEmailStart.Append("Dear Finance,<br> CC: PMO<br><br>");
sEmailStart.Append(string.Format("Project Name: {0} has been modified. Please review the Project Milestones as below:<br><br>", ds.Project[0].PROJ_NAME));
sEmailStart.Append("<table border=2 cellpadding=2 cellspacing=2 width=100%><tr><td>Name</td><td>Baseline Start</td><td>Actual Start</td><td>Baseline Finish</td><td>Actual Finish</td><td>Baseline Cost</td><td>Cost</td><td>%Complete</td></tr>");
sEmailStart.Append(string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>", dr.TASK_NAME, sBaselineStart, sActualStart, sBaselineFinish, sBaselineFinish, sBaselineCost ,sCost, sPctComplete));
sEmailStart.Append("<br>Please review them by following the link: ");
sEmailStart.Append(configurations["ProjectServerURL"].ToString() + "/project.aspx" + "<br><br><br>");
sEmailStart.Append("Regards, <br> EPM Team <br><br><br> Note: This is an auto-generated email, please don't respond");
sEmailStart.Append("</body></html>");
_________________________________________________________________________________
Once done with the development , add the library in the windows assembly and register the event handler with the appropriate Project server event. Follow the MSDN article i have mentioned above for debugging and deployment, all the steps are explained in detail in that article.
 |
| Add in windows assembly |
 |
| Register Project Server event handler |
And based on your requirement you can send this email through the code either to the pre-defined users (you can use web.config for this) or you can read any particular user's email on the fly from DB based on some defined criteria. Now if the project manager changes the milestone cost, our code will execute when he will publish the project after the change. And like the below project schedule, the change in milestone could be detected and the email will be generated to the PMO.
Your email might look like this in the end:
This email contains all the changed milestones with the exact change details, project center link to access the project other details and the effect this change propagates to the whole project schedule. Now imagine being a part of the PMO, you receive a notification on your outlook about the change in the project milestone by one of your Project Manager, you can quickly log on to your PWA, verify the change and its impact and can take the necessary steps. Doesn't it sound simple and quick? trust me it indeed is the quickest way for PMO to monitor the change in the project :)
As always any comments and suggestion are more than welcome.
Updated (26th Jan): The solution will work for Project Server 2007 as well as the PS 2010. Forgot to mention this earlier, adding it now after the valuable feedback of none other than Christophe Fiessinger.
Below is the complete code snippet of the solution for reference, but do note that the code attached has been written for the testing, and specific to the requirement, purposes only and attached as is. The code should be improved and revised, as per the requirements, prior to the deployment on the production server.
______________________________________________________________________________
public override void OnPublished(Microsoft.Office.Project.Server.Library.PSContextInfo contextInfo, Microsoft.Office.Project.Server.Events.ProjectPostPublishEventArgs e)
{
base.OnPublished(contextInfo, e);
try
{
string Task_Cost_Var = string.Empty;
string sEmailFrom = string.Empty;
string sToFinance = string.Empty;
string sToPMO = string.Empty;
string sEmailSubject = string.Empty;
string sSMTPServer = string.Empty;
string sBaselineStart = string.Empty;
string sActualStart = string.Empty;
string sBaselineFinish = string.Empty;
string sActualFinish = string.Empty;
string sBaselineCost = string.Empty;
string sCost = string.Empty;
string sPctComplete = string.Empty;
int iTaskCostVar;
public override void OnPublished(Microsoft.Office.Project.Server.Library.PSContextInfo contextInfo, Microsoft.Office.Project.Server.Events.ProjectPostPublishEventArgs e)
{
base.OnPublished(contextInfo, e);
try
{
string Task_Cost_Var = string.Empty;
string sEmailFrom = string.Empty;
string sToFinance = string.Empty;
string sToPMO = string.Empty;
string sEmailSubject = string.Empty;
string sSMTPServer = string.Empty;
string sBaselineStart = string.Empty;
string sActualStart = string.Empty;
string sBaselineFinish = string.Empty;
string sActualFinish = string.Empty;
string sBaselineCost = string.Empty;
string sCost = string.Empty;
string sPctComplete = string.Empty;
int iTaskCostVar;
//string sConnString = string.Empty;
StringBuilder sEmailStart = new StringBuilder();
int iSMTPPort = 0;
int iMilestoneCount = 0;
XmlTextReader reader = new XmlTextReader(@"C:\EPMConfigurations\Config.xml");
Hashtable configurations = new Hashtable();
while (reader.Read())
{
string lastKey = String.Empty;
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name.ToLower() == "add")
{
configurations.Add(reader[0], reader[1]);
}
}
}
reader.Close();
NotificationEventHandler.Project.Project p = new NotificationEventHandler.Project.Project();
p.Url = configurations["ProjectServerURL"].ToString() + configurations["PROJECT_WEBSERVICE"].ToString();
p.Credentials = CredentialCache.DefaultCredentials;
NotificationEventHandler.Project.ProjectDataSet ds = p.ReadProject(e.ProjectGuid, NotificationEventHandler.Project.DataStoreEnum.WorkingStore);
//ds.b
NotificationEventHandler.Project.ProjectDataSet.TaskRow[] drs = (NotificationEventHandler.Project.ProjectDataSet.TaskRow[])ds.Task.Select(ds.Task.TASK_IS_MILESTONEColumn.ColumnName + " = 1");
//sEmailStart = ds.Project[0].PROJ_NAME + Environment.NewLine + Environment.NewLine + "Below are the latest Milestone Tasks Cost changes occur:" + Environment.NewLine;
sEmailStart.Append("<html><body>");
sEmailStart.Append("Dear Finance,<br> CC: PMO<br><br>");
sEmailStart.Append(string.Format("Project Name: {0} has been modified. Please review the Project Milestones as below:<br><br>", ds.Project[0].PROJ_NAME));
sEmailStart.Append("<table border=2 cellpadding=2 cellspacing=2 width=100%><tr><td>Name</td><td>Baseline Start</td><td>Actual Start</td><td>Baseline Finish</td><td>Actual Finish</td><td>Baseline Cost</td><td>Cost</td><td>%Complete</td></tr>");
//check if there are any milestones in the project,if not simply return
if (drs.Length == 0)
return;
return;
foreach (NotificationEventHandler.Project.ProjectDataSet.TaskRow dr in drs)
{
{
//check if milestone cost changed, if not skip for this and check the other row
iTaskCostVar = (dr.IsTASK_COST_VARNull() ? 0 : Convert.ToInt32((dr.TASK_COST_VAR)));
if (iTaskCostVar == 0)
continue;
sBaselineStart = (dr.IsTB_STARTNull() ? "0" : (dr.TB_START).ToString());
sActualStart = (dr.IsTASK_ACT_STARTNull() ? "0" : (dr.TASK_ACT_START).ToString());
sBaselineFinish = (dr.IsTB_FINISHNull() ? "0" : (dr.TB_FINISH).ToString());
sActualFinish = (dr.IsTASK_ACT_FINISHNull() ? "0" : (dr.TASK_ACT_FINISH).ToString());
sBaselineCost = (dr.IsTB_COSTNull() ? "0" : (dr.TB_COST/100).ToString());
sCost = (dr.IsTASK_COSTNull() ? "0" : (dr.TASK_COST).ToString());
sPctComplete = (dr.IsTASK_PCT_COMPNull() ? "0" : (dr.TASK_PCT_COMP).ToString());
sEmailStart.Append(string.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td></tr>", dr.TASK_NAME, sBaselineStart, sActualStart, sBaselineFinish, sBaselineFinish, sBaselineCost ,sCost, sPctComplete));
iMilestoneCount = iMilestoneCount + 1;
}
sEmailStart.Append("</table>");
//check if there are any milestones appended in the string because of cost variance,if not just return
//check if there are any milestones appended in the string because of cost variance,if not just return
if (iMilestoneCount == 0)
return;
//sEmailStart = sEmailStart + Task_Cost_Var;
sEmailStart.Append("<br>Please review them by following the link: ");
sEmailStart.Append(configurations["ProjectServerURL"].ToString() + "/project.aspx" + "<br><br><br>");
sEmailStart.Append("Regards, <br> EPM Team <br><br><br> Note: This is an auto-generated email, please don't respond");
sEmailStart.Append("</body></html>");
sEmailFrom = configurations["EmailFrom"].ToString();
sToFinance = configurations["ToFinance"].ToString();
sToPMO = configurations["ToPMO"].ToString();
sEmailSubject = configurations["EmailSubject"].ToString();
sSMTPServer = configurations["SMTPServer"].ToString();
iSMTPPort = Convert.ToInt32(configurations["SMTPPort"].ToString());
MailMessage MilestoneEmail = new MailMessage(); //(sEmailFrom, sEmailTo,);
MailAddress MailFrom = new MailAddress(sEmailFrom);
MilestoneEmail.From = MailFrom;
MilestoneEmail.To.Add(sToFinance);
MilestoneEmail.CC.Add(sToPMO);
AlternateView htmlView = AlternateView.CreateAlternateViewFromString(sEmailStart.ToString(), null, "text/html");
MilestoneEmail.AlternateViews.Add(htmlView);
MilestoneEmail.IsBodyHtml = true;
MailAddress MailFrom = new MailAddress(sEmailFrom);
MilestoneEmail.From = MailFrom;
MilestoneEmail.To.Add(sToFinance);
MilestoneEmail.CC.Add(sToPMO);
AlternateView htmlView = AlternateView.CreateAlternateViewFromString(sEmailStart.ToString(), null, "text/html");
MilestoneEmail.AlternateViews.Add(htmlView);
MilestoneEmail.IsBodyHtml = true;
MilestoneEmail.Subject = sEmailSubject;
//MilestoneEmail.Body = sEmailStart;
//MilestoneEmail.Body = sEmailStart;
SmtpClient smtpClient = new SmtpClient(sSMTPServer, iSMTPPort);
smtpClient.Send(MilestoneEmail);
smtpClient.Send(MilestoneEmail);
}
catch (Exception err)
{
string errorMessage = "Code crashed OnPublished Method" + err.Message + " " + err.Source + err.StackTrace;
el = new ErrorLog();
el.CreateFile();
el.WriteToFile(errorMessage);
}
______________________________________________________________________________
