Pipe as a list separator and TM1RPTROW formula

Post Reply
User avatar
TheFlorian
Posts: 3
Joined: Mon Jan 21, 2019 4:19 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: various
Contact:

Pipe as a list separator and TM1RPTROW formula

Post by TheFlorian » Tue Nov 24, 2020 10:39 am

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?

User avatar
gtonkin
MVP
Posts: 900
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.9.4; PAoC 2.0.9.3
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Pipe as a list separator and TM1RPTROW formula

Post by gtonkin » Tue Nov 24, 2020 12:08 pm

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.

User avatar
TheFlorian
Posts: 3
Joined: Mon Jan 21, 2019 4:19 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: various
Contact:

Re: Pipe as a list separator and TM1RPTROW formula

Post by TheFlorian » Thu Mar 04, 2021 4:31 pm

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
MVP
Posts: 2800
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Pipe as a list separator and TM1RPTROW formula

Post by tomok » Fri Mar 05, 2021 1:51 pm

TheFlorian wrote:
Thu Mar 04, 2021 4:31 pm
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
TheFlorian
Posts: 3
Joined: Mon Jan 21, 2019 4:19 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: various
Contact:

Re: Pipe as a list separator and TM1RPTROW formula

Post by TheFlorian » Wed Mar 31, 2021 5:56 am

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.

Post Reply