Professional Services - Extracting Estimate At Completion (EAC) via CDS views

[MartyMcCormick]

In professional services projects, there is a calculated field called Estimate at Completion that is often asked for by customers for extraction to another system.

You can see this value on the Review Customer Projects app at the header level and for each work package / resource type. If the project actual costs exceed the originally planned costs, then the estimate at completion will be greater.

As of the current S/4HC release (2002), there are no APIs or CDS views that contains EAC as a stored value for easy extraction. The value is dynamically calculated on the fly by the apps/reports that use it in the system and thus if you want the value outside of S/4HC you need to perform this calculation programmatically as well.

However ,extracting this data is tricky but this blog will provide some further insight. The first step is identifying our source of data. I_EngmntProjFcstCube_2 contains this info. However, because this CDS view has no key fields we cannot use it on its own. Therefore, we create an association to I_EngagementProject and set up our CDS view as follows:

Once the association is set up, I will only add the fields that I need, mainly from cost amounts, work package and resource type the I_EngmntProjFcstCube_2 data source.

Because we want EAC values, we need to ensure we have SUM aggregates on the costs.

After publishing the CDS view, we add to a custom communication scenario and publish via communication arrangement. Now we can interact with the data from our 3rd party tool, for example MS Excel, Postman, SAP Cloud Platform Integration, etc.

This is where some tricks can be applied using OData query values to get the appropriate amounts.

The way OData $filter works is when you request a field with a SUM aggregate, it will apply it to the values to the lowest level in the selection criteria accordingly. That is to say, if you request the costs at the project, work package and resource level the corresponding resultset will contain records with costs at project resource level because that is the lowest level. So if you had 5 work packages, each with 2 resources you would get 10 records. If you request only projects and costs, then you will get one record of the costs at project level.

It should be noted that the CDS view has a mandatory parameter for FiscalYearPeriod (YYYYMMM), so you need to take this into account.

Let's take a look at some examples.

To get the costs for a project:

/sap/opu/odata/sap/YY1_INCCOMENGPROJ_CDS/YY1_IncComEngProj(P_FiscalYearPeriod='2019012')/Results?$select=PlndCostAmt,ActlCostAmt,ActualPlanDeviationCost&$filter=EngagementProject eq 'MMJL00011'

These results match the project in S/4HC for Planned and Actuals, but EAC is dynamically calculated and not in our resultset.

If we add work package or resource level (sub-work package) to the query, we will get the total amounts at the field level for each work package. For example, if we wanted to calculate EAC at the work package level, we add work package to the query and now get the amounts at the work package level:

sap/opu/odata/sap/YY1_INCCOMENGPROJ_CDS/YY1_IncComEngProj(P_FiscalYearPeriod='2019012')/Results?$select=PlndCostAmt,ActlCostAmt,ActualPlanDeviationCost,WorkPackage&$filter=EngagementProject eq 'MMJL00011'

In order to calculate the EAC by using the 3 values above. In the pseudo logic below, we can keep a running total for Actual Costs as we iterate the work packages. Basically, if actual costs are greater than the planned + deviation, use this amount, otherwise take the planned and deviation costs.

IF (Actual Cost > (Planned Cost + Actual Plan Deviation Cost))

ActualCostTotal = ActualCostTotal + Actual Cost

ELSE

ActualCostTtoal = ActualCostTotal + Planned Costs + Deviation Costs

You can implement this logic in your code (for example, on SAP Cloud Platform Integration (CPI) or as an example, I imported the above query and data to Microsoft Excel and performed the calculations n column E:

Here you can see the EAC of $84,421.60 matches what S/4HC calculated on the Review Projects app shown previously.

I hope you found this blog useful, please let me know if you have any questions.

Thanks,
Marty