DBRA Discrepancies (9.5.2)

[rfielden]

I have the following formula that works fine in one worksheet and not working in another. Any ideas what I may have missed or don't understand? One day I might pass Applix 101!

=DBRA("applixtm1:Asst_Plan_Color_Code",A3,"Desc")

[Mike Cowie]

Hi:

Just a guess here, but does the cell referenced (A3) have a numeric-looking element name that is formatted in Excel as a number or date? For example, "50100" or "Jan-2010"? If so, you may need to reformat the A3 cell as text, either by prefixing the element name with a single quote forcing Excel to render it as text, which the DBRA should like much better. When you slice from the cube viewer TM1 usually takes care of the element formatting for you, but if you've manually typed in something like an account number or time dimension element Excel has probably formatted it as a number or date because it thinks that's what it should do with anything that could possibly be a number, date, etc.

DBRA's are picky this way with number-formatted element names, but I don't think DBRW/DBR formulas are, which is why you may not have run into this as much, since you probably use DBRA's much less overall. I think date formats can give both types of formulas trouble unless you have an alias in your time dimension that matches Excel's numeric date value (even if you do have such an alias, DBRA's still won't handle it since it's a numeric value).

Hope that helps.

Regards,
Mike

[rfielden]

Mike, thanks as that did the trick and good knowledge to maintain. We had reformatted the cells a number of ways with negative result. Using the ' before the value did return the attribute. Thus, we recreated the worksheet with all cells properly formatted and reloaded with success. Thanks again and have a great weekend!

[tomok]

"Technically", all dimension elements are strings so it is best practice to have all cells in Excel, that reference elements, be formatted as text, unless the cell contains a formula. This includes XDI worksheets too. While you can get away with it in DBRs and DBRWs, you can't in DBRAs and who knows what else. Why tempt fate when you can avoid the problem just by formatting the cell properly as text in Excel.