Number formatting with dash as zero / DataSpreadFailed
Posted: Tue Feb 09, 2021 11:01 pm
Hi friends,
My preferred Excel number formatting in everything I do, TM1, Excel, balancing my checkbook, etc. is:
In Excel Speak = #,##0;(#,##0);-
In TM1 Speak = c:#,##0;(#,##0);-
Turns out using a dash as a zero works in all cases in IBM PA for Excel with Quick Reports, with one exception. When writing back, and only when linking to another cell that’s a zero, IBM PA for Excel will attempt to send a dash rather than zero to the TM1 server, resulting in a nasty DataSpreadFailed (143) error, which provides no context as to what the error is. Only stumbled upon this after an hour of trial-and-error.
My poor team has been consistently seeing this error and have devised a rather unnecessary work-around (in my opinion).
My plan is to log this as a defect with the IBM technical support team, but I'm not optimistic.
My question: Any experience with using a dash as zero in TM1 Format? Any other watch outs with regards to Format?
My preferred Excel number formatting in everything I do, TM1, Excel, balancing my checkbook, etc. is:
In Excel Speak = #,##0;(#,##0);-
In TM1 Speak = c:#,##0;(#,##0);-
Turns out using a dash as a zero works in all cases in IBM PA for Excel with Quick Reports, with one exception. When writing back, and only when linking to another cell that’s a zero, IBM PA for Excel will attempt to send a dash rather than zero to the TM1 server, resulting in a nasty DataSpreadFailed (143) error, which provides no context as to what the error is. Only stumbled upon this after an hour of trial-and-error.
My poor team has been consistently seeing this error and have devised a rather unnecessary work-around (in my opinion).
My plan is to log this as a defect with the IBM technical support team, but I'm not optimistic.
My question: Any experience with using a dash as zero in TM1 Format? Any other watch outs with regards to Format?