@= in active sheet formula

[MarenC]

Hi,

We are seeing some strange behaviour in excel active forms. When we put in a formula and use a function it is inserting an @ symbol, so for example we see,

=@TM1RPTROW($E$10,.....)

Anyone come across this before?

Maren

Admin Edit : added search terms!
Klammeraffe , grisehale , snabel , apestaart , apestaartje , apesturtsje , apinanhanta , kissanhanta , miukumauku , kukac , majmun , arroba , zavinac , strudel, kanelbulle , snail , escargot , arobase , commercial at , chiocciola , shablul , dalphaengi , heliko, whirlpool , addeco , asperand

[Wim Gielis]

Hello,

A customer of mine reported it to me. He didn't see any adverse effects so we left it as is.

Wim

[dymoy]

Apparently Microsoft recently added that to their latest Excel upgrade patch and it's "The implicit intersection operator was introduced as part of substantial upgrade to Excel's formula language to support dynamic arrays". A link to their support page: https://support.office.com/en-us/articl ... c999be2b34.

We have noticed that the @ is affecting any uploading, particularly with AWS. Other than that, no noticeable issue. A ticket was opened with IBM by ACG.

[Wim Gielis]

Thank you, that's useful knowledge.
I wonder when Microsoft is going to roll out those new functions to those who are not part of the Insiders program. Like 99% of the Office 365 population probably.

[Steve Rowe]

In both PAfe and perspectives version 2.0.43 / TM1 Build 11.5 I can see that =@DBRW causes the type over write back to fail, instead just ending up with the value you entered in the cell?

Any ideas what release this was fixed in or does the defect still exist in the 2.0.9 series?

Cheers and Good Health too all.

[Alan Kirk]

In both PAfe and perspectives version 2.0.43 / TM1 Build 11.5 I can see that =@DBRW causes the type over write back to fail, instead just ending up with the value you entered in the cell?

Any ideas what release this was fixed in or does the defect still exist in the 2.0.9 series?

I can't speak for PAfe, but in Perspectives It still exists as of the base 2.0.9 release.

I created an active form using Excel for Office 365 version 16.0.12527.20260 32 bit. None of the formulas had an @ sign.

I manually changed one of the DBRW formulas in a slice to use the @ sign. When I went to type over it I got this:

Message.png (21.47 KiB) Viewed 10725 times

If I may misquote a line from Casablanca, "I'm shocked, SHOCKED to find that gambling is going on in here and that Microsoft has lied again."

"Your formulas will behave the same"... not so much, no. As you say, entering a value just writes over the formula now.

I've seen posts in the Microsoft Communities indicating that this is an issue for a lot of UDFs (User Defined Functions) which are essentially what tm1.xla add-in formulas are.

Cheers and Good Health too all.

Indeed. Though one of the many downsides of this whole Covid business is that I have to run a lot of things through my domestic interwebz pipeline rather than through the cavernous maw that is the office interwebz feed. (I miss my desk. That's kind of disturbing.)

Anyway, I'm still waiting for the 2.0.9 IF 2.1 update that I posted about this morning to arrive down the pipe. However I'm not expecting to see any change in behaviour once I finally do have it. IBM would need to be dragged kicking and screaming to make changes to Perspectives, and with the workaound being "get rid of the @ signs if they appear" I'd be gobsmacked if they did a fix for this.

[Steve Rowe]

As an FYI, this seems to clear by doing an edit replace of "=@" with "=" across all cells and sheets. I've not seen the "@" get re-introduced to date.

[Steve Rowe]

Thought I'd keep all the nonsense that arises from the at sign in one place.

Working in active forms in Pafe where we have the at in front of report row formula i.e.

=@TM1RPTROW($C$11,"Server:DimName",,,"Description",,$D$21)

If I edit (f2, return) this then Excel decides that this is an email address and super helpfully changes the format of the cell and adds a hyperlink out to the default mail client.

Thanks Clippy!

So not only have M$ decided to add the @ to all the formula, that functionality is tripping up another "wizard".

Any ideas how to turn this off? It's that time of the week when the repetitive strain of this is beginning to make the pot boil!

[MarenC]

Hi Steve,

I just removed the hyperlink from the cell and that seemed to sort it

Maren

[Bakkone]

I recommend everyone to turn off the automatic formatting of URLs and email in their excel proofing settings. This is just one more reason to why.

[Steve Rowe]

@Bakkone, must have missed this option, I did look, I'll look harder!
@MarenC, it just comes back on the next edit so Yes you have to remove it for sure but I was looking to stop it happening in the first place.

Removing the snail stops it from happening as well.

[Alan Kirk]

@Bakkone, must have missed this option, I did look, I'll look harder!

You know how MS likes to make life easy for you! They haven't buried it more than a couple of levels deep.

Options -> Proofing -> Auto Correct Options -> AutoFormat As You Type tab.

I do actually find this option useful more often than not for web links rather than addresses, but this is one in, all in.

Autoformat.png (49.97 KiB) Viewed 10505 times

[PavoGa]

As an FYI, this seems to clear by doing an edit replace of "=@" with "=" across all cells and sheets. I've not seen the "@" get re-introduced to date.

I'm having a time with this. The snail keeps getting reintroduced to the formula (=@DBRW) just as soon as a new value is keyed into the cell. Would this have something to do with calculation order?

[PavoGa]

As an FYI, this seems to clear by doing an edit replace of "=@" with "=" across all cells and sheets. I've not seen the "@" get re-introduced to date.

I'm having a time with this. The snail keeps getting reintroduced to the formula (=@DBRW) just as soon as a new value is keyed into the cell. Would this have something to do with calculation order?

Got this resolved somewhat. The snail keeps showing back up, but the DBRW formula is now writing values to the cube as expected.

The solution revolved around one of the dimension arguments. That argument, HRLevel, was a variable using this formula:

Code: Select all

=IF(OR(DBR(cubOrgAttr, pOrg, "Function") = "SVC", DBR(cubOrgAttr, pOrg, "Function") = "USI"), "Admin Director", "Director")

So, instead of referencing this variable directly in the DBRW ( =DBRW(pCube, arg1, arg2, HRLevel, arg4, arg5) ), made one cell =HRLevel, named that cell pHRlevel, changed the DBRW to =DBRW(pCube, arg1, arg2, pHRLevel, arg4, arg5) and Voila!, it started working. I wonder if it has to do with the conditional in the formula for HRLevel and it being directly referenced in the DBRW function, because pCube is =pServer&":CubeName" and the DBRW works just fine.

Anyway, had not seen an explanation along this line so added for future reference.