Sum up values of MDF object on parent object

[Dharmin_Thakkar]

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

[xavierlegarrec]

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

jayakrishna.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

[Dharmin_Thakkar]

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

[Bertaq]

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 />

[S_Strangfeld]

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

[Bertaq]

Hi sylvia.strangfeld !

 

Thank you very much for your answer.

Unfortunately, using a variable gives the same options, or may be I miss something?

 

[heikeklews]

Hi @aaberezin

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

[17196]

Hi,

When we add multiple effective dated records, the total amount calculated is wrong. Is anyone encountered the same and has any solution?
dhthakkar2000_33 

[lukasolszar]

Hello ramasokan, I have the same issue which makes me sad. I hoped it will resolve my goal to sumup amounts from child object....

BR, Lukas

[S_Strangfeld]

Hi ramasokan 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

[17196]

Thanks sylvia.strangfeld

[haisamiqbal]

Hi Dharmin,

Excellent piece of information.

Just have a question, can we use new rule function ‘Sum Of Collection Field Values’ with a specific period of time as well?

For example, if I want to sum up the expenses incurred in the first half of the year. How can I achieve that?

Will appreciate if anyone can guide me in this regard.

Thanks.

[heikeklews]

Hi ramasokan

I have tested the issue successfully in release 2305 and assume that the issue has been resolved?

Kind regards, Heike

[heikeklews]

Hi haisamiqbal1982,

The prerequisites for using the rule function  ‘Sum Of Collection Field Values’  are:

• The objects from which you sum up the field values is an MDF object.


• The entries all belong to the same time slice of the parent object.

That is, whether you can sum up the values is very much dependent on your data model. If the expenses are captured on child objects which belong to the same time period it would work. For example, the parent time slice is from 01/01/2023 to 31/12/2023, and the expenses are captured on the children for 01/23, 02/23, etc. However, it does not work across time slices of the parent object because it is not possible to draw these time slices into the rule.

Kind regards, Heike

[Praveen_Kumar]

Hi @heike.klews, Did you test this successfully to sum up child objects with different effective dates? It's not working for me. Let me know if it's working. Thanks!

[antayy]

Hi @Dharmin_Thakkar @heikeklews 

Riding on an old topic. We would like to perform a count of the MDF object BenefitEmployeeClaim in the system based on a specific period range.  

We also checked out Cardinality() but it didnt seem to work as it is only counting based on the current MDF instance

Appreciate an expert advise how to achieve this.  Thank you. 

[heikeklews]

Hi @antayy,

Unfortunately, it is not possible to draw a collection of different time slices for an MDF object into a rule. The navigations within a rule (for example BenefitEmployeeClaim.BenefitEmployeeClaimDetail) always adhere to the effective date of the rule execution. Even those rule functions where you can pass a custom time period (instead of only one effective date) like the "Lookup" function, do not return a collection but only one record (even if there would be more records which meet the lookup criteria). 

The only option I can suggest for you is to submit an improvement request on influence.sap.com to enhance the "Lookup" function in a way that it can also return a collection of records instead of only one record.

Kind regards, Heike