Protect DBRW fomrula in Perspectives

[TM1NB]

Hi,

I'am looking for a simple way to protect DBRW formula in perspectives. The cells must still be wirteable, so that Users can type in their plan values without the risk of deleting the DBRW formula in it.

Thanks
TM1NB

[EvgenyT]

Easiest way to protect your report is to upload it to Applications folder and make users access it from there.
That way they they can never amend "master copy", as they only ever deal with a temp copy. Even if they delete dbrws from temp copy it will have no impact on the shared report.


Thanks

Evgeny

[TM1NB]

Thanks for the quick response.
Yes, the report is already stored in the applications folder and can always be used as master. Nevertheless the requirement is unfortunatly like "there must be a possibility to always have the correct formula" in the backround without "repopen" the master. I guess they are scared to delete it by mistake and do not notice it, or just don't want to reopen the sheet... and I can comprehend it a bit.
I already thought about using DBS, so that they can write in an empty cell, but I can't find a way to bring this in my active report, beacause the active report logic passes the first value on the next row...

Thanks
TM1NB

[tomok]

beacause the active report logic passes the first value on the next row...

??? I'm not sure what you are talking about. The active report logic copies all the formulas from the first row (excluding the TM1RPTROW ones) down as far as it needs to for it to have a row for every record in the view the active report is built on. This means ALL columns, not just the ones that are part of the active form range. So, you could put DBS formulas in a column to the right and the active form would copy those all down. Then you just hide the columns with the DBRW formulas in them. The trick here is to figure out how to configure the active form so that it gives you the intersections you want for input.

[TM1NB]

Hi Tomok,

sorry for my bad description/english.
My idea was about creating a new column without any formulas (so there is nothing that could be deleted by mistake) with the intention to let them put their plan values in those new cells... But this is not possible because the value of the first cell (wether formula or just a value ) will be copied down (like you've written). So I can't do it that way. Hope you now understand my intention better.
Nevertheless I'm just looking for a way give users the possibility to input some data in an active form without the possibility that they can delete a formula in a cell they have to user for data entry.

[tomok]

I understand your intention very clearly and it can be done with DBS, just not in a way that is going to completely mimic the way it works with the plain DBRW formula. For that reason you should probably just tell your client it can't be done. I can pretty much guarantee you if they aren't happy with the idea of the possibility of accidently deleting the DBRW then they aren't going to be happy with how the form will work with the DBS setup that would be necessary to make it work. Tell them to use TM1Web if they want to have their forms protected from change.

[paulsimon]

Hi

I know what you mean. Users simply deleting DBRW formula is one issue. The worst case is where they start cutting and pasting and you end up with a DBRW alongside one row column combination that is actually pointing at a completely different row and column and it is therefore showing the wrong value and potentially loading data against the wrong elements.

Simply having a DBS alongside as a way of sending in the value that they enter won't work either as the formula can just as easily be corrupted by cutting and pasting, with the result that the value is sent into the wrong location in the cube.

I haven't had this issue for years as i have been using TM1 Web for data collection which, as everything gets converted to HTML means that there are no formula left to corrupt. TM1 Web has its limitation but it certainly solves that problem. The same can be said of PAW.

Years ago I worked around this with VBA, which if you are using Perspectives is an option. The basic approach was

a) Input sheet has no TM1 Formula. It is the only unprotected visible sheet. If selections are needed these are Combo boxes linked to another sheet that uses SUBNM dragged down alongside an incrementing index number to retrieve all values in the subset to provide the list of values for the Combo box. A better alternative if there are a lot of potential values is to use a button to drive a VBA Macro which runs the SUBPICK TM1 Macro to display the Subset Editor and which returns a result that the VBA Macro can then store in a cell on the Input sheet. There are then no formula that can be corrupted

a) Clicking the Commit and Retrieve button on the Input sheet does the following :

1) Copies what is on the data area of the input sheet to a Sending Sheet
2) The known area on the sending sheet has DBS formula on it that send the data into the right location in the cube
3) The VBA Macro recalculates that sheet to send the values into the cube
4) On a separate Retrieving sheet DBRW formula retrieves the data according to the title selections on the Input sheet
5) The VBA Macro recalculates that sheet to retrieve the revised values from the cube
6) The VBA Macro copies the revised values to Input sheet
7) The user can now amend the values on the Input sheet and click the Refresh button again. Since none of the values on the Input sheet are in anyway linked to formula there is no way that any amount of copying and pasting can corrupt the formula.

b) Clicking the Retrieve button on the Input sheet just does 4-6 of (b)

c) In the Workbook.open event, to provide the initial view, it runs the code behind the Retrieve button.

I used this just for a Slice. It would be a little more complex to do this for an Active Form since the rows are variable. Copying the row codes as well as the values from the Retrieving sheet to the Input and Sending sheets should get past that.

However, this is getting to be quite a complex approach. You need 3 sheets to manage one set of input. Sharing the VBA by putting it into an XLAM Add In can help. It also means that the sheets themselves do not have to be Macro Enabled XLSM workbooks.

If you only have a few input sheets in the system and a lot of reporting, this might be feasible, but if there are a lot of input sheets then it might become cumbersome. An alternative is to look at TM1 Web or PAW, or as it is at present with PAW a TM1 Websheet embedded in a PAW workbook.

If you want to stick to Excel as the user interface, other alternatives might be to use the Rest API to send and retrieve the data but that is likely to be just as complex as the VBA method, although it could potentially do everything with one sheet.

PAX is another alternative but you would need to use one of the pure MDX based report types such as an Exploration View, rather than the formula based report types otherwise you have the same issue. The downside of the Exploration View is that formatting is limited.


Regards


Paul Simon

[TM1NB]

Hi Paul,

thank you very much for your response!

Unfortunately TM1 Web is no option because of performance issues.
Guess I will try to handle it with the VBA approach.

Regards
TM1NB

[paulsimon]

Hi

Your profile shows that you are still on 10.2.2. If so, then I would consider upgrading to Planning Analytics. You can then use PAX which will give faster performance than Perspectives. I say this because if you are about to start writing VBA you might end up having to re-write it again when you move to PAX as the PAX VBA interface is quite different.

However, I am surprised that you have a problem with the performance of TM1 Web. If you haven't looked at it since the days when it was on ASP.Net, then I would look again. The newer Java based version is much faster. In our case because we deal with a lot of remote partner organisations our only options when we did most of our development were either be Citrix or TM1 Web because of the chattiness of Perspectives over a WAN and the need to go through firewalls, etc, which needs an HTTP transport rather than the pure IP of Perspectives. In general we find the performance of TM1 Web OK for most cases. It is only when people want to run very large extracts that there is a problem. For bulk uploads we jump from TM1 Web to small CSV upload process that we developed.

Regards

Paul Simon