Active Form: Loss of "IF" function for dynamic formats

[John Zhang]

I'm using the Active Form in TM1 9.4.1. In the beginning (right after I installed the TM1 server & client), the "IF" function in column A worked well. It returns the level of the corresponding element of that row. Active Form sets the format for that row depending on the returned value in column A. However, after some time, the "IF" function was found broken. Even when I just tried to simply output a cube view to an Active Form on excel, the function in column A becomes a strange string "=D" and displaying with #NAME?, for all rows.

This has been happening for a long time, and in many environments I'm working with. I don't know from which exact time point this function was broken. And not sure whether it's related to un-installation and re-installation of TM1 on the same machine. But once the function broken, I have no way to bring it back even if I re-installed the whole TM1 software on that machine. Anyone has ever seen this problem? Can anyone give me some suggestions? Thank a lot in advance!

John

[Wim Gielis]

Hello John

I have never seen that one before, although I must say, with active forms we'll never have seen them all...

Does it occur with any cube? View? Is there something particular with the dimension(s) in the rows?

Are the functions related to the active form - TM1RPTROW adn the like - still okay?

Wim

[John Zhang]

Hi Wim,

Thanks for reply.

It seems to me this broken function is occurring to many kinds of dimensions / cubes / views, at least in two of the projects I've worked with.

The function is something used to determine whether the row element is a simple one or a consolidated one, and also what's the level in the subset used by that active form.

<quote from users guide>
Within the IF function, the TM1RTPELLEV function is used to determine the subset element level of a consolidated row element. This function is distinct from the ELLEV worksheet function. TM1RTPELLEV returns the level of an element within a subset, while ELLEV returns the level of an element in a dimension. For further details, see the documentation of the TM1RptElLev function.
<unquote>

One workaround is to find out what the exact original function was, and to replace the broken #Name? with that formula. It works for those customized active form reports. However I still cannot solve the problem when I'm using a drill function on excel reports. The "drill" on excel will always call a new sheet of active form. Without the correct IF function, all the data / cell formats lost.

[John Zhang]

It should be the problem of TM1 client. The function works well on one colleague's PC. However it remains broken on mine, connecting to the same TM1 server.

Is it possible related to re-installation of TM1 client?

[Wim Gielis]

What are the specific TM1 versions (and subversions)?

[John Zhang]

What are the specific TM1 versions (and subversions)?

I can at least specify two cases:
1) TM1 32 bit server = 9.4.1 (FP3); 32 bit client = Perspective 9.4.1 (FP3)
2) TM1 64 bit server = 9.4.1 (FP3); 32 bit client = Perspective 9.4 MR1

[jstrygner]

John,

This looks exactly the same like my issue with 9.4.1 SP2 I experienced around September 2009.
In my case it seemed the reason of a bug was obvious: It only happened when I was using Polish version of Excel (where "if" means "jeżeli"). With English version I had no problems.

I did make a bug report to IBM and the response I got was that Polish Excel version is not supported and it is not something I can easily get in a hot fix or sth.

In that implementation regular users did not create their own Active Reports. Admins knew that if they want to publish an Active Report they need to paste the correct function just like it is supposed to be (with all the "jeżeli"s and TM1RTPs).

Maybe your case also has something to do with Excel language version and if not, maybe some regional settings of the OS?

HTH

[John Zhang]

Appreciate so much your helpful reply......

I think I'm close to the answer now.

This bug happens to the TM1 client installed on 'Chinese' locale OS but doesn't occur on English locale OS.
So in summary seems the TM1 excel add-in (2003) doesn't paste the full formula to the A column in a non-English language locale.

John