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.
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
Data lost in TM1 Excel
-
- MVP
- Posts: 2834
- 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
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.
-
- 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
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.
-
- 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
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
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
-
- MVP
- Posts: 3683
- 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
Yes. Remove the formulas by hardcoding the values. (Copy + Paste As Values).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?
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
-
- 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
Try turning off excel auto calculation and save the sheet.
-
- 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
@jorelb - 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).
@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).