Page 1 of 1
DBRW returns 0 instead of a value or #N/A
Posted: Wed Apr 15, 2015 6:09 pm
by roymathew1
We recently upgraded to 10.2, and this is on an excel template that we used in 9.5.2 and continued to use in 10.2.
The DBRW formula contains an account "number": 1111, and the value we expect to see in the cell is "950". It returns a "0" until we add an apostrophe before the account number. Now, it used to throw either a "#N/A" or "*KEY_ERR" before and it helped us to go reformat the cells or add the apostrophe to fix the formatting issue. And this showing up as 0 now is throwing the totals off and we are now having to dig through to identify which cell is wrong.
Has anyone seen this with 10.2? Any input would be greatly appreciated.
Re: DBRW returns 0 instead of a value or #N/A
Posted: Wed Apr 15, 2015 8:59 pm
by dharav
@roymathew1:
I am replying it based on what i understood about your issue:
DBRW fromula contains cell value. Like = DBRW($A$1,$A$2,$A$3,$A7,$D5)
You should check following things which could cause that result:
1) DBRW formula reference correctly (with respect to NA or Key Error)
You can slice the same view through ISB and check whether your reference elements are proper or not.
2) Is it consolidation level (as you saying totals are off )for which you are retrieving the data?
If so, than
=>check feeders. If rules are not fed properly than you can get 0 at total level or at consolidation level.
Thank You
Dharav
Re: DBRW returns 0 instead of a value or #N/A
Posted: Thu Apr 16, 2015 10:28 am
by Steve Rowe
Have you tried alternative approaches to track these problem cells, the function T() should do the job. =If(T(a1)=a1 ,"OK" , "Fix") should return Fix for numeric cells
Re: DBRW returns 0 instead of a value or #N/A
Posted: Thu Apr 16, 2015 4:50 pm
by roymathew1
Thanks dharav. Let me clarify- I got the function and syntax right. Taking your example, DBRW($A$1,$A$2,$A$3,$A7,$D5) - I have a numeric reference in the formula, lets say $A$2 is referring to an account number which is formatted as a number: 950. In the previous versions on TM1/ Excel, the cell which has the DBRW formula would have returned a "#N/A", or *KEY_ERR" until we re-format the number as a "text" or add an apostrophe to make $A$2 as '950 - which usually takes care of the problem. In 10.2 the cell with DBRW formula returns a zero (0), a wrong value, which makes you think that the spreadsheet behaved properly, until we look at the totals and identify that there is a mismatch.
Steve,
Thanks. Yes, can identify the numeric cells that way, but doesn't solve the problem.
Version that we are on currently:
TM1: 10.2.2
Excel: Office 2010