Number formatting with dash as zero / DataSpreadFailed

Post Reply
Posts: 48
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Excel Version: Office 365 x32

Number formatting with dash as zero / DataSpreadFailed

Post by Adam » 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?
Take care.

Post Reply