How to return the children only if they exist or if they don't, the member only.

[karlo]

Hello Cognoise ... this is my first blog request .. I'm currently building a 10.2.1 Report using dynamic cubes. I'm still coming up to speed from my 'relational past' thinking!! ... so any help is appreciated.

Problem:
I have a prompt-selected member 'Parent ABC' that can OR cannot have children (ie it can be selected from any level of the hierarchy). I would like to know how to define an expression that will retrieve its children in the next level down BUT, if the member doesn't have any children, will only retrieve the member itself (eg 'Parent ABC') .

Example:
IF member [Parent ABC] has the child members [Child ABC No 1] and [Child ABC No 2] then the expression will return [Child ABC No 1] and [Child ABC No 2] 
IF member [Parent ABC] does NOT have any children then the expression will return [Parent ABC]

I can easily find the children when they exist (eg descendants([Parent ABC], 1).  Also if I use the 'selfbefore' keyword I can retrieve the member and its children. However the example case eludes me. It seems so simple but when playing with hierarchize to order unioned sets etc I end up with empty set scenarios. I have also tried performing an intersection of an intersection at the lowest levels but that also didn't work. I'm thinking there may be an elegant simpler way ...??

Thank you

[xyz]

Hi Karlo,

Try the below expression, I think it will help you.

union([your hierarchy] -> ?Your Parameter?, children([your hierarchy] -> ?Your Parameter?))

Above expression will see, if you have children for particular member of hierarchy then it will display it's children's otherwise it will display the selected member.

Hope the above expression helps :)

Thanks & Regards,
XYZ

[karlo]

Thanks XYZ! I will give it a go. Just back from an extended trip so apologies for late thanks. Appreciate the advice.