Assets: Sum attribute of linked objects

Newbie user to the more advanced MDX functions and require assistance in the following.

Use case:
Assets object schema with 2 object types:

  • Budget (contains an attribute Budget Total $ which is type Float)
  • Expenditure (contains 2 attributes: Budget which is type Object and links to Budget; and Value which is type Float)

I have a report that displays the Budget objects in the rows; and successfully displays its Budget Total $ value. What I would like to do is also display the Spend to Date $; calculated by suming the Value attributes of all the linked Expenditure objects. I have only got as far returning the set of expenditure objects which I have been able to confirm using:

[Object.Expenditure Budget].[Budget].GetMemberByKey([Object].CurrentHierarchyMember.Get(‘DISPLAY_KEY’)).Children.Count

Would love some guidance as to whether this is possible? And if so what are the next step; as I have tried a number of different functions including Sum without success.

1 Like

Hi @TanyaT

Welcome to the Community! :sunny:

I see that you are already at the finish line with the formula with successfully getting the linked children count!

Please try this formula to Sum the Values of these children:

Sum(
  [Object.Expenditure Budget].[Budget].GetMemberByKey(
    [Object].CurrentHierarchyMember.Get('DISPLAY_KEY')
  ).Children,
  [Object.Expenditure Budget].CurrentMember.Get('Value')
)

Check that the current property name is used in the .Get(“Value”) function and let me know if this returns the expected results!

​Best regards,
​Nauris

1 Like

Thank you that certainly returned the expected results, it was so much easier than I thought. :smiley:

I have a similar requirement, but would like to only sum children with a specific object Status or object Type. How would you go about adding a filter to the formula?

Hi @TanyaT

The current set of children that you pass to the Sum() function, can be first filtered by the necessary condition with the Filter() function.

The formula would look something along these lines:

Sum(
  -- the filter function will return a filtered set
  Filter(
    -- set of all children from the current Budget member
    [Object.Expenditure Budget].[Budget].GetMemberByKey(
      [Object].CurrentHierarchyMember.Get('DISPLAY_KEY')
    ).Children,
    -- the condition by which to select the children
    [Object.Expenditure Budget].CurrentHierarchyMember.Get('Object Type') = "Specific Type"
  ),
  [Object.Expenditure Budget].CurrentMember.Get('Value')
)

Be sure to adjust it as necessary- so that the Status and Type are collected properly and compared to the necessary values.

​Best regards,
​Nauris

Thank you for your continued assistance, but I am still struggling to filter on the object type.

The Expenditure object type is abstract with children object types inheriting its attributes.
Expenditure has children objects (Actual, Forecast, Commitment)

What I’m wanting to do is sum just the Value of Actual objects. The problem I am having is that when I try to enter the below:

[Object.Expenditure Budget].CurrentHierarchyMember.Get(‘Object type’) = “Actual”

‘Object type’ is not an attribute that I am able return. I’m not sure how to collect the Object Type value in this situation. Any suggestions appreciated, or is there any online resources that can assist with understanding how to do this?

Hi @TanyaT

I apologize for missing this thread.
Could you please try this formula?

Sum(
  -- the filter function will return a filtered set
  Filter(
    -- set of all children from the current Budget member
    [Object.Expenditure Budget].[Budget].GetMemberByKey(
      [Object].CurrentHierarchyMember.Get('DISPLAY_KEY')
    ).Children,
    -- the condition by which to select the children
    [Object.Expenditure Budget].CurrentHierarchyMember.Name = "Actual"
  ),
  [Object.Expenditure Budget].CurrentMember.Get('Value')
)

​Best regards,
​Nauris

The formula returns no results.
I tried using the following output to check the Name attribute and found it to be empty.
Using:
[Object.Expenditure Budget].[Budget].GetMemberByKey([Object].CurrentHierarchyMember.Get(‘DISPLAY_KEY’)).Children.Item(1).AllProperties

Returns:
KEY: FIN-5956719
URL_ID: 5956719
DISPLAY_KEY: FIN-5956719
PARENT_KEY: 2935
Label: XXX-24-003
Name:
Created at: 2024-01-05 14:26:47
Updated at: 2024-01-25 16:50:17
Updated at UTC: 2024-01-25 05:50:17
Status ID: 1501
Expenditure Budget: FIN-5762597
Expenditure Period: FIN-5876549
Expenditure Description:
Expenditure Category: FIN-5873570
Expenditure Value: 10295.45