Microsoft Office Implicit Intersection Operator "@" impacting TM1 "DBRW" Database Write Back Formula

[Putra_Bala]

Here is the link on Microsoft support page with regards to the implicit intersection operator (i.e. @ and {}) being automatically added to old Excel files created in older version(i.e. MS O365 32-bit Ver 1907) when opened in a newer Excel version(i.e. MS 0365 32-bit Ver 1912).

Although it states clearly in the Microsoft Support site that this change will not impact the functionality, but it does for TM1’s IBM Planning Analytics Local and Client Version 2.0.7 “DBRW” formulas with multi cells array especially during write backs to TM1 database

(i.e. “=@DBRW(T$21,T$22,$I$46,$I$47,$H65,T$23,T$24,T$25,T$26,T$27,T$28,T$29,T$30,T$59,T$60)”).

https://support.office.com/en-gb/article/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c9...

Any thoughts on how this can the default automatic implicit intersection operator "@" can be disabled on MS 0365 32-bit Ver 1912?

[#2]

@Putra_Bala 

I don't think it can be disabled, implicit intersection doesn't work in silent mode any more.

[#3]

@Sergei Baklan 

So how will TM1/IBM Planning Analytics DBRW formula work to write back to the database?

Are there no other organization facing similar issue? How do they work around this? Any interim solution? 

[#4]

@Putra_Bala Did you ever find an answer to this?  Did you open a case with IBM?

[#5]

Hi @JimGeraci ,

Yes, I got this resolved. Apparently you can do a find "@" and replace with "" on the impacted Excel report sheet/workbook. This was the solution provided by IBM.

I did open a case with Microsoft as well and did have some discussions with them about this.

They informed that they have switched-off the implicit Intersection Operator from being default in the future releases of Microsoft updates(Version 2003 - End of March 2020 onward).

Regards,

PB

[#6]

@Putra_Bala   of course IBM would tell you to find and replace *smh*

Good to hear microsoft will turn it off by default.  Thanks for the update :)

[#7]

Looks like it is still there in version 2006

[#8]

@JimGeraci 

I am not sure I understand why MS should turn off the "@" operator by default.  Their main focus should be to ensure that the new versions of Excel run old files and obtain the same results (backward compatibility).  If third party code reads or writes formulas, as opposed to values, then there is no promise that the development will not break the functionality (unless it uses a formally specified API).  It is up to the third party developer to rework their application.