Invert Hierarchy in INFOR.GET.ELEMENTS Excel Integration

Jose Salinas

Good morning everyone,

I'm building a report with our account hierarchy displayed on the rows via the INFOR.GET.ELEMENTS formula on the Excel Integration tool. One of the requirements is to invert the hierarchy so that children come before the parents. In addition, I have a custom user field that I need to use to sort the members of the hierarchy. However, it seems that the ORDER and HIERARCHIZE functions are in conflict with each other.

If I place the ORDER function on the outside and the HIERARCHIZE function within, the accounts are sorted via the custom user field but HIERARCHIZE function does not flip the children to be on top when used with the POST option.

If I place the HIERARCHIZE function on the outside and the ORDER function within, the children are flipped to be on top but the accounts are not sorted by the custom user field. Has anyone been able to get the two functions to work together using the INFOR.GET.ELEMENTS function or any of the available functions?

I see that INFOR.GET.AXIS has an "Options" field. In some examples, I've seen "ExpandToLevel, 100" used in this field. Is there syntax that can be used here to flip hierarchy and sort by specific field?

Thanks in advance!

My Formula

INFOR.GET.ELEMENTS(FALSE,"BIFSM_FSM","GLTOT",{"ATT1_BIFSMOrderUDF","ATT1_ACCOUNT","MEMBER_CAPTION","ATT1_LEVEL","ATT1_ACCOUNTTYPE","MEMBER_UNIQUE_NAME"},"[GLTOT_ChartAccount]",,,,,

"
ORDER(
HIERARCHIZE(
DISTINCT(
FILTER(
ADDCALCULATEDMEMBERS(
DESCENDANTS([GLTOT_ChartAccount].[CH00].[CH00/36_TOP_NODE].[CH00/36_NET_INCOME],
[GLTOT_ChartAccount].[CH00].[CH00/36_TOP_NODE].[CH00/36_NET_INCOME].Level,
After)
),
[GLTOT_ChartAccount].CurrentMember.Properties('ATT1_Level')<=6)
), POST
),
[GLTOT_ChartAccount].CurrentMember.Properties('ATT1_BIFSMOrderUDF'), ASC)
")

Best regards,

Jose

Vit Holy

Hello @jose-salinas2 ,

I think this cannot be easily done from MDX POV. I think the Hierarchize will enforce the order and so it is really clashing. There can be some more complext solution but that will probably affect heavily performance + not sure it would work with INFOR.GET.AXIS or INFOR.GET.ELEMENTS.

However, what I would recommend is to check INFOR.READ.SLICE (ad-hoc) where you can use Financial Mode. Maybe it will help you to cover this scenario?