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?
Pass a dimensional element from a TM1RPTROW row to a parameter in a TI process
-
- Posts: 14
- Joined: Tue Aug 31, 2021 12:04 pm
- OLAP Product: Cognos Analytics
- Version: 10.2.2
- Excel Version: 2017
Pass a dimensional element from a TM1RPTROW row to a parameter in a TI process
- Attachments
-
- Screenshot_3.png (4.69 KiB) Viewed 4454 times
-
- Regular Participant
- Posts: 198
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: Pass a dimensional element from a TM1RPTROW row to a parameter in a TI process
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
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
-
- Posts: 14
- Joined: Tue Aug 31, 2021 12:04 pm
- OLAP Product: Cognos Analytics
- Version: 10.2.2
- Excel Version: 2017
Re: Pass a dimensional element from a TM1RPTROW row to a parameter in a TI process
i have already read this, the problem was with excel.
-
- Site Admin
- Posts: 1457
- Joined: Wed May 28, 2008 9:09 am
Re: Pass a dimensional element from a TM1RPTROW row to a parameter in a TI process
I use an approach which isn't as slick but works in Excel and Web:
Works well enough. Doesn't handle repeated elements gracefully.
- 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
Works well enough. Doesn't handle repeated elements gracefully.
-
- Regular Participant
- Posts: 198
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: Pass a dimensional element from a TM1RPTROW row to a parameter in a TI process
No problem, will provide some illustrations tomorrow, unless someone beats me to it.
-
- Regular Participant
- Posts: 198
- Joined: Wed May 06, 2020 2:58 pm
- OLAP Product: Planning Analytics
- Version: 2.0.9
- Excel Version: 2016
Re: Pass a dimensional element from a TM1RPTROW row to a parameter in a TI process
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.
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.
- Attachments
-
- Action Button.docx
- (231.85 KiB) Downloaded 222 times
-
- Example.xlsx
- (77.78 KiB) Downloaded 190 times
-
- MVP
- Posts: 3126
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Pass a dimensional element from a TM1RPTROW row to a parameter in a TI process
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).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: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.
- 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
Works well enough. Doesn't handle repeated elements gracefully.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly