Generate MDX for subsets in Excel

Ideas and tips for enhancing your TM1 application
Post Reply
Wim Gielis
MVP
Posts: 3111
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:

Generate MDX for subsets in Excel

Post by Wim Gielis »

Hi all,

A small fun side project of mine :-)

Turn on the record function by clicking the cell next to "Record mode" if it's off.
Then click on the 1st column of the orange table to apply that particular MDX operation of the result in the yellow cell.
Fill out the 3rd column of the table when arguments are needed.

There's a bit of VBA code but I keep it deliberately small. The formulae in the sheet, notably, Lambda's and MAKEARRAY are much more interesting.
You do need a recent Excel version to use the file.

I like the input table on the right-hand side, whereby dynamic array formulas do things we could not do in the past.

You could extend it for instance by sending the MDX to the clipboard.

Enjoy.
08.PNG
08.PNG (228.06 KiB) Viewed 12561 times
MDX generator - Wim Gielis.xlsm
(27 KiB) Downloaded 394 times
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
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: Generate MDX for subsets in Excel

Post by 20 Ton Squirrel »

Thanks very much for this, Wim. I'll peek into it and give some feedback this week.
War teaches us geography, getting old teaches us biology.
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: Generate MDX for subsets in Excel

Post by 20 Ton Squirrel »

I don't have some of these functions like MAKEARRAY so the workbook "breaks" on me.

I run Office 365, Excel is version 2002 (build 14931.20274). I see everyone on the internet gabbing about MAKEARRAY and LAMBDA functions but I don't have 'em. Now I'm jealous.
War teaches us geography, getting old teaches us biology.
User avatar
gtonkin
MVP
Posts: 1198
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Generate MDX for subsets in Excel

Post by gtonkin »

Looks like 2 years out of date-should be 2204 build 15xxx or something.
File, Account, Update? Or has IT blocked this?
User avatar
gtonkin
MVP
Posts: 1198
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Generate MDX for subsets in Excel

Post by gtonkin »

Thanks for this post Wim, looks like some fun to be had.
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: Generate MDX for subsets in Excel

Post by 20 Ton Squirrel »

My company's IT is the worst. Figures I'm two years behind and screwed for it. >__<
War teaches us geography, getting old teaches us biology.
Wim Gielis
MVP
Posts: 3111
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: Generate MDX for subsets in Excel

Post by Wim Gielis »

gtonkin wrote: Thu May 12, 2022 7:35 pmThanks for this post Wim, looks like some fun to be had.
You can barely imagine all the singing and dancing when working on the file. Beers and confetti all around. You should have seen it to believe.
</20 ton squirrel mode=OFF>
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
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: Generate MDX for subsets in Excel

Post by 20 Ton Squirrel »

Wim Gielis wrote: Thu May 12, 2022 8:16 pm You can barely imagine all the singing and dancing when working on the file. Beers and confetti all around. You should have seen it to believe.
</20 ton squirrel mode=OFF>
It would seem I'm getting a reputation on this forum. Meh, I'm shameless anyway. I'll own it.

«attempts to </20 Ton Squirrel Mode=OFF> but OH NO THE TAG IS BROKEN AND XML SPILLS ALL OVER THE FLOOR, STAINING YOUR CARPET»

🤪
War teaches us geography, getting old teaches us biology.
Wim Gielis
MVP
Posts: 3111
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: Generate MDX for subsets in Excel

Post by Wim Gielis »

Houston, we have an XML problem !
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