Calculate total Estimate at Completion for all Projects in a Program

[Mickey_Cegon]

On a Program record, calculate the total forecast_cost of all Projects on a Program. I'd like to know if there is already this functionality available to expose the data needed. If not, would like assistance in understanding the best method to gather and expose the data on the Program record.

Example:

1. Navigate to the Program record.

2. Add the column "Estimate at Completion" on the Related Records - Projects tab.

3. Add the Total using the List Calculations.

4. On the Program form, add a new field "Estimate at Completion" to the Financials tab.

5. Calculate the sum of EAC on all attached Projects on the Program, and populate the new field with the data.

6. Compare the data from the new field to the Related List of projects, and verify it is the same. 

7. Add/Remove a Project from the Program, and verify the new EAC field total is updated to match the new calculation.

8. Use the new field on reports and dashboards.

Any suggestions would be greatly appreciated.

Mickey Cegon

[Sunil B N · Accepted solution]

Hi Mickey,
As of now, EAC and ETC fields are not available at Program level OOTB.
Nevertheless as you suggested, few fields can be added and they can aggregate from all the projects associated to the program.
You can further split EAC into OpEx and CapEx too.

Fields at Project level to consider - for aggregate.

forecast_cost = Estimate at completion
capex_forecast_cost = Capital estimate at completion
opex_forecast_cost = Operating estimate at completion
estimate_to_completion = Estimate to completion
capex_estimate_to_completion = Capital estimate to completion
opex_estimate_to_completion = Operating estimate to completion

Recalculation can be monthly scheduled job. As this doesn't change within one fiscal month.
Please be assured that we will consider this feedback for the future product roadmap.
Best Regards,
Sunil B N

[Mickey_Cegon]

Thanks! This is exactly the information I was looking for!

Mickey Cegon

[Mickey_Cegon]

I have a question on the script that I am planning on using to populate this new field with the totals from the Program's projects. I have the totals available via the script, but when I populate the new field, it is not adding in the commas in the currency. Can you assist with this script, so I can get the proper currency formatting?

updateProgEAC();
function updateProgEAC(){
	var currencyService = new SNC.FMCurrency();
	var functional_currency = currencyService.getFunctionalCurrency();
	var prog = new GlideRecord('pm_program');
	prog.addQuery('sys_id', 'b2999989dbf9fb405de642fa0b96194a');
	prog.addActiveQuery();
	prog.query();
	
	while(prog.next()){
		//gs.print('Here is a program: '+prog.short_description);
		
		// find all open projects on the program
		
		var progSysID = prog.sys_id;
		var totalEAC = new GlideAggregate('pm_project');
		totalEAC.addQuery('primary_program', progSysID);
		totalEAC.addAggregate('SUM', 'forecast_cost');
		totalEAC.groupBy('primary_program');
		totalEAC.query();
		
		if (totalEAC.next()) {
			
			gs.print('Here is the total EAC from all projects: '+ parseFloat(totalEAC.getAggregate('SUM', 'forecast_cost')));(totalEAC.getAggregate('SUM', 'forecast_cost'));
			
			prog.u_forecast_cost = functional_currency +";"+ (parseFloat(totalEAC.getAggregate('SUM', 'forecast_cost')));
			
		} else {
			gs.print('none found');
			prog.u_forecast_cost = 0;
		}
		prog.update();
	}
}

Results are correct totals, but the total in the currency field looks like this: 2307627.09. Should be 2,307,627.09.

Any help would be appreciated!

Mickey

[Sunil B N]

Hi Mickey,
Don't worry about what it prints..
Display of number depends upon the user's locale. Once you set and see the form, it will appear accordingly with commas.
Best Regards,
Sunil B N