REST API: consolidated elements without children
-
- Posts: 32
- Joined: Fri Jan 15, 2016 2:53 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
REST API: consolidated elements without children
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
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?
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
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?
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: REST API: consolidated elements without children
Example Dimension has two consolidations without children:
This MDX code produces as set of the consolidations without children:
Save the subset and expose the subset with REST API.
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)
Ty
Cleveland, TN
Cleveland, TN
-
- MVP
- Posts: 3652
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: REST API: consolidated elements without children
Pretty sure this would return the same.
Childless consolidations still evaluate as level 1 and not level 0.
Code: Select all
FILTER(TM1FILTERBYLEVEL( TM1SUBSETALL( [test2]), 1), TM1TUPLESIZE( ([test2].currentmember.children).ITEM(0)) = 0)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 31
- Joined: Sat Apr 08, 2017 8:40 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: 2016
Re: REST API: consolidated elements without children
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
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: REST API: consolidated elements without children
Flat did not think about/realize that. Good tip, thanks.
Ty
Cleveland, TN
Cleveland, TN
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: REST API: consolidated elements without children
Hi
Not sure I understand why you would not use easier functions that TM1TupleSize and Item. Straight from my MDX page:
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)}
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3652
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: REST API: consolidated elements without children
@Wim I'm kinda surprised that the first query works!
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.
Code: Select all
{Filter( TM1FilterByLevel( TM1SubsetAll([test]), 1), ISLEAF( [test].CurrentMember ))}
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: REST API: consolidated elements without children
@Wim, ask a hard question.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:
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
Ty
Cleveland, TN
Cleveland, TN
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: REST API: consolidated elements without children
Thanks ! Happy reading.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly