Active form websheet not populating

[JamiseBondi]

Hi,

I've recently come across two active forms that are no longer populating in the web - they work fine in perspectives. I've read this thread posted by lotsaram but my situation is slightly different.
http://www.tm1forum.com/viewtopic.php?f ... orm#p30984

I have an active form that has one row dimension, two column dimensions and the rest are context dimensions (4 of them). When I use perspectives to rebuild/populate the active form it works perfectly but using the workbook in the web doesn't populate at all.

In the context menu I'm using three named ranges that lookup info from another tab in the workbook and the 4th dimension uses a SUBNM to choose a cost centre.
What I've found is that if I load the workbook into TM1 with all the context dimensions populated (so no blanks in any of the fields) the websheet works fine - it allows me to change my cost centre and repopulates as expected etc. I prefer loading the workbook up with one of the context dimensions empty such as
=SUBNM(TM1Server&":COSTCTR","subset123","","KEY") so that the form loads quickly and is empty on opening it. The user then chooses a cost centre and rebuilds the form to see the results. However in the web after selecting a cost centre the form won't populate (exactly the same form in perspectives is populating fine)

I'm finding this in two separate workbooks that used to work a month or so ago and I'm not sure what's changed.
In the web log I'm getting:
2013-07-12 13:26:08,880 [18] ERROR Applix.TM1.Web.WebControls.Websheet.ViewExData - an error occurred while building the new view
System.Exception: error in active report title dimension, index 3
at Applix.TM1.Web.WebControls.Websheet.ViewExData.BuildView(ArrayList tm1rowData)

There's nothing in the server log relating to this error so I think it's a web issue. I have done the following already:
looked through the named ranges, they look OK.
used an excel add-in (excessformatcleaner.xla) to remove excess formatting
removed any conditional formats on all tabs in the workbook
removed the current excel named ranges in that tab of workbook and hard coded the context dimensions (so eg. =TM1version is now 001A)

The last step was to go back to the cube view, dump a brand new active form and load this up to the server (no customisation of the workbook at all) and render it in the web - same result. It doesn't populate in the web but it does in perspectives.

Does anyone have any pointers that could help in hunting down the problem here? The uncustomised brand new active form when trying to rebuild spits out this error:

2013-07-12 14:07:16,258 [20] ERROR Applix.TM1.Web.WebControls.Websheet.TM1WebFormula - Last AR data row object was null, check for off 'used range' reference
2013-07-12 14:07:16,289 [20] ERROR Applix.TM1.Web.WebControls.Websheet.ViewExData - an error occurred while building the new view
System.Exception: error in active report title dimension, index 4
at Applix.TM1.Web.WebControls.Websheet.ViewExData.BuildView(ArrayList tm1rowData)

The context dimensions are identical and in the same order for original problematic workbook and the new uncustomised workbook so it's complaining about a company dim when it says "title dimension, index 3" (error from original websheet) and a cost centre dim when it says "title dimension, index 4" (error from new uncustomised active form)

Any pointers would be appreciated, thanks.

[AmbPin]

When this happens here we usually find that there is some calculated column/row label that has not worked as expected on the web.
Have you un-hidden any hidden cells so that you can check that they work correctly on the web?

[JamiseBondi]

Hi AmbPin,

There are calculated columns in the original websheet but the brand new cube view that I made an active form from doesn't have any customisation at all and it has the same problem rendering in the web so I'm troubleshooting the simpler form first to minimise the variables to look at but I appreciate your suggestion, thanks.
I had unhidden the original workbooks calculated cells and they weren't calculating in the web incidentally.

[tomok]

I can't speak to 10.1 on this as yet, but I ran across the same "issue" with 9.5.2. As a consequence, I never leave the SUBNM formula unresolved. What I started doing was putting a dummy element in each dimension called "Select a cost center", or "Select an account", etc. and had all my active form SUBNM formulas default to these. That way, when the form is opened the user knows to select an item from each of the Title dimensions and my forms work on TM1Web.

[JamiseBondi]

Hi tomok,

Thanks for that useful work around. I've implemented it and it works as you say so before I go and update a bunch of workbooks with this fix I have one more glitch that's still unresolved.

Again, in perspectives the workbook works as expected but in the web one of the column dimensions doesn't update and the web log displays this:
2013-07-16 09:14:16,729 [18] ERROR Applix.TM1.Web.WebControls.Websheet.TM1WebFormula - Last AR data row object was null, check for off 'used range' reference

The server log is empty (relating to this error). here's what I'm trying to do:

The image shows an excel named range being used which is defined by the user (using a SUBNM lookup).

Named Range.jpg (25.55 KiB) Viewed 13005 times

This named range is then referenced in the "Currency" column dimension in the TM1RPTROW formula here (Amount_Currency)


=TM1RPTROW($C$4,TM1Server&":ABC1","",Amount_Currency)


In perspectives this works fine and the "Currency" column changes to match whatever has been selected in the context menu "Currency" field.
I know this looks like duplication and in this instance it is but it's as per the clients request. This same concept of specifying a field in the context menu and that field being an excel named range which is used in other TM1RPTROW formulas has been used elsewhere in the past. Have you guys come across this as being 'buggy' to use in this way?

thanks.

[AmbPin]

Hmm,
Just tried that method myself and it works fine both In Excel & TM1 web.

Could you put another formulae on your sheet that refers to the named range to ensure that it resolves correctly?

[JamiseBondi]

Hi AmbPin,

