TM1 Forum

I have noticed that if an Active Form in Excel is worked on while "offline" (not connected to TM1 Perspectives) and it is saved (before saving the workbook calculates, and because the user is not connected to TM1 then it obviously shows errors (keyerrors, etc)).

However, once it is saved offline, any time the file is opened again, there appears to be no way to refresh and rebuild the Active Form (by hitting Alt-F9). It seems to be forever faulty.

Note - I am on TM1 v9.5.2, and Excel 2007. This Active form is semi-complicated (references multiple cubes, etc) so I'd prefer not to have to rebuild it from scratch. I know all cells are referencing the correct fields, etc - it has something to do with saving while offline with the *KEY_ERROR's and it can never be ressurected afterwards.

Has anyone seen this before? Any thoughts would be appreciated.

Thanks.

scotcanuck wrote:This Active form is semi-complicated (references multiple cubes, etc)

Does that "complication" include nested DBR or DBRW formulas. By that I mean a cell contains a DBR or DBRW formula and then another DBR or DBRW formula references the first cell? If so, I would recommend opening up the Excel file again, this time connected to TM1, and recalculating (via F9, not Alt F9) and re-saving. If that doesn't work, go through each of the nested DBRs and DBRWs while connected, hit F2 to edit and then hit enter. After you've been through all the formulas then you can rebuild (Alt-F9) and then save. Not guaranteeing it will work but it has for me before in this situation.

tomok wrote:

scotcanuck wrote:This Active form is semi-complicated (references multiple cubes, etc)

Does that "complication" include nested DBR or DBRW formulas. By that I mean a cell contains a DBR or DBRW formula and then another DBR or DBRW formula references the first cell? If so, I would recommend opening up the Excel file again, this time connected to TM1, and recalculating (via F9, not Alt F9) and re-saving. If that doesn't work, go through each of the nested DBRs and DBRWs while connected, hit F2 to edit and then hit enter. After you've been through all the formulas then you can rebuild (Alt-F9) and then save. Not guaranteeing it will work but it has for me before in this situation.

Hi tomok,

Firstly, yes there are nested DBR and DBRW formulas, and I actually recently learned that DBR is more stable than DBRW, so I converted everything to DBR. And yes I have tried opening all the formulas in edit mode and hitting enter, and nothing.

I'm no expert, but it appears it's the TM1RPTROW formulas not getting rebuilt when Alt-F9 is activated.

scotcanuck wrote:I actually recently learned that DBR is more stable than DBRW, so I converted everything to DBR. And yes I have tried opening all the formulas in edit mode and hitting enter, and nothing.

I don't know what would give you the idea that DBR is more "stable" than DBRW. Both are just as stable but DBRW is much better performing wherever you have a large number or DBRW and/or you have any network latency to the server. It is almost always better to use DBRW over DBR. You just need to understand that as the client retrieves the results of all DBRW from the server in a single batch operation that you cannot have a DBRW formula feeding into another DBRW formula as an argument and any cells containing string values for "control parameters" that serve as element references for DBRW formulas must be retrieved by DBR as you cannot link DBRWs.

Once this is understood then you should not have any issues with DBRW. If anyone told you that you should always use DBR and not DBRW then this is bad advice and should be filed in the trash.

lotsaram wrote:

scotcanuck wrote:I actually recently learned that DBR is more stable than DBRW, so I converted everything to DBR. And yes I have tried opening all the formulas in edit mode and hitting enter, and nothing.

I don't know what would give you the idea that DBR is more "stable" than DBRW. Both are just as stable but DBRW is much better performing wherever you have a large number or DBRW and/or you have any network latency to the server. It is almost always better to use DBRW over DBR. You just need to understand that as the client retrieves the results of all DBRW from the server in a single batch operation that you cannot have a DBRW formula feeding into another DBRW formula as an argument and any cells containing string values for "control parameters" that serve as element references for DBRW formulas must be retrieved by DBR as you cannot link DBRWs.

Once this is understood then you should not have any issues with DBRW. If anyone told you that you should always use DBR and not DBRW then this is bad advice and should be filed in the trash.

Hi lotsaram,

Yes you are correct - the only advice I found online was that DBRW formulas being nested in Active Forms can cause incorrect data to be retrieved, so I converted all of these to DBRs. I did know about DBRWs being better performers than DBRs and almost always use DBRWs in my worksheets.

Thanks for clarifying my comments.