Data lost in TM1 Excel

Post Reply
LoganNSE
Posts: 22
Joined: Fri Nov 18, 2011 10:19 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2003 2007

Data lost in TM1 Excel

Post by LoganNSE »

Hi,

I have an issue in saving data in excel i.e. when connected to server, i can see data (using TM1RPTVIEW/TM1RPTROW/SUBNM/DBRW) and able to save it however when I close and re-open the excel, I was not able to see previously saved data. All data retrieved via DBRW was gone and I can only see blank cells.
TM1 data lost on closing and reopening the excel - need data offline
TM1 data lost on closing and reopening the excel - need data offline
TM1Excel_Issue_DataLost.jpg (62.98 KiB) Viewed 4370 times
I would like to see data even when I am offline in the same excel and when needed, I need to connect to the server and update data.

Thanks,
Logan
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Data lost in TM1 Excel

Post by tomok »

For DBRW formulas to display data in Excel you have to 1) have the Perspectives add-in loaded and 2) be logged into the TM1 server that the formulas point to. If you are "off-line" the formulas will result in a blank cell.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
PlanningDev
Community Contributor
Posts: 349
Joined: Tue Aug 17, 2010 6:31 am
OLAP Product: Planning Analytics
Version: 2.0.5
Excel Version: 2016

Re: Data lost in TM1 Excel

Post by PlanningDev »

Is this an active form? Ive seen issues with adding DBRW formulas of your own that reference the TM1RPTVIEW function which appears to be the cube name visually. Changing the cube name reference to a hard coded cell or something else seemed to work for me.
LoganNSE
Posts: 22
Joined: Fri Nov 18, 2011 10:19 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2003 2007

Re: Data lost in TM1 Excel

Post by LoganNSE »

Hi,

Thanks!! I tried harcoding the cubename however it ended-up in blank cells on reopening the excel.

Is there any workaround to save the data and make it available in excel offline?

Thanks,
Logan
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Data lost in TM1 Excel

Post by lotsaram »

LoganNSE wrote:Thanks!! I tried harcoding the cubename however it ended-up in blank cells on reopening the excel.

Is there any workaround to save the data and make it available in excel offline?
Yes. Remove the formulas by hardcoding the values. (Copy + Paste As Values).

I don't know why you would possibly expect anything different. TM1 is a client-server application, you need to get your head around the fact that the data resides on the server and therefore Excel is demoted from being the database to being a browser. Normally this is seen as an advantage as all the data is in one place and everyone is therefore looking at the same figures which update centrally, moreover security can be applied so that people only see the data that they are entitled to. However this does of course mean that data is only available 1/ if you are online and 2/ if you have the security rights to see the data in question. If you need an "offline copy" then the values must be hardcoded
jorelb
Posts: 71
Joined: Fri Feb 13, 2009 1:41 am
OLAP Product: IBM Planning Analytics Cloud
Version: 2.0.9 IF (2)
Excel Version: 2016

Re: Data lost in TM1 Excel

Post by jorelb »

Try turning off excel auto calculation and save the sheet.
LoganNSE
Posts: 22
Joined: Fri Nov 18, 2011 10:19 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2003 2007

Re: Data lost in TM1 Excel

Post by LoganNSE »

@jorelb :idea: - thanks a lot, it worked. Go to Tools -> Options -> calculation tab and turn on Manual instead of Automatic.

@lotsaram - our users integrate TM1 excel data with other application to form a finacial report by inserting a new worksheet in the same excel file and via VLOOKUP formulas. TM1 data will change more often and they want to retain till new data comes in another application, hence we are in a stage to keep the old data in excel till they want it to be updated (manually by pressing a refresh button).
Post Reply