How to use TM1ELLIST in conjunction with MDX-Subsets as Drop-Down Input in Excel?

[LGEIS]

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?

[Elessar]

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/

[gtonkin]

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.

[LGEIS]

Hi and thanks for answering!

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

How can I work out this issues?

TM1ELLIST.png (320.06 KiB) Viewed 42380 times

Best Regards
Leonhard Geisler

[burnstripe]

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

[LGEIS]

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?

[gtonkin]

Try clear the cache and see if that helps - could be that PAfE is not seeing that the sheet is dirty again.

[LGEIS]

I am not exactly sure about what you mean

[Elessar]

• 1. disabled auto refresh

Hello, I may misunderstand something... You need to enable autorefresh. Without it, you will not be able to use dynamic drop-downs

[gtonkin]

I am not exactly sure about what you mean

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.

[LGEIS]

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!