Pipe as a list separator and TM1RPTROW formula

[TheFlorian]

One of my customers is currently getting some training in PAX and we ran into a peculiar error message.
One of the employees there is using the pipe symbol (the vertical line) as the standard list separator in his local Windows installation. When he is changing any argument in a TM1RPTROW formula, Excel will refuse to accept that. The error message is the same as when you use the wrong list separator (comma vs. semicolon for example).
When he changes the Windows standard list separator to the semicolon, his TM1RPTROW formulas work fine.

Has anyone seen similar issues and knows of a solution?

[gtonkin]

Seen it many times - it almost seems like the code behind PAfE and Perspectives in the day looked for commas to separate arguments in the functions.
I often work with Pipes too and get caught out on various formulas, slicing etc. It crashes/debugs and I realise the error of my ways.

Windows+R, Intl.cpl and change it back.

[TheFlorian]

What IBM have now said on this topic:

After performing series of test this issue turned out to be a defect. I've created APAR under below name:
PH34953 PIPE AS A LIST SEPARATOR MAKES IN EXCEL TM1RPTROW FORMULA CAUSE ERROR
Now we're waiting for IBM Software Team update. Thanks for collaboration.

[tomok]

What IBM have now said on this topic:

After performing series of test this issue turned out to be a defect. I've created APAR under below name:
PH34953 PIPE AS A LIST SEPARATOR MAKES IN EXCEL TM1RPTROW FORMULA CAUSE ERROR
Now we're waiting for IBM Software Team update. Thanks for collaboration.

Better to just avoid using reserved characters in TM1 in reports and objects (elements, dimensions, cubes, etc.). The pipe character (|) is reserved for concatenating strings in TI processes.

[TheFlorian]

Just to keep all of you informed:
IBM see this as an excel-bug and I am in no position to suggest or even prove otherwise.
Of course it's disappointing as my client has been using the pipe-symbol as the list separator without any problems so far. It's just that one PAX/Perspectives-formula, which won't work.

IBM:
<<Hi Florian, Dev team got back and the indicated this is an Excel limitation. The application logic to handle the formula never gets triggered when it is changed. The application has listener logic to process every formula which should get triggered by the Excel runtime when changes are detected. In this case, this never gets triggered when pipes are used. This is also very well evident by the Excel alert. So this is an Excel bug/ limitation. On a general note, we haven't seen pipes being used in practice. Also a side note, they debugged the code path in the working case (when pipe is not used) and compared the formula construction to the non-working case (when pipe is used). they didn't see any application flaws here meaning the formula is constructed properly with the expected args. This is an Excel bug/ limitation. Please review and let me know if this helps. Thanks.>>


An a quick note from myself to gtonkin and tomok:
Thanks for your participation.
PAX works nicely with at least two different list separators (comma and semicolon).
We were not using a TM1-reserved character or expression inside TM1. TM1 seemed to react incorrectly to a property of the operating system.