Page 1 of 1

Clearing a drop down selection made using subnm

Posted: Mon Dec 06, 2021 5:17 pm
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

Re: Clearing a drop down selection made using subnm

Posted: Mon Dec 06, 2021 5:44 pm
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.

Re: Clearing a drop down selection made using subnm

Posted: Mon Dec 06, 2021 5:50 pm
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

Re: Clearing a drop down selection made using subnm

Posted: Thu Dec 09, 2021 6:39 pm
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

Re: Clearing a drop down selection made using subnm

Posted: Fri Dec 10, 2021 12:39 am
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

Re: Clearing a drop down selection made using subnm

Posted: Fri Dec 10, 2021 3:55 pm
by M1ndbender
Thank you so much. I will give this a try