TM1 Forum

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.

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.

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™

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.

«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

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.

ascheevel wrote: ↑Wed May 11, 2022 1:29 pm 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.

ascheevel, you mentioned rendering set editor in a userform in excel. Could you please provide more details? I tried adding the subset editor link mentioned in the link shrea by you, but it is opening the PAW Admin page.

Scenario:
I am migrating Perspectives reports to PAfE. In Perspectives, the user used to double click on subnm and open subset editor, however, I could not replicate this functionality in PAfE. I tried using TM1Set, but we can have 1 TM1Set per sheet due to named range.
Is there any way I can achieve this? Thank You in advance.

Regards,
Lin

Double clicking a SUBNM cell to open set editor works in Pafe except when you have an "&" in the subset name. The dropdown selection feature of SUBNM cells also doesn't work when you have an "&" in the subset name.