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.
PAX Formula Issues
-
- Posts: 41
- Joined: Fri Jun 02, 2017 6:35 pm
- OLAP Product: Planning Analytics
- Version: 2.0...
- Excel Version: 2016
-
- Community Contributor
- Posts: 324
- 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
Hi,
RE: @
Its a new Implicit intersection operator in Excel:
https://support.office.com/en-us/articl ... c999be2b34
RE: @
Its a new Implicit intersection operator in Excel:
https://support.office.com/en-us/articl ... c999be2b34
-
- Regular Participant
- Posts: 390
- 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
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
Then remove the @ from the formula, or at least remove the hyperlink
Maren
-
- Posts: 41
- Joined: Fri Jun 02, 2017 6:35 pm
- OLAP Product: Planning Analytics
- Version: 2.0...
- Excel Version: 2016
Re: PAX Formula Issues
I found the information about arrays, so my question is: is there a way to disable this functionality in Excel?
-
- 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
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
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
-
- 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
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
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
-
- Posts: 6
- Joined: Tue Aug 27, 2013 6:26 pm
- OLAP Product: TM1 10.2.2.3
- Version: TM1 10.2.2.3
- Excel Version: 2010
Re: PAX Formula Issues
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
https://techcommunity.microsoft.com/t5/ ... -p/1138367