TM1 Excel files with DBR formulas still must be saved as paste values? is there a workaround ?

[jboulay]

Any update on how to get around not having to paste values every excel workbook we send out that contains TM1 formulas?

This happens who user open files they see *KEY_ERR error.

What are the latest options below or other optiond?:

1) Use existing VBA macros? some end users don't like to use macros or click on macro button (You really can't people to click)..unless you make macro run automatically?

2) Any new macros or other solutions on other threads?

3) I read somewhere that with the CAFE version of
TM1, the system automatically retrieves data that contains DBR formulas and there was no need to paste values..is that correct?

4) How about new TM1 versions? do they have a work around for this issue?

[paulsimon]

Hi

There are three possible options that I can think of :

1) The In-Spreadsheet Browser which just puts in values and not formula. However, it is limited to cube viewer like layout and IBM are always threatening to remove support for it, so it is not really an option.
2) Use an XLAM which loads in just like the TM1 Add In. This then means that you don't need to have a Macro in every Workbook that has DBRs. Instead they can run the macro in the XLAM. The macro just needs to apply a select all, copy and paste special values to the Active Sheet, ie the one with the formula. You just save the sheet with the macro as an XLAM. You sound as though you know enough VBA to write this yourself. It is easy enough to create a Menu for the XLAM which then makes the option appear on the Add-Ins Ribbon in Excel. If not there are various utilities on the market that will do this and a lot more besides. A common but more complex requirement is to paste special value the TM1 Formula but to leave other Excel formula intact. With the XLAM option, however, you will need to send the utility out to all your users, but once they have it, they can then include it as an Excel Add In, and it will be loaded every time they start Excel, like the TM1 Add In. Alternatively, your IT Dept could distribute it as part of the user's profile in the XLSTART folder.
3) CAFE (PAX in Planning Analytics) has Exploration Views and Quick Reports which don't use formula and just put data in the sheet. However, they have some limitations around presentation. The three other report types still use formula, so you probably still need option 2.

Regards

Paul Simon

[jboulay]

Thank You paulsimon.

I'm referring to Ad-hoc Excel spreasheet files on desktops and laptops, not TM1 cubes/views that can be exported as in inspreadsheet browser..so option 1 or 3 would not work.

I'm sure everyone here knows the PAIN of having to save many files on a daily /weekly basis as paste values before we can publish them or email them out. This takes a lot of valuable..and sometimes we just forget and send files out with DBR formulas and the receiver see all error messages..it just look bad.

with macros limitations , you know, all files names , sheet tabs names would have to stay the same for them to work...this requires a lot of maintenance.

most of us have to send files to upper management personnel weekly and these individuals do not have the time, the technical ability to have to go to excel add-ins or ask them to copy paste information..they just want to see numbers ..that's it.


so IBM has Not come up with a "real" solution even in latest planning analytic for excel (pax)?

[paulsimon]

Hi

So is there a problem with option 2 ie the XLAM, or did you not understand it?

Regards

Paul Simon

[jboulay]

Hi,

I was thinking in the lines of not having to paste values to begin with.

What if we send out an ad hoc excel file (not a snapshot) , the receipient opens file, the macro run in background to values formulas, then the recripient makes changes to file and send it back to us for corrections? but now, his modified file loses all formulas? then we'll have to start from scratch to create a new file with updated dbr formulas.

As with Essbase (not to compare) when an Essbase Excel ad hoc file is sent to receipient, the receipient will see the numbers/figures whether or not he /she is connected to database.

I know TM1 works differently, but I wish it did not require that extra step of saving files as paste values. I guess that's what makes TM1 somewhat more flexible?

[paulsimon]

Hi

Again I and lots of other people have VBA that will take a sheet with DBRs and will generate a workbook for, eg, each Cost Centre in a subset list of Cost Centres. It can automatically paste special value these and email them out to the user associated with each Cost Centre. I have other code that will monitor an Outlook email account and when the user emails the updated sheet back in, it will automatically copy what they have sent in to a sheet which has DBS formula linked to it, and will send the updated values in to TM1.

