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

Post Reply
LGEIS
Posts: 8
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?

Post by LGEIS » Wed Nov 17, 2021 1:04 pm

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?

User avatar
Elessar
Community Contributor
Posts: 270
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia
Contact:

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

Post by Elessar » Wed Nov 17, 2021 1:13 pm

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/
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 3rd article - Use python to change server in all TM1 nodes in SPSS Modeler.

User avatar
gtonkin
MVP
Posts: 979
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
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?

Post by gtonkin » Wed Nov 17, 2021 2:24 pm

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
Posts: 8
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?

Post by LGEIS » Wed Nov 17, 2021 3:27 pm

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
TM1ELLIST.png (320.06 KiB) Viewed 3027 times
Best Regards
Leonhard Geisler

burnstripe
Posts: 84
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?

Post by burnstripe » Thu Nov 18, 2021 5:50 pm

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
Posts: 8
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?

Post by LGEIS » Mon Nov 22, 2021 8:46 am

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?

User avatar
gtonkin
MVP
Posts: 979
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
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?

Post by gtonkin » Mon Nov 22, 2021 8:53 am

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

LGEIS
Posts: 8
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?

Post by LGEIS » Mon Nov 22, 2021 12:29 pm

I am not exactly sure about what you mean

User avatar
Elessar
Community Contributor
Posts: 270
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Location: Russia
Contact:

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

Post by Elessar » Mon Nov 22, 2021 1:31 pm

LGEIS wrote:
Wed Nov 17, 2021 3:27 pm
  • 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
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 3rd article - Use python to change server in all TM1 nodes in SPSS Modeler.

User avatar
gtonkin
MVP
Posts: 979
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
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?

Post by gtonkin » Mon Nov 22, 2021 3:44 pm

LGEIS wrote:
Mon Nov 22, 2021 12:29 pm
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
Posts: 8
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?

Post by LGEIS » Thu Nov 25, 2021 9:16 am

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!

Post Reply