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.
PAfE API to open a set editor
- 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
War teaches us geography, getting old teaches us biology.
-
- 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
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.
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.
- 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
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™
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.
-
- 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
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?
You had another post on this. I'll post my thoughts there to avoid merging the threads.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!
- 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
«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
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.
-
- 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
Maybe you can get ideas from my addition here20 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.
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
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