Reddy wrote:Hi,
Sorry, I have a very basic question which needs your knowledge; I hav an excel template (input) which is sliced from TM1, when I tried to copy values from one source to this sliced template sheet all the DBRW formulae are overridden with these values hence values are not updated in the cube. But, when I tried to copy a single cell value from other source (excel not linked to tm1) and pasted (using paste special) in tm1 template, then its working fine (DBRW is not overridden and value is updated in the cub).
So, is it possible to copy bulk values and paste it in our input tm1 templates (should not override DBRW formulae)?
Thanks for your attention
Reddy
Congratulations, you've discovered a "feature" that has ticked some of us (well, me at least) off for years. You'll find several-years-old threads on the subject in the old Applix archive. As you've discovered, you can copy a
single value and paste it into a DBRW formula, and it will act as if you've typed it in. That is, it will send the value up to the cube and leave the formula in place. If you do it with MULTIPLE cells, the DBRW formulas will be overwritten.
I have no problem with either of these behaviours. I have a problem with the fact that the behaviour is inconsistent between a single cell and multi-cell range, and more of a problem with the fact that Iboglix doesn't make this difference sufficiently clear to the end users with the result that the single cell behaviour is often a (sometimes unwelcome) surprise.
There are in fact tools which will allow you to do bulk copying; the Beachware / SPF add-in is one that immediately comes to mind, and if you search the Forum you'll find VBA code which will perform that functionality as well.
However, I do recommend AGAINST having DBRW formulas in an input template. It's far too easy to inadvertently delete them, with the result that the user may think that they've input a value when in fact all they've done is typed it into an Excel spreadsheet. As has been discussed in a number of other threads recently, a better approach is to use blank cells for the input, and have hidden and protected DBSW formulas to send the values to the cube. We take that further and have the DBSW formulas conditional on a flag in a cell being set; in that way the users don't have to worry about committing their numbers until they have them the way they want them.
However if you choose to have direct input to DBRWs, you're always going to risk having them overwritten.
.