Linking a DBSW to a DBRW cell

[philosman]

Hi,

I have a spreadsheet that has retrievals in it as DBRW formulae.

I then also have 'send' formulas as DBSW that link to the DBRW cells and try to send data to my cube.

However, TM1 seems to struggle with this and the values don't send. If I hard code one of the DBRW values it works fine, so I'm sure it is something to do with not being able to link back to a TM1 formula and send data based on that ??

Any help gratefully received

Phil

[philosman]

This feels like I am talking to myself, but anyway..........

It appears to work if I change my 'send' formula to a DBS instead of DBSW.

Could someone explain why ??

[lotsaram]

Phil - this is actually quite simple and explained in the documentation (.... somewhere).

The function of DBRW formulas as opposed to DBR formulas is that they are more optimised for reducing network traffic and the number of packets sent back and forth between client and server. In Excel manual calculation mode Perspectives gathers up all DBRW references on the calculation event and submits a single request and similarly gets all values back from the server in one message. In the meantime while the client is waiting to hear back from the server with updated values the cells are populated with a temporary value of "RECALC". For this reason you can't have a DBRW linking to another DBRW to obtain a dimension element reference as the address will be invalid during the recalc event. The same of course goes for DBSW linking to DBRW.

If you substitute in either a DBR or DBS then these formulas follow the standard Excel "one cell at a time" recalculation model and resolve their dependencies which avoids the error. Which in your case could be either from trying to send a value of "RECALC" to a numeric leaf cell or from having one or more internal element address references having a value of "RECALC".

Just be aware that DBR and DBS are by nature less efficient so you don't want to have too many of them in a sheet that is used over a network.

[philosman]

Thanks indeed for the explanation, that helps a lot