PAfE DBRWs overwritten on pasting of values

Post Reply
User avatar
gtonkin
MVP
Posts: 863
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

PAfE DBRWs overwritten on pasting of values

Post by gtonkin » Sat Nov 28, 2020 2:12 pm

Hi, Curious to see if anyone else is experiencing the same issue or having the same problem with DBRW formulas being overwritten in dynamic reports when copying and pasting.

I am trying to mimic how some users will be sending data to TM1 based on their own workings that they may have in various workbooks. Typically they would copy and paste into the template.

The image below shows a very simple dynamic report with a few products on rows and months on the columns with a full year on the end. Formulas are straight DBRW's no other functions, nesting etc. PAfE is 2.0.59, TM1 11.8.100.13
PAfE Formula Overwrite 01.PNG
PAfE Formula Overwrite 01.PNG (12.51 KiB) Viewed 371 times
The row with static is simply numbers 1 through 8. The row below has formulas referencing the cell above e.g. =J13.

If I copy and paste the static numbers into the report, I get what is shown against # Product, in the above.
If I copy and paste the formula based numbers into the report, the formulas are pasted into the report overwriting the DBRWs and thus referencing the cell above, per yellow highlights below.

Design Mode is not enabled - have toggled and re-tested a few times to be sure. Working with Automatic Calculation enabled, Custom Report - Refresh data on writeback = None.
PAfE Formula Overwrite 02.PNG
PAfE Formula Overwrite 02.PNG (13.95 KiB) Viewed 371 times
If I delete all rows except the first and rebuild, like the recreate, and repeat the same test by copying the formula based row but paste as values, I generally have the DBRW's disappear, 1 or more may be retained, at least one but sometimes more, never all. The graphic below show Dec onwards as missing. Note that the value in FY is 36. Generally, it appears like the values were sent to TM1 and are in the cube, but not always.
PAfE Formula Overwrite 03.PNG
PAfE Formula Overwrite 03.PNG (14.28 KiB) Viewed 371 times
If I recreate the book, the DBRW's are regenerated from the top row as expected and the values appear expected.
If I paste the formulas as values again, I get the same behaviour albeit with different cells missing formulas. (There was another screenshot but cannot seem to add file here. Shows Nov and Mar as blanks, other months with values.)

This behaviour is a bit erratic but is reproducible on my side after a Rebuild Sheet.
- Start with your template i.e. only the TM1RPTRow row, delete any previous rows from the data range that may be in your report.
- Click the Rebuild Sheet icon to generate all rows.
- Copy values that are formula based, could be from another workbook, sheet or any rows with values in the report you just refreshed.
- Copy and paste as values into a row within your report, not row 1 preferably.
- You should see the behaviour I am if you are able to replicate.

I cannot think that you would need to Recreate Workbook everytime you want to rebuild the sheet.

Keen to hear if anyone else is having this issue, has seen it or can reproduce. If so, will log as a defect via APAR.
Thank you.

p.s. Posted this on the IBM Community Forum to see if there is additional feedback there.

Post Reply