TM1 Forum

Hello
I have a user that his Excel formulas (DBR, DBS) working intermittently.
When the formula is not working it shows KEY ERROR
Does anyone know the source of the problem?
For all the other users in the same model, there is no such problem.
Thanks
Shelly

shellyk1980 wrote: I have a user that his Excel formulas (DBR, DBS) working intermittently.
When the formula is not working it shows KEY ERROR
Does anyone know the source of the problem?
For all the other users in the same model, there is no such problem.

Really DBR, or DBRW? The metaphorical 99% of such cases occur where:
- You are using a DBRW formula to return a value (which we'll call "the value formula"); and
- One of the element names that you are feeding to that formula is itself derived from another DBRW formula (which we'll call "the element formula").

Since DBRW formulas are processed in batches and not individually, the element formula's result usually isn't known at the time that the value formula is calculated, resulting in a key error. However whether you get it or not depends to a large extent on the order of calculation (particularly in complex workbooks), which is why it can appear to be intermittent in some cases. (And why simply going to the value formula's cell and pressing [F2] [Enter] will yield the correct value, and why tools like TM1 Tools' formula tracer won't reveal any errors; when you calculate the value formula by itself and not as part of a standard [F9] recalculation, it's fine.)

The solution is to ensure that the element formula uses a DBR and not a DBRW so that it is calculated separately before the DBRWs are.

If that's not it, you would need to supply substantially more relevant information than you have.

Thanks for the detailed answer
The thing is, that I always uses DBR and not DBRW
In addition DBS formulas do not always work for the same specific user
I have no Additional information to give, because when we both try to retrieve the same data exactly, I can and he sometimes succeeds and sometimes fails.
I feel that there is a conflict with Excel plugin or software installed on his computer
Sound familiar, by any chance?
Thank you
Shelly

shellyk1980 wrote:Thanks for the detailed answer
The thing is, that I always uses DBR and not DBRW

Then you probably have some painfully slow reports, but that's a different conversation.

shellyk1980 wrote:In addition DBS formulas do not always work for the same specific user
I have no Additional information to give,

Nothing like:
- Structure of the cube?
- Nature of the elements fed to the DBR, whether hard coded or derived by formula?
- If the latter, what the formulas are?
- Whether there are any inter-sheet or interworkbook formulas involved?

Just for four examples...

What leads me to think that the problem is only with this user, is that other users do not report problems.
The element that I try to get is hard coded.
When I create it in my Excel (in my computer) , the formula is working in both computers (his and mine), when he creats the formula (on his computer) it is not working for both of us

shellyk1980 wrote:What leads me to think that the problem is only with this user, is that other users do not report problems.
The element that I try to get is hard coded.
When I create it in my Excel (in my computer) , the formula is working in both computers (his and mine), when he creats the formula (on his computer) it is not working for both of us

Other pieces of additional information that come to mind without too much effort are:
- What the user's calculation mode is;
- Whether you have tried re-starting Excel;
- Whether you have tried restarting Windows.

I would hope you have definitely tried the second of the ones above.

One other thing to try is running the following command in VBA This would clear the user's TM1 cache, though shouldn't be necessary if you've restarted their Excel session.

Hi
the malfunction is repeated for several weeks,
Of course the Excel has been initialized and the computer itself.
the calculation mode is always manual.
I really do not know what the problem is
Shelly

Are there maybe any subsets (private or public), with names that are exactly the same as any of the dimension elements refererenced in the report? TM1 has an undocumented feature that means you can reference a subset name in DBR(W) and TM1 will resolve it automatically to return a correct number. Maybe the user has a private subset that is being picked up like that instead of a dimension element? Or something similar...

I went through all the subsets,
There is no subsets with names that are exactly the same as any of the dimension elements refererenced in the report