How to parse actual costs by fiscal year

David78
Giga Contributor

‎08-31-2022 02:26 PM

We have a use case to parse out actual costs by fiscal year for multi-year projects.  We have been able to parse out planned costs by fiscal year from the cost plan, and budgets by fiscal year, but have been unable to parse out actual costs by fiscal year using the expense line data.  Can anyone help?

0 Helpfuls
  • 930 Views
5 REPLIES 5

Namita Mishra
ServiceNow Employee
ServiceNow Employee

‎08-31-2022 08:46 PM

Hi @David,

As per the plan, starting Tokyo release a new tab - Overview- will get added in the Financials. Using Overview tab, user can get the breakup of the following fields by fiscal year and further by quarters:

  • Budget
  • Planned Cost
  • Actual Cost
  • Variance (Planned vs Actual Cost)
  • Estimate At Completion
  • Estimate To Completion

Refer below screenshot.... 

 

find_real_file.png

Please mark this as Correct Answer, if this helped in answering the query.

Thanks,

Namita Mishra

Preview file
288 KB

David78
Giga Contributor

‎09-01-2022 05:04 AM

Thank you for the reply- that is great to hear.  Unfortunately our customer cannot deploy Tokyo for another year

 

Has anyone had success with scripting this use case? 

0 Helpfuls

David78
Giga Contributor
In response to David78

‎09-06-2022 01:58 PM

Bump- can anyone help here?

0 Helpfuls

Nootan Bhat
Kilo Sage
In response to David78

‎09-14-2022 11:55 PM

Hey David,

1) You can get the aggregated value of actual costs from cost_pla_breakdown table:

var f = new GlideAggregate('cost_plan_breakdown');
f.addEncodedQuery('task.numberSTARTSWITHPRJ0010276^breakdown_type=task');
f.groupBy('fiscal_period.fiscal_year');
f.addAggregate('SUM','actual');
f.query();
while(f.next()){
    gs.print(f.getDisplayValue('fiscal_period.fiscal_year')+" : "+f.getAggregate('SUM','actual'));
}

Or

2) If your actuals are not properly maintained in breakdown table,

You can have a fiscal period field created for expense line table and populate the value based on change in Date or Process Date of the expense line.

This way you can get the expense grouped by year in similar way as above.

Sample code to populate fiscal period value (Before BR)

var gdtProcessedStart = new GlideDateTime(current.process_date);
var gdtProcessedEnd = new GlideDateTime(current.process_date);
	
gdtProcessedStart.setDayOfMonthUTC(1);
gdtProcessedEnd.setDayOfMonthUTC(31);
	
var startDate = gdtProcessedStart.getDate().toString();
startDate = startDate.replace('-', '') + 'T000000';
var endDate = gdtProcessedEnd.getDate().toString();
endDate = endDate.replace('-', '') + 'T235959';
	
var grFiscalPeriod = new GlideRecord('fiscal_period');
grFiscalPeriod.addEncodedQuery('fiscal_type=month^start_date_time='+startDate+'^end_date_time='+endDate);
grFiscalPeriod.query();
	
if (grFiscalPeriod.next()) {
	current.u_fiscal_period = grFiscalPeriod.getUniqueValue();
}

 

3) Or user below script to get the sum by year:

var f = new GlideRecord('fm_expense_line');
f.addEncodedQuery('task=eff18e17dbd8001025c85a35dc961942');
f.query();
var sum = {};
while(f.next()){
    
	var grFiscalPeriod = new GlideRecord('fiscal_period');
	grFiscalPeriod.addEncodedQuery('fiscal_type=year^fiscal_start_date_time<'+new GlideDateTime(f.process_date)+'^fiscal_end_date_time>'+new GlideDateTime(f.process_date));
	grFiscalPeriod.query();
	
	if (grFiscalPeriod.next()) {
        gs.print(grFiscalPeriod.name)
		var period = grFiscalPeriod.name;
        sum[period] = (sum[period] || 0 ) + f.amount;
	}
    
}

gs.print(JSON.stringify(sum));
0 Helpfuls