Page 1 of 1
REST API: consolidated elements without children
Posted: Tue Mar 05, 2019 1:55 pm
by Dimix
Hi
Sorry for a very basic (?) question, but this is still pretty new to me.
I'm looking for a way to list all consolidated elements within a dimension without any children.
Below syntax will retrieve all members (and number children) in the plan_business_unit dimension.
Dimensions('plan_business_unit')/Hierarchies('plan_business_unit')/Members?$expand=Children($select=Name), Element($select=Name,Type), Children/$count
- Capture.PNG (7.33 KiB) Viewed 6134 times
I can then use
Type to check for consolidated elements and number of children.
I'm sure there is an easier way to do this, i.e. only retrieve consolidated elements without any children. I have tried to combine above request with
?$filter=Level%20gt%200 and
$count eq 0, but I can't get it to work.
Doable?
Re: REST API: consolidated elements without children
Posted: Tue Mar 05, 2019 5:07 pm
by PavoGa
Example Dimension has two consolidations without children:
- Test2 hierarchy.png (13.97 KiB) Viewed 6125 times
This MDX code produces as set of the consolidations without children:
Code: Select all
FILTER(EXCEPT(TM1SUBSETALL( [test2] ), TM1FILTERBYLEVEL( TM1SUBSETALL( [test2]), 0)),
TM1TUPLESIZE( ([test2].currentmember.children).ITEM(0)) = 0)
- ChildlessConsolidations.png (8.89 KiB) Viewed 6125 times
Save the subset and expose the subset with REST API.
Re: REST API: consolidated elements without children
Posted: Tue Mar 05, 2019 8:22 pm
by lotsaram
Pretty sure this would return the same.
Code: Select all
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [test2]), 1), TM1TUPLESIZE( ([test2].currentmember.children).ITEM(0)) = 0)
Childless consolidations still evaluate as level 1 and not level 0.
Re: REST API: consolidated elements without children
Posted: Tue Mar 05, 2019 10:30 pm
by MariusWirtz
This should do the trick:
Code: Select all
/api/v1/Dimensions('plan_business_unit')/Hierarchies('plan_business_unit')/Members?$select=Name&$expand=Element($select=Type),Children/$count&$filter=Element/Type eq 3 and Children/$count eq 0
Re: REST API: consolidated elements without children
Posted: Tue Mar 05, 2019 10:41 pm
by PavoGa
lotsaram wrote: ↑Tue Mar 05, 2019 8:22 pm
Childless consolidations still evaluate as level 1 and not level 0.
Flat did not think about/realize that. Good tip, thanks.
Re: REST API: consolidated elements without children
Posted: Wed Mar 06, 2019 12:27 am
by Wim Gielis
Hi
Not sure I understand why you would not use easier functions that TM1TupleSize and Item. Straight from my MDX page:
Code: Select all
Consolidations without children:
{Filter( TM1FilterByLevel( TM1SubsetAll([test]), 1), ISLEAF( [test].CurrentMember ))}
{Filter( TM1FilterByLevel( TM1SubsetAll([test]), 1), [test].CurrentMember.FirstChild.Name = "")}
{Filter( TM1FilterByLevel( TM1SubsetAll([test]), 1), Count([test].CurrentMember.Children) = 0)}
Re: REST API: consolidated elements without children
Posted: Wed Mar 06, 2019 9:02 am
by lotsaram
@Wim I'm kinda surprised that the first query works!
Code: Select all
{Filter( TM1FilterByLevel( TM1SubsetAll([test]), 1), ISLEAF( [test].CurrentMember ))}
That I think would have to be a bug in the MDX engine or interpretation of what a "leaf" is (that is a numeric element). A consolidation without children can't store data. It isn't a leaf in any normal sense.
Re: REST API: consolidated elements without children
Posted: Wed Mar 06, 2019 12:16 pm
by PavoGa
Wim Gielis wrote: ↑Wed Mar 06, 2019 12:27 am
Hi
Not sure I understand why you would not use easier functions that TM1TupleSize and Item. Straight from my MDX page:
@Wim, ask a hard question.
1) Have visited your MDX page. Just did not recall those queries or would have referenced them.
2) Have to admit I'm not as up on all the various MDX functions as you obviously are, but am grateful someone knows more me.
3) After wondering what TM1TupleSize's purpose for existence is, I have found a use or two. And now that I've got that hammer, all queries like this look like nails.
4) Great Granddaddy Butler was a Methodist circuit preacher and I got a bit of that in me I guess. I'm preaching the Gospel of TM1TupleSize.
Seriously, thanks. I am going to visit your MDX page again! Good
Re: REST API: consolidated elements without children
Posted: Thu Mar 07, 2019 11:18 am
by Wim Gielis
PavoGa wrote: ↑Wed Mar 06, 2019 12:16 pmSeriously, thanks. I am going to visit your MDX page again! Good
Thanks ! Happy reading.