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.
Generate MDX for subsets in Excel
-
- MVP
- Posts: 3128
- 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
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
- 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
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.
- 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
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.
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.
- gtonkin
- MVP
- Posts: 1209
- 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
Looks like 2 years out of date-should be 2204 build 15xxx or something.
File, Account, Update? Or has IT blocked this?
File, Account, Update? Or has IT blocked this?
- gtonkin
- MVP
- Posts: 1209
- 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
Thanks for this post Wim, looks like some fun to be had.
- 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
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.
-
- MVP
- Posts: 3128
- 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
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
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
- 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
It would seem I'm getting a reputation on this forum. Meh, I'm shameless anyway. I'll own it.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>
«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.
-
- MVP
- Posts: 3128
- 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
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
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