Active Form Losing named Ranges

[djoughin]

Hi

I'm new to TM1 and have got as far as creating a cube and an Active Form from it (Cognos Express Xcelerator 9.5)

I have added a chart to the active form using named ranges to define the data.

This works fine until I make a selection from one of the dimensions (I'm changing the country). It has the effect of altering the named ranges by putting #Refs in them). The actual position of the data is not changed, just the data.

If however I switch to manual Calculation before changing the country and then calculating after the change all is fine.

It's as though at some point the sheet referred to by the named range isn't there at some point during an Auto Recalc, hence the #refs.

Am I stuck with having to manually recalc all the time or am I missing something?

Thanks

David

[lotsaram]

By "manually recalculating" I am guessing you mean hitting Shift+F9 or F9 when Excel is in manual recalculation mode? Note this will not rebuild the active form. To do this you would uye the Alt+F9 keyboard shortcut, ... and if you do this then you will get the same issue as with automatic recalculation and your formulas linked to the data range of the active form will go to REF# error messages.

Why? you ask. Because the active form "owns the rows" associated with it's data range. As the rowset is dynamic (the list of elements can be swapped on the fly and responsds to zero supression and other filtering of the data set) it needs to be regenerated each time the active form is refreshed. This is accomplished by the TM1RPTDATARNGx named ranges being deleted and then re-created as part of the rebuild action. This will invalidate any charts that reference this range.

It is possible to link charts to the dynamic range but you have to use "advanced" Excel techniques such as setting the chart range as a data table or else using named formulas using Offset and-or Index to define the location of the chart input ranges.

Note:
1/ you should ALWAYS use manual recalculation with TM1 or Xcelerator
2/ if you are unfamiliar with the concept of "named formulas" check out Daniel Ferry's excellent Excel Hero website and blog

[djoughin]

Hi

Thanks for the reply.

I've now got to the stage of creating a new Active Form from my cube but have not created a chart.

I've just created two named ranges, one referencing cells within the data and the other outside the data range.

E.g =Sheet1!$C$58:$C$61 inside the data and =Sheet1!$DT$118:$DU$121 well ouside the data.

Now, without changing the data, with auto calc off and calculate when saving off I save the workbook and reopen it and the named range withinn the data has changed to =Sheet1!#REF! but the named range ouside the data is unchanged.

This also happens if I set the named ranges up and use the recalculate on the cxl menu.

The strange thing is I created an active form and chart from a different cube last week using named ranges and with auto calc on and it works fine. I can drill down etc and the chart follows without any problem. I've looked for anything I did differently but without success.

Is it possible that there is a setting somewhere that keeps named ranges unchanged even if they are referring (in this case temporarily) to an invalid range?

Being new to this I can only think I'm missing something simple. Time to sleep on it I think.

David

[tomok]

Time to sleep on it I think.David

There's nothing to sleep on. Whenever you "rebuild" an active form report, the rows inside the report area are deleted and re-added. If you were to reference some of those cells somewhere else in the report you'll end up with a bunch of #REF formulas after the form rebuilds. It's the same behavior you would get if you deleted the rows manually yourself and then added them back. That's why lotsaram suggested using the Excel offset formula if you can make the logic work.

[djoughin]

Hi

Thanks lotsaram and tomok for your replies which have pointed me in the right direction.

I've now changed my named ranges to only refer to cells well outside the data range such as :-

=OFFSET(Sheet1!$C$1000,-1000+235,0,53,1)

This refers to C1000 which is well outside the data range but the offset makes it point to the range I want (C235 to C287) so no more #refs.

Everything working fine now.

Thanks

David

[nedcpatm1]

I have the same #REF! problem and the Excel Offset function solves the problem. Check out this video: https://www.youtube.com/watch?v=kvXToxeiS6Y.

Ned