Active Form results not consistent between TM1 web and Excel

[tosca1978]

Hi All,

I am using 9.5.2 and Excel 2007 with IE7 (or) Google chrome.

I have an active form that displays the committed costs by cost line down the rows and by 3 different currencies across the columns (Transaction currency/Base Currency/Project Currency). The transaction currency is the raw data coming from the business warehouse and the other two currency types are rule derived. All is working as expected in the cube and active form in excel.

Another thing to note is that there are a number of drop down combo boxes that allow the user to select the country/project etc with ELCOMP formulas displaying the results each time to give the user a refreshed list to choose from in the next drop down box. I have used this in many other areas of the model and it works fine. There is also a TM1 action button that merely rebuilds the active form after each selection (when it is clicked).

The problem:

When viewing the results in TM1 web there are inconsistencies in the results compared to those in excel. In excel there will be three different values (one per currency) for a cost line. In TM1 web the Project currency matches excel, however the Transaction and base currencies are displaying the SAME VALUES as Project currency. This error happens consistently.

What I have tried/checked:

1. All the results in the excel version are correct as per the cube, it is the TM1 web version that is displaying incorrect results
2. In TM1 web, I have repeatedly pressed the TM1 action button to rebuild the form to see if it refreshes the results to display the correct ones - it doesn't.
3. In TM1 web, I have repeatedly pressed the rebuild workbook and re-calc workbook buttons in the toolbar to see if it refreshes the results to display the correct ones - it doesn't.
4. I tried saving the application with auto-recalc turned on in excel (normally this is always switched off) - this didn't help
5. In TM1 web I have tried toggling the aut-recalc button on and off (pre and post step 4) - this didn't help
6. I have checked that the DBRW formulas are referencing the correct currency elements - I knew that they would be as the excel version is correct.
7. I have checked that the error occurs in both IE7 and Google Chrome and in the Citrix version we have. Note: Due to standard desktop I cannot upgrade to IE9 until the company does
8. I have checked that the DBRW results are not dependent on any cells which contain formulas that TM1 web does not support I THINK they are all supported:
a) the time element it references is a VLOOKUP formula > referencing an INDEX formula based on period selected from drop down.
b) Country and Project elements reference a cell with an INDEX formula that looks though a list of ELCOMP formulas.


If anyone can suggest any other areas that I can investigate I would be very grateful.

Many thanks

[declanr]

Judging from your detailed question I expect that you would have already noticed if this was the case but...
Your info bar states that you use Excel 2007 while the Q states that you are using 2003... you haven't created the sheet in one and then published in the other?
I found issues with the above on a 2010/2007 front.

[tosca1978]

Hi DeclanR,

well spotted...I have just updated the info bar to 2007! We moved from excel 2003 to 2007 about 5 months ago. At the time I rebuilt all of the active forms etc in excel 2007 because we had issues migrating them.

Thanks for trying though!

Cheers

[Gregor Koch]

Hi
Just throwing out a few random thoughts...personally haven't seen this issue before.

I would try to publish a vanilla Active Form that contains the three currencies in columns to the web and check the result.

Then I would replace the VLOOKUP (and the INDEX function) with according values, just for a test, to isolate that it is not those two. Although officially supported, there have been bugs with VLOOKUP on the web before (named ranges in VLOOKUP comes to mind, but that is a really old one).
The way I understand it neither of the functions has anything to do with the way the DBRWs are referenced to different currencies but I'd still try.

Are the different currencies in columns? If so, are they next to each other? Are the references in the DBRW to the currency fixed/fixed?
Are you using merged cells?

Double check whether all the functions are supported in TM1Web
http://publib.boulder.ibm.com/infocente ... E0415.html

I read you 'THINK' they are, but how many can you possibly use to come up with the references for the DBRWs, especially the ones for the currency?

Cheers

[declanr]

I think commenting on this thread has cursed me...

In one implementation I am also using 9.5.2 and Excel 2007.

An active form report when viewed in excel shows "Account Specific A&P" in one column and "Variable Costs" in the next.

In TM1 Web it reverses the values in these 2 columns! As far as I am aware this behaviour has only started to occur recently, sadly I haven't had much chance to investigate yet but I will have a crack at it over the next few days and see what happens.
Seems to be a similar issue but I am not using any functions in the page outside of the standard TM1 DBRWs etc so its not a vlookup issue for me.

[jim wood]

I've had problems when publishing excel sheets to TM1 Web when the client and server versions are not identical. The onlu way I found to get around this is to remove the excel sheet from applications and re-upload as a new sheet each time. It's a pain but it seems to work.

[jim wood]

Just to clarify what I meant. I do the following:

1) In persepctives open the excel sheet in excel
2) Remove the excel sheet from the applications
3) Upload the excel sheet as a new sheet in the same place as the old one
4) Rename the newly uploaded sheet

[declanr]

I know this is a bit late but I thought I should post the outcomes of my issue.

Whereas i had automatically assumed it was a Web vs Excel issue it actually turned out to be a gammy developing issue

I picked up a project that someone else had started off and left a few things they had developed in place, not much but a few things e.g. a process that would allow users to insert elements into a dim and some rules were left in an elementattributes cube.
Normally I am not keen on using rules in TM1's attributes or security cubes but they seemed to be doing the trick... testing however did not pick up on the fact that the TI process to add elements allowed them to be added in a format that caused the rule based aliases to create duplicate elements (non unique element names)

So it seemed that although fine in excel (which surprises me)... tm1web didn't know which element it should be referring to and somehow this results in some rather unusual behaviour in the web sheets.

So the fix was obviously creating a TI to hunt out and remove my duplicate elements and then far far far more importantly update the TI process to lock it down on only allowing insertion of elements in a very specified format.


Cheers for the pointers though.