PAX Formula Issues

Post Reply
AskAndAnswer
Posts: 41
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

PAX Formula Issues

Post by AskAndAnswer » Thu Jan 09, 2020 10:41 pm

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
Community Contributor
Posts: 300
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: PAX Formula Issues

Post by EvgenyT » Fri Jan 10, 2020 4:40 am

Hi,

RE: @

Its a new Implicit intersection operator in Excel:

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

MarenC
Posts: 56
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: PAX Formula Issues

Post by MarenC » Fri Jan 10, 2020 8:38 am

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
Posts: 41
Joined: Fri Jun 02, 2017 6:35 pm
OLAP Product: Planning Analytics
Version: 2.0...
Excel Version: 2016

Re: PAX Formula Issues

Post by AskAndAnswer » Fri Jan 10, 2020 3:48 pm

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

Solanna
Posts: 35
Joined: Thu May 29, 2008 11:20 pm
OLAP Product: TM1
Version: 9.5.2 to 10.2
Excel Version: 2007 - 2013
Location: Redondo Beach, CA USA

Re: PAX Formula Issues

Post by Solanna » Thu Jan 16, 2020 7:32 pm

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
Posts: 35
Joined: Thu May 29, 2008 11:20 pm
OLAP Product: TM1
Version: 9.5.2 to 10.2
Excel Version: 2007 - 2013
Location: Redondo Beach, CA USA

Re: PAX Formula Issues

Post by Solanna » Fri Jan 17, 2020 8:07 pm

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

Post Reply