TM1 Forum

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

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.

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.

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.

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!

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.

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.

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.

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

Yes it normally works but using O365 introduces risks...

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!

Just on the off chance does it work without the column break in G?

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:

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:

If you put an apostrophe in the blank column you can probably keep your report layout working.

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.

Hi Wim and Steve,
Thanks for your comments.

I was testing what you proposed, but I got the same results:

What happens if you fill the blue cell right underneath the left red arrow ?

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 don’t know the logic there but it sounds like a bug.