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:
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.
Thank you that certainly returned the expected results, it was so much easier than I thought.
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?
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.
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 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?
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')
)
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
