REST API: consolidated elements without children

Post Reply
Dimix
Posts: 24
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 » Tue Mar 05, 2019 1:55 pm

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 319 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
Community Contributor
Posts: 324
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: REST API: consolidated elements without children

Post by PavoGa » Tue Mar 05, 2019 5:07 pm

Example Dimension has two consolidations without children:

Test2 hierarchy.png
Test2 hierarchy.png (13.97 KiB) Viewed 310 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 310 times
Save the subset and expose the subset with REST API.
Ty
Cleveland, TN

lotsaram
MVP
Posts: 3229
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 » Tue Mar 05, 2019 8:22 pm

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: 4
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 » Tue Mar 05, 2019 10:30 pm

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
Community Contributor
Posts: 324
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: REST API: consolidated elements without children

Post by PavoGa » Tue Mar 05, 2019 10:41 pm

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: 2040
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: REST API: consolidated elements without children

Post by Wim Gielis » 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:

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

lotsaram
MVP
Posts: 3229
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 » Wed Mar 06, 2019 9:02 am

@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
Community Contributor
Posts: 324
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: REST API: consolidated elements without children

Post by PavoGa » Wed Mar 06, 2019 12:16 pm

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: 2040
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: REST API: consolidated elements without children

Post by Wim Gielis » Thu Mar 07, 2019 11:18 am

PavoGa wrote:
Wed Mar 06, 2019 12:16 pm
Seriously, thanks. I am going to visit your MDX page again! Good :idea:
Thanks ! Happy reading.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Post Reply