PAfE API to open a set editor

Post Reply
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

PAfE API to open a set editor

Post by 20 Ton Squirrel »

Let's say I threw down a List object in a workbook linked to a dimension. To edit the set normally, one would activate the worksheet the list resides on and interact with the PAfE pane by clicking the wee arrow, then select Edit Set.

Is it possible to call the set editor with VBA? I'm not seeing any references in the API documentation but I'm hopeful someone out there knows a trick. ;)

Bonus Points™ if anyone knows ways to interact with that set editor window like setting/getting MDX within it.
War teaches us geography, getting old teaches us biology.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: PAfE API to open a set editor

Post by ascheevel »

I don't think this is a preferred option, but the set editor is an available widget in the PAW api. You can render the set editor within a userform in excel. This method would require the modifying & saving a public subset and then calling a refresh of the list pointed to that subset after the subset is edited. I don't think you could capture selected elements from this method, you'd literally need to save every edit to the public subset.

I think another option would be to leverage the server rest api with vba and a custom userform to view/select elements and mdx. It would require a lot of dev work, but that method would probably give you the best control over a custom member set and population of a regular excel list rather than PAfE list object.
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API to open a set editor

Post by 20 Ton Squirrel »

Thanks for the response, ascheevel! I appreciate the link to the PAW API, that is now bookmarked. Based on what you describe, it doesn't seem calling the set editor from VBA is a good option. I'll just have the user manually click around to call it up.

I was originally attempting to make a userform to build custom MDX for selecting/filtering/sorting elements. I wanted to speed up and dumb-down the original set editor to focus on the model's design. The MDX was going to be piped into a TM1ELLIST function dropped into a hidden worksheet to return a list… but I could never figure out how to get PAfE to execute/calculate that individual function and not refresh the adjacent quick reports!

The alternative to THAT was to use the REST API, as you recommended. I'm not proficient enough (yet) to develop something that complex, so I need to keep things simple… but I still want to learn, dammit. ;)

If anyone has examples of using VBA with the REST API to execute subset MDX queries, they might could win a fortune in Internet Appreciation Points™
War teaches us geography, getting old teaches us biology.
ascheevel
Community Contributor
Posts: 286
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: PA 2.0.9.1
Excel Version: 365
Location: Minneapolis, USA

Re: PAfE API to open a set editor

Post by ascheevel »

Did you see gtonkin's post a few weeks ago with a link to a blog they wrote about getting started with the rest api in vba?

20 Ton Squirrel wrote: Wed May 11, 2022 3:02 pm The MDX was going to be piped into a TM1ELLIST function dropped into a hidden worksheet to return a list… but I could never figure out how to get PAfE to execute/calculate that individual function and not refresh the adjacent quick reports!
You had another post on this. I'll post my thoughts there to avoid merging the threads.
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: PAfE API to open a set editor

Post by 20 Ton Squirrel »

«emits a high-pitched joyful squeal that shatters windows for miles around»
THAT LINK IS EXACTLY WHAT I NEEDED. Holy carps, I should've searched further here. THANK YOU.

I did, indeed make a long-winded post about the whole TM1ELLIST/refresh thing. I'd love to see your take on it, honestly. Thanks again, man. This forum is solid gold. n__n
War teaches us geography, getting old teaches us biology.
Wim Gielis
MVP
Posts: 3105
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: PAfE API to open a set editor

Post by Wim Gielis »

20 Ton Squirrel wrote: Wed May 11, 2022 3:02 pmI was originally attempting to make a userform to build custom MDX for selecting/filtering/sorting elements. I wanted to speed up and dumb-down the original set editor to focus on the model's design.
Maybe you can get ideas from my addition here
I found it amusing to build it.
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