Page 1 of 1
Dynamic Action Button
Posted: Tue May 06, 2025 1:08 am
by mboderoriv
Hi,
I would like to create an action button that runs a process, but with a dynamic Period. For example, I have already created an action button but I have to manually change the Period every time I want to run the process. Is there a way to make this dynamic? I.e, period for this month would be 202504 next month will be 202505, etc. Additionally, once the process is run successfully I want to update a cell with the current timestamp so I know when it was run.
I use TM1 in excel and I am a beginner in its fuctionaly. So, I am unware of what workarounds I can use to achieve this.
Any help would be greatly appreciated.
Thank you
Re: Dynamic Action Button
Posted: Tue May 06, 2025 8:09 am
by Wim Gielis
Hello,
Do you have a system cube of some sort, to collect parameters like the current fiscal period and year that are being worked on in finance ?
Or parameters in that cube to hold file paths and folder structures. Or an ODBC user and password. Etc.
That cube could be manual input by the key user, or updated via a process, or rules calculated or a combination thereof. Usually this is done with picklists such that the admin user can directly select the relevant month and year and it leads to existing dimension elements.
I recommend this approach because then, your process can just do CellGetS against the right parameter and you are done.
Re: Dynamic Action Button
Posted: Tue May 06, 2025 6:47 pm
by tomok
Since you are using the Excel add-in, the action button parameter references can also point to a cell in the workbook, instead of a hard-coded value. The cell holding the parameter value can be derived by manual input, any relevant formula in Excel, or it can reference a cube value via a DBRW formula. The possibilities are almost endless.
Re: Dynamic Action Button
Posted: Thu May 22, 2025 11:55 pm
by mboderoriv
Hi Wim,
Thank you for your response.
Yes, I have a Calendar cube with the different fiscal periods.
Could you provide the step by step on how I can do it, please? where exactly can I reference the cube and where should I inpute the cellgets?
Many thanks
Wim Gielis wrote: ↑Tue May 06, 2025 8:09 am
Hello,
Do you have a system cube of some sort, to collect parameters like the current fiscal period and year that are being worked on in finance ?
Or parameters in that cube to hold file paths and folder structures. Or an ODBC user and password. Etc.
That cube could be manual input by the key user, or updated via a process, or rules calculated or a combination thereof. Usually this is done with picklists such that the admin user can directly select the relevant month and year and it leads to existing dimension elements.
I recommend this approach because then, your process can just do CellGetS against the right parameter and you are done.
Re: Dynamic Action Button
Posted: Thu May 22, 2025 11:58 pm
by mboderoriv
Hi,
Thank you for your information. How can I reference a cell or cube in the action button? Do i do it in the field provided by adding a formula or some kind of reference? if so, how?
Appreciate your response
tomok wrote: ↑Tue May 06, 2025 6:47 pm
Since you are using the Excel add-in, the action button parameter references can also point to a cell in the workbook, instead of a hard-coded value. The cell holding the parameter value can be derived by manual input, any relevant formula in Excel, or it can reference a cube value via a DBRW formula. The possibilities are almost endless.
Re: Dynamic Action Button
Posted: Fri May 23, 2025 8:22 am
by Wim Gielis
mboderoriv wrote: ↑Thu May 22, 2025 11:55 pm
Hi Wim,
Thank you for your response.
Yes, I have a Calendar cube with the different fiscal periods.
Could you provide the step by step on how I can do it, please? where exactly can I reference the cube and where should I inpute the cellgets?
Many thanks
Wim Gielis wrote: ↑Tue May 06, 2025 8:09 am
Hello,
Do you have a system cube of some sort, to collect parameters like the current fiscal period and year that are being worked on in finance ?
Or parameters in that cube to hold file paths and folder structures. Or an ODBC user and password. Etc.
That cube could be manual input by the key user, or updated via a process, or rules calculated or a combination thereof. Usually this is done with picklists such that the admin user can directly select the relevant month and year and it leads to existing dimension elements.
I recommend this approach because then, your process can just do CellGetS against the right parameter and you are done.
You can do CellGetS() towards that cube in your TI process. Do it in the Prolog tab.
Clear values in the cube based on that selection (not too many cells to clear, not too few cells).
Or... you can add a parameter to your process like pPeriod. Then the process expects a parameter value to be entered by you if you run the process manually, or an action button should also need a value for the period. That could be a SUBNM in PAfE, or a DB in PAfE to retrieve the value from the central cube, etc.
Re: Dynamic Action Button
Posted: Fri May 23, 2025 8:24 am
by Wim Gielis
mboderoriv wrote: ↑Thu May 22, 2025 11:58 pm
Hi,
Thank you for your information. How can I reference a cell or cube in the action button? Do i do it in the field provided by adding a formula or some kind of reference? if so, how?
Appreciate your response
tomok wrote: ↑Tue May 06, 2025 6:47 pm
Since you are using the Excel add-in, the action button parameter references can also point to a cell in the workbook, instead of a hard-coded value. The cell holding the parameter value can be derived by manual input, any relevant formula in Excel, or it can reference a cube value via a DBRW formula. The possibilities are almost endless.
The value for the parameter for the action button can also be:
=pPeriod
whereby the named range pPeriod in Excel points to a cell that contains a valid period. Either hardcoded, either SUBNM, either DB to the central cube, either Excel string concatenation, etc. etc. As long as there is a valid value for the period you should be good.
Re: Dynamic Action Button
Posted: Sun May 25, 2025 10:45 pm
by mboderoriv
I have been able to do it. Thank you so much for the advice guys!
Re: Dynamic Action Button
Posted: Mon May 26, 2025 7:31 pm
by Wim Gielis
You’re welcome