TM1 Forum

Hello
May be somebody know how to pass a dimensional element from a TM1RPTROW row to a parameter in a TI process via an Action Button?
I try to do it using OFFSET function. But it does't work.
Could someone help me?

https://www.linkedin.com/pulse/how-pass ... eter-ralph

Have a read of this. If you're still stuck let us know and I'll generate an illustration when I'm at the laptop

i have already read this, the problem was with excel.

I use an approach which isn't as slick but works in Excel and Web:

• Create a range over the Active Form column you want to select from
• Use Data Validation to point a 'selector' cell to that range
• You can then select one of the elements in the column and pass it to your Action Button

Key requirement is that the range must extend beyond the active form. As the active form selections change, the range will grow and shrink to match.
Works well enough. Doesn't handle repeated elements gracefully.

No problem, will provide some illustrations tomorrow, unless someone beats me to it.

Have a look at the attachments. In Example.xlsx have a look at the tab Bank Statement Review, in cells C11 to C13, I am using offset to return the the values from within the active form... In this example I wanted the user to select a row, and for that selection to drive parameters for a process and navigate and set properties on another form (BACS entry detail). This example was built in perspectives, so for those with PAX, I have provided screenshots the BACS Entry action button.

I created a named range called rng_start from cell D26 to Q26 i.e. the titles for each column on the report and then used this in the offset function...
in c12 i have this formula
=OFFSET(D26,@CELL("row")-@ROW(rng_start),8)

This is saying to start from cell D26, and navigate down the number of rows between row 26 and the row the user has selected... in your case if you want the second row of the report, then it would be =OFFSET(D26,2,8)
The 8 is how many columns to to the right of D26 do you require data from. If you needed something from column D then it would be 0, column E then it would be 1 ...

Once you have the data extracted using offset, you can then map those cells to the parameters of your process.

David Usherwood wrote: ↑Thu Sep 02, 2021 10:41 pm I use an approach which isn't as slick but works in Excel and Web:

• Create a range over the Active Form column you want to select from
• Use Data Validation to point a 'selector' cell to that range
• You can then select one of the elements in the column and pass it to your Action Button

Key requirement is that the range must extend beyond the active form. As the active form selections change, the range will grow and shrink to match.
Works well enough. Doesn't handle repeated elements gracefully.

I bet the UNIQUE function and dynamic arrays as the source of your data validation dropdown could be helpful. Or the TM1ELLIST function (https://www.ibm.com/docs/en/planning-an ... -tm1ellist).