Skip to Content
Product Information
Author's profile photo Marty McCormick

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

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.

s4costs.JPG

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:

cds1.JPG

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.

CDS2.JPG

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

CDS3.JPG

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’

ProjectCosts.JPG

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

ProjectCostss43.JPG

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’

WPPostman.JPG

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:

EACExcel.JPG

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

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sandeep Kumar
      Sandeep Kumar

      Marty McCormick Great blog and really useful for PS customers!

      Author's profile photo Feras Al-Basha
      Feras Al-Basha

      This is really helpful and valuable. Thank Marty McCormick

      Author's profile photo Corey Yu
      Corey Yu

      Hi Marty McCormick

      Thanks a lot for sharing !!

      Best regards,

      Corey

      Author's profile photo Eric Yu
      Eric Yu

      Thanks Marty for sharing this useful information.

      Author's profile photo Priyank Kumar Jain
      Priyank Kumar Jain

      Good one Marty...

      Author's profile photo Venkata Lakshmi Narasinga Rao Srirangam
      Venkata Lakshmi Narasinga Rao Srirangam

      Very Useful