TM1 Perspectives Active Form and O365 removing formulas
-
- Posts: 3
- Joined: Thu May 20, 2021 11:48 am
- OLAP Product: TM1 Planning Analytics/PAW
- Version: 2.0.9/2.0.57
- Excel Version: MS Office 365 64 bit
TM1 Perspectives Active Form and O365 removing formulas
Hi,
In a reconciliation report between Planning Analytics and Controller, we have an active form pointing to a TM1 instance and alongside a view of Controller with parameters to return the lookup value. The TM1RPTROW builds the account dimension which is used as a reference in the cc.fGetVal and this was repeating down dynamically in Excel 2016.
Since switching to O365, whenever the active form is rebuilt using either the Rebuild Current Sheet or Rebuild Current book, the formulas no longer repeat down for the controller report or the check formulas alongside the Active form, they appear to stay just for the first row. I've checked the TM1RPTMTRNG which covers the width of the full reconciliation report.
Current Excel Version: Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20764) 64-bit
Previous Version Microsoft Excel 2016 (16.05369.100) MSO (16.0.5365.1000) 64-bit
In a reconciliation report between Planning Analytics and Controller, we have an active form pointing to a TM1 instance and alongside a view of Controller with parameters to return the lookup value. The TM1RPTROW builds the account dimension which is used as a reference in the cc.fGetVal and this was repeating down dynamically in Excel 2016.
Since switching to O365, whenever the active form is rebuilt using either the Rebuild Current Sheet or Rebuild Current book, the formulas no longer repeat down for the controller report or the check formulas alongside the Active form, they appear to stay just for the first row. I've checked the TM1RPTMTRNG which covers the width of the full reconciliation report.
Current Excel Version: Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20764) 64-bit
Previous Version Microsoft Excel 2016 (16.05369.100) MSO (16.0.5365.1000) 64-bit
-
- Posts: 4
- Joined: Wed Oct 06, 2021 4:17 pm
- OLAP Product: TM1
- Version: PAoC
- Excel Version: Office 365
Re: TM1 Perspectives Active Form and O365 removing formulas
Hi
I had similar issues with dynamic reports after moving across to O365. If you try the recreate book button it should fix the problem.
From my understanding the recreate button will effectively delete row 2 onwards of the dynamic area and recreate it. This then forces any content off the the side to also fill down with the rebuild.
I had similar issues with dynamic reports after moving across to O365. If you try the recreate book button it should fix the problem.
From my understanding the recreate button will effectively delete row 2 onwards of the dynamic area and recreate it. This then forces any content off the the side to also fill down with the rebuild.
-
- Posts: 3
- Joined: Thu May 20, 2021 11:48 am
- OLAP Product: TM1 Planning Analytics/PAW
- Version: 2.0.9/2.0.57
- Excel Version: MS Office 365 64 bit
Re: TM1 Perspectives Active Form and O365 removing formulas
The recreate book option would be there for Dynamic reports in PAX, however, I'm unable to do this in an active form in Perspectives. I tried Alt + F9, hift+ctrl+alt+F9 and an an action button to rebuild the Current Sheet.flecky wrote: ↑Mon Nov 14, 2022 3:32 pm Hi
I had similar issues with dynamic reports after moving across to O365. If you try the recreate book button it should fix the problem.
From my understanding the recreate button will effectively delete row 2 onwards of the dynamic area and recreate it. This then forces any content off the the side to also fill down with the rebuild.
-
- Posts: 4
- Joined: Wed Oct 06, 2021 4:17 pm
- OLAP Product: TM1
- Version: PAoC
- Excel Version: Office 365
Re: TM1 Perspectives Active Form and O365 removing formulas
Sorry, I completely missed that. As a test, you could do what that button does manually.
Delete row 2 down to the end of the area and then rebuild the sheet. As I was trying to figure out my issue I ended up doing that before I realised thats what the recreate button does.
You should only need to do it once to get the report to work and any further updates can be done with the usual buttons.
Delete row 2 down to the end of the area and then rebuild the sheet. As I was trying to figure out my issue I ended up doing that before I realised thats what the recreate button does.
You should only need to do it once to get the report to work and any further updates can be done with the usual buttons.
-
- Posts: 3
- Joined: Thu May 20, 2021 11:48 am
- OLAP Product: TM1 Planning Analytics/PAW
- Version: 2.0.9/2.0.57
- Excel Version: MS Office 365 64 bit
Re: TM1 Perspectives Active Form and O365 removing formulas
I should have mentioned what I've tested so far - I've tried both the rebuild Current Sheet and Rebuilt current book for the workbook in addition to removing all rows after the first row and all columns after the end of the report range.
I've also opened the same workbook in our UAT environment which still has 2016 + Perspectives and the same report is able to repeat rows for non-TM1 formulas adjacent to the active form. I've also created a fresh Active form (in O365) in a new workbook which again leaves just the first row with any formulas I'd add to the right of the active form.
Thank you for your help!
I've also opened the same workbook in our UAT environment which still has 2016 + Perspectives and the same report is able to repeat rows for non-TM1 formulas adjacent to the active form. I've also created a fresh Active form (in O365) in a new workbook which again leaves just the first row with any formulas I'd add to the right of the active form.
Thank you for your help!
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: TM1 Perspectives Active Form and O365 removing formulas
Some other things you can try:
- Right-click on the TM1RPT row cell then navigate to the Edit Set option, choose something else and see if that triggers a rebuild.
- Check the TM1RPTTitle references in the TM1RPTView cell. Start by removing them all and rebuilding. If it rebuilds, add them back based on what is actually context related.
- Hash out all but one column that refers to the TM1RPTView cell and will drive the form. Errors, dependencies etc. could be causing an issue.
- Try removing the named range referring to the data range then rebuilding.
- Failing this, if you have not tried another machine try that. Sometimes you need to close Excel, clear temp files etc, sometimes reboot, sometimes tequila.
- Right-click on the TM1RPT row cell then navigate to the Edit Set option, choose something else and see if that triggers a rebuild.
- Check the TM1RPTTitle references in the TM1RPTView cell. Start by removing them all and rebuilding. If it rebuilds, add them back based on what is actually context related.
- Hash out all but one column that refers to the TM1RPTView cell and will drive the form. Errors, dependencies etc. could be causing an issue.
- Try removing the named range referring to the data range then rebuilding.
- Failing this, if you have not tried another machine try that. Sometimes you need to close Excel, clear temp files etc, sometimes reboot, sometimes tequila.
-
- MVP
- Posts: 3113
- 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: TM1 Perspectives Active Form and O365 removing formulas
My recollection may be flawed, but I thought there were some issues in dynamic reports regarding formulas that would not copied down as much as needed. IIRC, this wasn't too long ago and it could well be in the range of versions of an early 2.0.9.
Does this ring a bell to someone ? Maybe worth having a look in the Fix lists of the more recent versions.
Does this ring a bell to someone ? Maybe worth having a look in the Fix lists of the more recent versions.
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
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: TM1 Perspectives Active Form and O365 removing formulas
Yes, we recently had an issue with PA 2.0.9.13/14 where it seemed to only copy down the first column with DBRW's, simply ignored all others.Wim Gielis wrote: ↑Mon Nov 14, 2022 7:53 pm My recollection may be flawed, but I thought there were some issues in dynamic reports regarding formulas that would not copied down as much as needed. IIRC, this wasn't too long ago and it could well be in the range of versions of an early 2.0.9.
Does this ring a bell to someone ? Maybe worth having a look in the Fix lists of the more recent versions.
Seems fine now on 2.0.9.15 with Perspectives that came with that but would not recommend upgrading due to the TI/Rule based Attributes issue mentioned in the forum. May be worth trying some different clients.
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: TM1 Perspectives Active Form and O365 removing formulas
Also, and not to be "that guy", but Perspectives is not supported on O365....
Yes it normally works but using O365 introduces risks...
Yes it normally works but using O365 introduces risks...
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 31
- Joined: Fri Jan 13, 2017 12:33 pm
- OLAP Product: IBM Planning Analytics/TM1
- Version: 2.0.9.15
- Excel Version: Excel 2019
Re: TM1 Perspectives Active Form and O365 removing formulas
Hi,
Last nov-22 we upgraded to:
Planning Analytics 2.0 Local 2.0.9.15 (11.8.01700.1)
Planning Analytics 2.0 Spreadsheet Services 2.0.81
Planning Analytics 2.0 For Microsoft Excel 2.0.82
Planning Analytics 2.0 Workspace 2.0.81
I think I have the same problem...
When trying to refresh an active form built on TM1 Perspectives using MS EXCEL 2019 it never copy the formulas from dynamic row to cells below...
It only do it for some columns...
In order to discard any mistake in the report, I create a new one exporting a cubewiew to an active form and i got the same problem. So It's not related to the particular report.
I have same version of MS EXCEL:
Do you know if there is a solution from IBM Support (I was looking but it seems not yet) ?
Is there any way to solution this problem?
Kind regards!
Last nov-22 we upgraded to:
Planning Analytics 2.0 Local 2.0.9.15 (11.8.01700.1)
Planning Analytics 2.0 Spreadsheet Services 2.0.81
Planning Analytics 2.0 For Microsoft Excel 2.0.82
Planning Analytics 2.0 Workspace 2.0.81
I think I have the same problem...
When trying to refresh an active form built on TM1 Perspectives using MS EXCEL 2019 it never copy the formulas from dynamic row to cells below...
It only do it for some columns...
In order to discard any mistake in the report, I create a new one exporting a cubewiew to an active form and i got the same problem. So It's not related to the particular report.
I have same version of MS EXCEL:
Do you know if there is a solution from IBM Support (I was looking but it seems not yet) ?
Is there any way to solution this problem?
Kind regards!
- Attachments
-
- Version EXCEL.png (3.91 KiB) Viewed 1910 times
-
- Active Form Error TM1 Perspectives.png (136.82 KiB) Viewed 1910 times
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: TM1 Perspectives Active Form and O365 removing formulas
Just on the off chance does it work without the column break in G?
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 31
- Joined: Fri Jan 13, 2017 12:33 pm
- OLAP Product: IBM Planning Analytics/TM1
- Version: 2.0.9.15
- Excel Version: Excel 2019
Re: TM1 Perspectives Active Form and O365 removing formulas
Hi,
the last column of the data range gets wrong as it will not replicate formulas from the TRM1RPTROW to below cells.
And curious.. if you add a second column then the second column from rigth to left in data range gets wrong too and so on...
Please take a look on the following pictures in order and see how the error is triggered:
The steps to reproduce de error:Just on the off chance does it work without the column break in G?
- step1 : export an active form from any cubeview using Tm1 perspectives
step2: insert a new blank column anywhere in the data range
step3 click on recreate the sheet or workbook
the last column of the data range gets wrong as it will not replicate formulas from the TRM1RPTROW to below cells.
And curious.. if you add a second column then the second column from rigth to left in data range gets wrong too and so on...
Please take a look on the following pictures in order and see how the error is triggered:
- Attachments
-
- step1.png (75.75 KiB) Viewed 1873 times
-
- step2.png (76.59 KiB) Viewed 1873 times
-
- step3.png (33.43 KiB) Viewed 1873 times
-
- Posts: 31
- Joined: Fri Jan 13, 2017 12:33 pm
- OLAP Product: IBM Planning Analytics/TM1
- Version: 2.0.9.15
- Excel Version: Excel 2019
Re: TM1 Perspectives Active Form and O365 removing formulas
Now, please see what happen if I don't leave the blank and add any formula
and what happen IF I deleted the inserted blank columns:
and what happen IF I deleted the inserted blank columns:
- Attachments
-
- step5.png (65.87 KiB) Viewed 1867 times
-
- step4.1.png (62.25 KiB) Viewed 1867 times
-
- step4.png (60.85 KiB) Viewed 1867 times
- Steve Rowe
- Site Admin
- Posts: 2416
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: TM1 Perspectives Active Form and O365 removing formulas
If you put an apostrophe in the blank column you can probably keep your report layout working.
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- MVP
- Posts: 3113
- 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: TM1 Perspectives Active Form and O365 removing formulas
It is probably something along these lines.
The active form will count the number of non-empty cells in the first row, the TM1RPTROW row, and create the range to encompass these.
If you have empty cells, they are not counted towards the total, and hence too few columns have their first row copied downwards.
The active form will count the number of non-empty cells in the first row, the TM1RPTROW row, and create the range to encompass these.
If you have empty cells, they are not counted towards the total, and hence too few columns have their first row copied downwards.
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
-
- Posts: 31
- Joined: Fri Jan 13, 2017 12:33 pm
- OLAP Product: IBM Planning Analytics/TM1
- Version: 2.0.9.15
- Excel Version: Excel 2019
Re: TM1 Perspectives Active Form and O365 removing formulas
Hi Wim and Steve,
Thanks for your comments.
I was testing what you proposed, but I got the same results:
Thanks for your comments.
I was testing what you proposed, but I got the same results:
- Attachments
-
- test.png (59.43 KiB) Viewed 1837 times
-
- MVP
- Posts: 3113
- 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: TM1 Perspectives Active Form and O365 removing formulas
What happens if you fill the blue cell right underneath the left red arrow ?
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
-
- Posts: 31
- Joined: Fri Jan 13, 2017 12:33 pm
- OLAP Product: IBM Planning Analytics/TM1
- Version: 2.0.9.15
- Excel Version: Excel 2019
Re: TM1 Perspectives Active Form and O365 removing formulas
Hi,
I populated the blue title with the same string, a diferent period but nothing changes...
Only when I add a DBRW formula that uses the TM1RPTView the problem disapear.
I populated the blue title with the same string, a diferent period but nothing changes...
Only when I add a DBRW formula that uses the TM1RPTView the problem disapear.
-
- MVP
- Posts: 3113
- 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: TM1 Perspectives Active Form and O365 removing formulas
I don’t know the logic there but it sounds like a bug.
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