REST API: consolidated elements without children

Post Reply
Dimix
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

Post 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
Capture.PNG (7.33 KiB) Viewed 6082 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?
User avatar
PavoGa
MVP
Posts: 616
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

Post by PavoGa »

Example Dimension has two consolidations without children:

Test2 hierarchy.png
Test2 hierarchy.png (13.97 KiB) Viewed 6073 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
ChildlessConsolidations.png (8.89 KiB) Viewed 6073 times
Save the subset and expose the subset with REST API.
Ty
Cleveland, TN
lotsaram
MVP
Posts: 3651
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

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
MariusWirtz
Posts: 29
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

Post 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
User avatar
PavoGa
MVP
Posts: 616
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

Post 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.
Ty
Cleveland, TN
Wim Gielis
MVP
Posts: 3103
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

Post 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)}
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
lotsaram
MVP
Posts: 3651
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

Post 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
PavoGa
MVP
Posts: 616
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

Post 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 :idea:
Ty
Cleveland, TN
Wim Gielis
MVP
Posts: 3103
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

Post by Wim Gielis »

PavoGa wrote: Wed Mar 06, 2019 12:16 pmSeriously, thanks. I am going to visit your MDX page again! Good :idea:
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
Post Reply