PAX Formula Issues

[AskAndAnswer]

I am experiencing the whole bunch of issues in PAX, and since they are inconsistent, I suspect they might be related to Excel.

First, when a dynamic report is created in PAX from a cube view, every DBRW, SUBNM or report view related formula gets "@" in front of it. The report works with "@" or without it if I replace it with the blanks.

If I publish the report to TM1 Web, every functional field is formatted as if it's a "send to" email link. Then if I open TM1 Web report in PAX again, every formula has {} around it, so it looks like this: {=DBRW(..., ..., ..., ...)}. However, {} disappears if I click on the formula in the formula bar, so it looks like it's just formatting.

Any ideas?
Thanks.

[EvgenyT]

Hi,

RE: @

Its a new Implicit intersection operator in Excel:

https://support.office.com/en-us/articl ... c999be2b34

[MarenC]

The {} indicates every formula is treated as an array, get rid of every {} around every formula.

Then remove the @ from the formula, or at least remove the hyperlink

Maren

[AskAndAnswer]

I found the information about arrays, so my question is: is there a way to disable this functionality in Excel?

[Solanna]

My customer is having this same issue with the @ sign in front of the DBRW formula
The customer is using Office 365 which is set to auto update... that's another issue but not the critical issue at the moment
This update happened last week

As mentioned Microsoft has upgraded their formula language to assume every formula that has multiple cell references MUST be a dynamic array
NOT!

Here is the topic from Microsoft:

Upgraded Formula Language

Excel's upgraded formula language is almost identical to the old language, except that it uses the @ operator to indicate where implicit intersection could occur, whereas the old language did this silently. As a result, you may notice @'s appear in some formulas when opened in dynamic array Excel. It's important to note that your formulas will continue to calculate the same way they always have.

Unfortunately, it's not that simple
First issue... you cannot write into a DBRW when the =@ is present
Also, if an organization has different versions of Excel (for example, 2010 and 365) then depending on which version saves the file will determine the cell contents...

For example, if you have a spreadsheet that was created in a previous version and you open it up to the latest 365 version you will now see =@ before the DBRW
However if the spreadsheet was created in the latest version which would not have the =@ and you open it in 2010 all of the DBRW formulas will be in braces {} which in Excel identifies the formula as an array...

I've only done limited testing on this

Has anyone else experienced this issue?
Also, can someone who has experienced this issue and has access to IBM report this issue?
I don't have that capability

Thanks,

Solanna

[Solanna]

Quick update...

The IT folks were able to roll back the version of Excel 365...

Here is the information for anyone interested:

They tried to do a rollback to 2019 December 10 Version 1911 but the issue persisted. So they roll backed to 2018 August 14 Version 1807, which was before the implicit intersection operator(@) was added to Excel in September 2018

Thanks,

Solanna

[kool2zero]

I saw this message on the board and one of the users says that the implicit intersection was removed in version 2003.

https://techcommunity.microsoft.com/t5/ ... -p/1138367