It is not an approach that I like using very much because users can still manage to do some very strange things to templates that you send out, even if you protect the sheet, use Data Validation, etc. That can lead to problems when you try to send the values in again. I prefer to have the users directly type the values in to TM1, eg via TM1 Web. However, when you have a large number of occasional users it can be hard to justify the license cost for this. An alternative is to have users send in or upload CSV files and to process these with TI. This gives more scope for error checking on what has been uploaded, and importantly for clearing any existing data.

You mentioned Essbase - obviously the main drawback is that once you have got your data back into the cube you then have to run a calc script which means that no one can access the cube for hours. I used to work with Essbase, then I worked with TM1. I have never wanted to go back to Essbase. I spent a year with Analysis Services and went back to TM1. TM1 is far from perfect, but it is still better than the offering from other vendors.

Regards

Paul Simon

[babytiger]

If I understand your issue correctly...
1. don't want to pay for extra licensing costs, hence distributing excel workbooks to users via email.
2. would like to retain the excel formulas on workbook, so any changes can be fed back to TM1 when you response with updated workbook. but this causes issues, as users can either overwrite formulas and gets error showing in cell (since they don't have TM1 installed).

There are a few of methods I have experimented (and I am sure there are others):
1. Set workbook with hidden DBS formulas that linked to value cells (where value cells were original DBRs, but a macro to remove these DBR formulas). And the import process is just require someone with access to TM1/Perspectives to refresh the workbook.
2. Have TM1 output packets of data to a shared directory/FTP server (as text files), where user can use a macro to retrieve values. On value updates, data is saved back to the shared directory/FTP server for TM1 to pickup.
3. Setup a database table that can perform similar tasks as #2.

All workaround will need time to setup and implement (and training), which translates to cost. The question is, what's the benefit vs cost of each method.

[paulsimon]

Hi

Just to mention, one of the downsides of option 1 - sending out workbooks with DBS formula is that it is very easy for these to get corrupted if the users start to cut, copy and paste data around, which can result in the DBS picking up data from the wrong location. That is why I would recommend removing any DBR or DBS formulas from the sheets that are sent out, and then when the sheet comes back in, use a process to copy and paste the data over to a separate sheet that has reliable links to the DBS and then sending the data in. However, as I mentioned that will only cure the issue of links to DBS formula getting corrupted. It will not cure all the other things that tend to go on when you send out templates. Eg unless you lock the sheet if a user inserts a row, then your DBS can still end up picking up data from the wrong location.

TM1 Web is less susceptible to corruption but there is a license cost if you have user based licensing and a potential cost if you have CPU based licensing and want good performance with a lost of users.

Building applications to process CSV files etc also has a development cost

Another alternative is to build a web collection sheet in JavaScript but that also has a cost. However, if there is a large number of users, and the collection is regular then it might be worth doing.

Regards

Paul Simon

[tomok]

From your posts it sounds like you are using Perspectives. Have you not heard of the Print Report functionality in Perspectives that can take a report and generate either a PDF or range-valued Excel file out of it?

[jboulay]

From your posts it sounds like you are using Perspectives. Have you not heard of the Print Report functionality in Perspectives that can take a report and generate either a PDF or range-valued Excel file out of it?

Hello Tomok,

With the print report features in TM1 and PAX , I would still end up with 2 Excel files, 1 file with Tm1 DBR formulas and 1 file with ranged-values, which I wouldn't like.

Ideally, I would like one file that, when opened by the recipient end-user with TM1 access, the file would obviously shows real excel numbers since he/she is connected to the TM1database. If the end-user has NO Access, the same file would display real excel cell value numbers after recalc instead of getting
KEY_ERR.

It would be similar to essbase without
KEY_ERR.

[ndivine]

This is how conversion to static values is handled in the latest versions of PAx:
https://www.ibm.com/support/knowledgece ... tatic.html

Also, both Quick Reports and Explorations can be opened, refreshed, and sent to users without PAx and they will not get KEY_ERRs. Quick Reports have some flexibility for formatting, but are not as flexible as Custom Reports (standard DBRW report). Explorations are not really formattable.

https://www.ibm.com/support/knowledgece ... ports.html