Dynamic Action Button

[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

[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.

[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.

[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

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.

[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

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.

[Wim Gielis]

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

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.

[Wim Gielis]

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

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.

[mboderoriv]

I have been able to do it. Thank you so much for the advice guys!

[Wim Gielis]

You’re welcome