TM1 Perspectives Active Form and O365 removing formulas

Post Reply
SurajLFC
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

Post by SurajLFC »

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
flecky
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

Post by flecky »

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

Post by SurajLFC »

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

Post by flecky »

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

Post by SurajLFC »

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!
User avatar
gtonkin
MVP
Posts: 1192
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

Post by gtonkin »

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.
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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.
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
User avatar
gtonkin
MVP
Posts: 1192
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

Post by gtonkin »

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.
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.
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.
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

Also, and not to be "that guy", but Perspectives is not supported on O365....

Yes it normally works but using O365 introduces risks...
Technical Director
www.infocat.co.uk
jjramirez
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

Post by jjramirez »

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

I have same version of MS EXCEL:
Image

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
Version EXCEL.png (3.91 KiB) Viewed 1799 times
Active Form Error TM1 Perspectives.png
Active Form Error TM1 Perspectives.png (136.82 KiB) Viewed 1799 times
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

Just on the off chance does it work without the column break in G?
Technical Director
www.infocat.co.uk
jjramirez
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

Post by jjramirez »

Hi,
Just on the off chance does it work without the column break in G?
The steps to reproduce de error:
  • 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
Results:
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
step1.png (75.75 KiB) Viewed 1762 times
step2.png
step2.png (76.59 KiB) Viewed 1762 times
step3.png
step3.png (33.43 KiB) Viewed 1762 times
jjramirez
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

Post by jjramirez »

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:
Attachments
step5.png
step5.png (65.87 KiB) Viewed 1756 times
step4.1.png
step4.1.png (62.25 KiB) Viewed 1756 times
step4.png
step4.png (60.85 KiB) Viewed 1756 times
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

If you put an apostrophe in the blank column you can probably keep your report layout working.
Technical Director
www.infocat.co.uk
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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

Post by jjramirez »

Hi Wim and Steve,
Thanks for your comments.

I was testing what you proposed, but I got the same results:
Attachments
test.png
test.png (59.43 KiB) Viewed 1726 times
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
jjramirez
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

Post by jjramirez »

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.
Wim Gielis
MVP
Posts: 3105
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

Post by Wim Gielis »

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
Post Reply