Product Information
Sum up values of MDF object on parent object
Hello All,
Did you ever want to sum up the values of an MDF child object on the parent object and couldn’t find a way to do so?
If you answered yes – we have solutions for you with our latest 1H 2022 release.
We had quite a few customers asking for an option to calculate the sum of field values of a collection. This means, that customers had created MDF objects with a one-to-many composite child association and now wanted to sum up a field value of the child records.
We have introduced a new rule function called ‘Sum Of Collection Field Values’
This function sums up the values of a collection field that meet the conditions.
The pre-requisite is – a “parent-child” association between objects, a collection.
Function Parameters
The function has two parameters that need to be maintained.
The first parameter is to select the collection. Collections can be recognized via the “stack” icon on the user interface. The collection needs to be a Metadata Framework object.
The second parameter is to select the field in the collection that contains the value that you want to sum up.
Behaviour in case of errors:
If Collection is “null” or empty, the system returns the number 0.
If the field value of a data record in the collection is “null”, the system uses the number 0 for this data record when calculating the overall sum.
Use Case
You want to see the total amount of travel costs. You’ve created a custom parent object called “Reimbursement for Travel Costs” that has a child object called “Cost Types” that contains several child records, for example, for hotel and flight costs. You create a rule that uses the function Sum of Collection Field Values to get the overall amount of all travel costs on the parent object.
A special thanks to Sylvia Strangfeld and the team for introducing this feature in the SuccessFactors system
There are a few more rule functions introduced in 1H 2022 release. Click Here to know more about these rule functions.
Regards,
Dharmin
Hi Dharmin Thakkar
Thanks for sharing ! we often get asked in Compensation module implementations whether it is possible to pull an average of all salaries for same Pay Grade and Geozone in order to create an automatic Internal Compa-Ratio calculation with already loaded salary data in EC (for example : as an employee my external market ranges Compa Ratio is 97% but my internal Compa Ratio based on colleagues on same Pay Grade and GeoZone is 99%). It looks like it could be possible with this new feature.
We could create one "Salary" field in an MDF for each combination of Pay Grade and Geozone, then use Integration Center to populate Salary data from Comp Info into each MDF column (or create an initit + on save rule that will populate the columns correctly on mass upload) then divide each column by the number of non 0 values to get to this internal compa ratio.
The challenge with this design is that when a new employee gets hired all peer ratios need to be recalculated for all employees in the same pay grade and geozone.
Curious to know your thoughts.
Found an influence request on this (I've also had this requirement from 3 customers over the past 5 years) : https://influence.sap.com/sap/ino/#/idea/206835
Jaya Krishna Palakusi and myself worked on a draft to implement this "Peer Ratio" design back in 2020 for a major US customer and this is the mock up we came up with (the customer eventually decided to do the calculation in excel and load it directly into a compensation worksheet column through a lookup table).
Thank you !
Xavier
Hi Xavier,
Yes, it looks possible from this rule function. However, my concern here is the number of employees in that particular MDF object and its performance issue. However, we can try implementing this scenario in our demo system. You can connect with me on teams.
Regards,
Dharmin
Hi Dharmin Thakkar !
Is that somehow possible to use parameters for the "where" part of query? Now we can only choose exact value or null. But in that exact example I'd like to calculate sum for dependent chosen on the Benefit Claim screen and it seems like it's not possible. Or I'm doing something wrong?
Thank you!
<img />
Hi Alexander,
this is the expected behavior.
To fulfill your use case you should not do the calculation in the set-statement directly, but use a variable instead. There you will find the additional options to define the collection filter more granularly.
Hope this helps...
BR, Sylvia
Hi Sylvia Strangfeld !
Thank you very much for your answer.
Unfortunately, using a variable gives the same options, or may be I miss something?
Hi @Alexander Berezin
Sorry, but I just came across this question now. You probably have found a solution by now. I just wanted to add the answer nevertheless.
The variable should hold the information which you need in the where condition. In this example:
var_dependent = Benefit Employee Claim.<path to dependent>
In the set condition, you can use the variable in the collection filter:
Benefit Employee Claim.Entitlement Amount = Sum of Collection Field Values() ...
where Dependent is equal to var_dependent ...
Kind regards,
Heike
Hi,
When we add multiple effective dated records, the total amount calculated is wrong. Is anyone encountered the same and has any solution?
Dharmin Thakkar
Hello Ram Asokan, I have the same issue which makes me sad. I hoped it will resolve my goal to sumup amounts from child object....
BR, Lukas
Hi Ram Asokan hi Lukas Olszar , I was able to reproduce the issue in our internal landscape and it is (obviously) NOT the expected behavior of the rule function, but a bug.
Could you please open a customer ticket to the MDF component and state, that calculating the sum of children considers the wrong number of child entries. I would recommend to attach a rule trace to the case (trace mode: complete), as there the support and MDF dev colleagues can see, that instead of 2 the rule retrieves the same entries for the collection twice (4). (I attach my example here, so please check if this is the same in your case).
Thx a lot!
BR, Sylvia
Thanks Sylvia Strangfeld