Clearing a drop down selection made using subnm

Post Reply
M1ndbender
Posts: 9
Joined: Mon Jul 19, 2021 2:15 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: 365

Clearing a drop down selection made using subnm

Post by M1ndbender »

I have basically a set of columns in PAX with the ability for the user to filter from a drop down generated by a subnm pointing to a subset. The issue I have is there is no way to clear out the selections the user made if they want to remove a filter.

Is there a way to do this through a button using mdx or a way to add a null value to the drop down they could use to basically revert to select all records?

Any help would be appreciated
User avatar
gtonkin
MVP
Posts: 988
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.10; PAW 2.0.71
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Clearing a drop down selection made using subnm

Post by gtonkin »

Why not add the Total element as the first member in the set and set your index to 1.
Obviously after changing, the user would need to set it back to Total.

Could possibly also use TM1Ellist and link a data validation to that with a named range and a blank cell in cell(1,1).

I think others have previously posted using a dummy element that never has data stored e.g. Select Period . This would be part of the subset in position 1. Problem is you would need to handle this vs Total, depending on your use case.
burnstripe
Posts: 91
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Clearing a drop down selection made using subnm

Post by burnstripe »

You could also have a check box placed next to your subnm which states if the filters are active or not and then an if statement to determine what selections get applied to the form.

If the user wanted to remove the filter than they would just need to uncheck the checkbox and click rebuild
M1ndbender
Posts: 9
Joined: Mon Jul 19, 2021 2:15 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: 365

Re: Clearing a drop down selection made using subnm

Post by M1ndbender »

This sounds like a good idea but I am a bit lost.

So each subnm would have a checkbox and that check box would contain logic to see if it is in use? - not sure how to do this

and then the could unselect the checkbox and rebuild the sheet - would there need to be anything needed in the checkbocx logic for this?

Thanks for you input
burnstripe
Posts: 91
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Clearing a drop down selection made using subnm

Post by burnstripe »

Yes, each subnm would have a checkbox next to it.

I'm not at my pc just now but I think to add checkbox you would need to turn on the developer ribbon for excel. On that ribbon will be an option that will allow you to add a checkbox. When adding the checkbox it'll ask to link to a cell. When the checkbox is ticked that linked cell will show 1 or true, and then when not a false or 0. In another cell have a formula that goes if the checkbox cell = 1/true then grab the subnm and return it in mdx form.
{[dimname]. [element name]}
If it's 0/false then have you default selection, for example all leaf elements
{tm1filterbylevel({tm1subsetall([dimname])}, 0)}

Then in the active form the update the tm1rptrow to point to this mdx

Then create an action button which rebuild the sheet.
Once the user has finished making all their selection they will need to hit the action button to rebuild the sheet

If taking this approach ensure titles for the rptview are removed where necessary

Note if you don't like the checkbox a simple data validation of yes/no would work just as well
M1ndbender
Posts: 9
Joined: Mon Jul 19, 2021 2:15 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: 365

Re: Clearing a drop down selection made using subnm

Post by M1ndbender »

Thank you so much. I will give this a try
Post Reply