How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
-
- Posts: 10
- Joined: Tue Nov 16, 2021 8:16 am
- OLAP Product: IBM TM1 Perspectives and PAfE
- Version: several
- Excel Version: several
How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
Hi all,
for one of my costumers I am starting to use TM1 with Planning Analytics for Excel. As I need a pre-filtered drop-down menu to select a value, that gets than written by TI into a parameter cube.
To reach my goal, I was hoping to create a dynamic array in a hidden sheet, that serves as the input for my (data validation) drop down box. I even figured out, how to adjust the data validations drop down length, to avoid blank spaces in the drop-down, after an input list gets reduced.
Now I created a TM1ELLIST formula in a hidden sheet, to display the contents of a dynamically filtered subset. When I first wrote the formula it appeared to work. However, when I change the dynamically filtered input subset, the formula still displays the last definition of the subset and does not update.
I am using the following syntax: =TM1ELLIST("SERVER:DIM";"name_of_MDX_subset")
Is it normal behavior, that this formula only works once and after the elements of the mdx subset change, you cannot use it again? This seems useless. I even tried to rewrite the formula in another sheet or workbook, with the same result.
Is there a way to avoid this senseless behavior?
for one of my costumers I am starting to use TM1 with Planning Analytics for Excel. As I need a pre-filtered drop-down menu to select a value, that gets than written by TI into a parameter cube.
To reach my goal, I was hoping to create a dynamic array in a hidden sheet, that serves as the input for my (data validation) drop down box. I even figured out, how to adjust the data validations drop down length, to avoid blank spaces in the drop-down, after an input list gets reduced.
Now I created a TM1ELLIST formula in a hidden sheet, to display the contents of a dynamically filtered subset. When I first wrote the formula it appeared to work. However, when I change the dynamically filtered input subset, the formula still displays the last definition of the subset and does not update.
I am using the following syntax: =TM1ELLIST("SERVER:DIM";"name_of_MDX_subset")
Is it normal behavior, that this formula only works once and after the elements of the mdx subset change, you cannot use it again? This seems useless. I even tried to rewrite the formula in another sheet or workbook, with the same result.
Is there a way to avoid this senseless behavior?
- Elessar
- Community Contributor
- Posts: 340
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
Hi,
Do you have autorecalculation enabled?
Where do you use TM1ELLIST? In named range?
In short, you need to make named range using TM1ELLIST (this cannot be used in data validation), then display elements using INDEX, then make named range using OFFSET, and then use it in Excel data validation).
I've described this in more details here: https://advoynev.wordpress.com/2021/10/ ... -function/
Do you have autorecalculation enabled?
Where do you use TM1ELLIST? In named range?
In short, you need to make named range using TM1ELLIST (this cannot be used in data validation), then display elements using INDEX, then make named range using OFFSET, and then use it in Excel data validation).
I've described this in more details here: https://advoynev.wordpress.com/2021/10/ ... -function/
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
- gtonkin
- MVP
- Posts: 1199
- 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: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
What we found is that if you are Manual calculation, TM1ELLIST works the first time as you describe. Recalc does not do anything, unless you dirty the sheet i.e. tell PAfE that it needs to actually do a refresh. This also seemed to be the case with other formulas where PAfE is being efficient and only requesting when something has changed. In my case I was using macros so dirtied the sheet then did the necessary updates.
Best is to go with Automatic calculation per Elessar's post.
Best is to go with Automatic calculation per Elessar's post.
-
- Posts: 10
- Joined: Tue Nov 16, 2021 8:16 am
- OLAP Product: IBM TM1 Perspectives and PAfE
- Version: several
- Excel Version: several
Re: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
Hi and thanks for answering!
Here is what I did so far:
Best Regards
Leonhard Geisler
Here is what I did so far:
- 1. disabled auto refresh
- 2. Created named range with TM1ELLIST formula as reference (the formula is only working, when I am using the name of the MDX (mdx_Reportarten)
and not when I am pasting the whole formula from the subset editor into Excel
- 3. I used "ROWS" to count length of the named range (here I got back 1 at first, which makes no sense. After a while (like 30 min.) a 3 appeared. 3 whatsoever is the number of the elements in the mdx subset before I changed the definition. Now it should display 2, as you can see in the provided
subseteditor below
- 4. Same goes for the results of INDEX, which display still 3 elements instead of 2
- 5. Created another named range (ProductSelection) using OFFSET as indicated to retrieve the
- 6. Referencing ProductSelection (list, source: =ProductSelection) I get some undefined source error and data validation drop down stays empty
Best Regards
Leonhard Geisler
-
- Regular Participant
- Posts: 197
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
Hi,
This is a different approach, instead of using TM1ELLIST, with PAX you can add a "List" containing a subset. If you add it using this option then the list will update when the subset is changed. On the data validation then just cover the range this list covers (include a few blank cells in the range so that if new elements are added to the subset they are picked up.
No need for index function then
The blank cells also shouldn't show in the drop down once an item from the list has been selected
This is a different approach, instead of using TM1ELLIST, with PAX you can add a "List" containing a subset. If you add it using this option then the list will update when the subset is changed. On the data validation then just cover the range this list covers (include a few blank cells in the range so that if new elements are added to the subset they are picked up.
No need for index function then
The blank cells also shouldn't show in the drop down once an item from the list has been selected
-
- Posts: 10
- Joined: Tue Nov 16, 2021 8:16 am
- OLAP Product: IBM TM1 Perspectives and PAfE
- Version: several
- Excel Version: several
Re: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
Hi,
thanks for your proposed solution. However, as I tired to make it work via List, I encountered some issues again. After I did drag and drop the mdx subset into the list object, it worked fine for the first moment. After I changed the size of the mdx subset, containing 2 elements instead of three and pressing refresh, all the subset members disappear, leaving me with an empty list.
Anyone, who encountered same issues as me in this cases?
thanks for your proposed solution. However, as I tired to make it work via List, I encountered some issues again. After I did drag and drop the mdx subset into the list object, it worked fine for the first moment. After I changed the size of the mdx subset, containing 2 elements instead of three and pressing refresh, all the subset members disappear, leaving me with an empty list.
Anyone, who encountered same issues as me in this cases?
- gtonkin
- MVP
- Posts: 1199
- 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: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
Try clear the cache and see if that helps - could be that PAfE is not seeing that the sheet is dirty again.
-
- Posts: 10
- Joined: Tue Nov 16, 2021 8:16 am
- OLAP Product: IBM TM1 Perspectives and PAfE
- Version: several
- Excel Version: several
Re: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
I am not exactly sure about what you mean
- Elessar
- Community Contributor
- Posts: 340
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
Hello, I may misunderstand something... You need to enable autorefresh. Without it, you will not be able to use dynamic drop-downs
Best regards, Alexander Dvoynev
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
- gtonkin
- MVP
- Posts: 1199
- 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: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
In PAfE, go to your PAfE options then the top option IBM then scroll down in the window to Clear Cache.
You may still need to make a change on the sheet to dirty it.
Going back to Automatic calculation per Elessar's post may still be the correct way forward though.
-
- Posts: 10
- Joined: Tue Nov 16, 2021 8:16 am
- OLAP Product: IBM TM1 Perspectives and PAfE
- Version: several
- Excel Version: several
Re: How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?
clearing the cache actually did the trick. I now switched to automatically clearing it when saving the sheet. Both solutions are working just fine know. Thanks a lot!