I put another formula that is simply =Amount_Currency to see if this cell would update when the named range changed - it does. So when the named range changes to a new value and the sheet is rebuilt the test cell updates to the new named range value but the TM1RPTROW formula doesn't refresh itself with the new named range value and the same error appears in the error log (tm1web.log).
thanks for the suggestion.

[tomok]

If the intent is to just repeat the currency value selected above in each row of the report then why are you using a TM1RPTROW formula to do that? It's not necessary. You can just put the formula =AmountCurrency in the first row of the active form and when you rebuild it will copy that formula down as necessary.

[JamiseBondi]

In a similar form (this one makes more sense as I agree the previous one does seem unnecessary and will most likely change) you'll see here that the excel named range is actually being used as a subset input for the TM1RPTROW formula. When the user changes the named range the subset that the "Details" column uses changes accordingly.

excel named range input.jpg (21.12 KiB) Viewed 12962 times

=TM1RPTROW($C$8,TM1Server&":ABC1",CCTRDetail,"")

Here, the subset used for dimension ABC1 is a lookup in the CCTRDetail named range that the user selects above in the "Currency" field.

So to summarise:
(in this sheet) I'm using excel data validation to allow the user to choose between company and group currency detail.
This is an excel named range that the "Details" column uses as the subset for the "ABC1" dimension in the TM1RPTROW formula.

[JamiseBondi]

Hi Declan,

I saw your post in an email but can't see it in this thread (tried from a few PC's just to make sure it wasn't my browser.... not sure why I can't see it) so I'm pasting it in here:
declanr said:

Just a different "take it back to the basics" perspective here which you may have already highlighted in your posts... but there were a lot of words to skim through so I could have missed it.

When you are referencing TM1Server as an excel names range, it's not on a different sheet/tab by any chance is it?
As that would still work in excel but in TM1 Web (at least historically, it may have changed in v10) named ranges on other sheets/tabs won't pull through and in your case that would probably result in an empty active form through the web but a perfectly functioning one in excel.

To answer your question:
The way we setup the workbooks is to have a menu tab which has some global parameters such as server name (hard coded and hidden from users) as well as other global parameters that users can change on the menu tab like version and company codes (accessible through SUBNM functions). These excel named ranges are used in subsequent tabs so that we don't have to make these selections on each tab. This has worked for all the workbooks we have in production without a hassle.

In this particular tab of the workbook there is a named range called "CCTRDetail" that is unique to this tab and the value of this named range is (for example) Company Currency Detail which is the name of a subset used in the TM1RPTROW formula. There are 9 tabs in the workbook all using named range lookups for server name, amongst others, so I think this part of it is working OK but you may be onto something.... just not sure what yet

I know if I substituted the CCTRDetail for a regular subset like "Company Currency Detail" that it would work but then I'd have to make another tab to display the other subset info and the workbook tabs are going to double in number if use this approach.

Appreciate the pointer though Declan, thanks.

[declanr]

Hi Declan,

I saw your post in an email but can't see it in this thread (tried from a few PC's just to make sure it wasn't my browser.... not sure why I can't see it) so I'm pasting it in here:
declanr said:

Just a different "take it back to the basics" perspective here which you may have already highlighted in your posts... but there were a lot of words to skim through so I could have missed it.

When you are referencing TM1Server as an excel names range, it's not on a different sheet/tab by any chance is it?
As that would still work in excel but in TM1 Web (at least historically, it may have changed in v10) named ranges on other sheets/tabs won't pull through and in your case that would probably result in an empty active form through the web but a perfectly functioning one in excel.

To answer your question:
The way we setup the workbooks is to have a menu tab which has some global parameters such as server name (hard coded and hidden from users) as well as other global parameters that users can change on the menu tab like version and company codes (accessible through SUBNM functions). These excel named ranges are used in subsequent tabs so that we don't have to make these selections on each tab. This has worked for all the workbooks we have in production without a hassle.

In this particular tab of the workbook there is a named range called "CCTRDetail" that is unique to this tab and the value of this named range is (for example) Company Currency Detail which is the name of a subset used in the TM1RPTROW formula. There are 9 tabs in the workbook all using named range lookups for server name, amongst others, so I think this part of it is working OK but you may be onto something.... just not sure what yet

I know if I substituted the CCTRDetail for a regular subset like "Company Currency Detail" that it would work but then I'd have to make another tab to display the other subset info and the workbook tabs are going to double in number if use this approach.

Appreciate the pointer though Declan, thanks.

Yeah, I just deleted the post after putting it down as I realised that it was only an intermittent issue with referencing named ranges on others tabs (think it was particularly a problem if they were hidden) but it hasn't cropped up for a while so it looks like it's not much of an issue now. May have been specific to versions I was working on at the time I encountered it etc.

I've done very similar tasks to what you are doing here before and its always worked perfectly well.
The data validation that you are pulling down isn't based on DBRW results hidden away somewhere are they? It could just be the order the calculations are performed in if so.

Just out of interest, try creating a dimension with "company currency detail" and "group currency detail" as the elements in it (and any other of your subset names), then remove the data validation and replace it with a SUBNM to see what effect that has.
I'm not expecting it to do much since I have actually used data validation before in scenarios like this, quite often to chose a 1 or 0 and pass that through the View formula to control zero suppression.

[dmillerksu]

I know this post is a little old, but I just came across this same issue in a 10.1 environment. Excel was rebuilding the active form, but TM1 Web would not. I was also heavily using named ranges. I finally noticed that I had created a named range, but spelled the name incorrectly and corrected it afterwards. However, I failed to update that named range in the view statement. It still seems odd that Excel would rebuild the active form with an incorrect view statement. Anyways, just wanted to share my experience.