TM1 Forum

Hi All,

I have went through few of earlier posts to understand flexibility in using TM1RebuildOPtion.
I have deleted TM1RebuildOption from Name Manager in Excel, instead of having value as either 0 or 1.

We haven't heard any issues from users, but will like to know if anybody encountered any issues.
Can someone please share knowledge?

Regards,
Deepak Jain

And why do you delete it ? What do you want to achieve ?

Hi Wim,

While working with multiple forms, I deleted it by mistake in few forms, and didn't created TM1RebuildOption in Name Manager again.

Regards,
Deepak Jain

I would prefer to create the named constant again in the Name Manager, and stay as close to the original Active form as possible.

Note: I do not like Active forms and would like to not touch them at all, if possible.

Wim Gielis wrote:Note: I do not like Active forms and would like to not touch them at all, if possible.

OK, do tell... why is that?

I regard them (aside from the occasional glitch I'll admit) as one of the best things Iboglix has done, once you understand how to work them and manipulate them. (And don't go overboard with forms that run to tens of thousands of rows, admittedly, though even then I've found the performance of large forms to be quite good.)

However someone with your experience obviously wouldn't say that for no reason, so you've piqued my curiosity.

Thanks Wim,

Now we have created Active Forms, where business users access them using TM1 Web as an application.
Will it have any impact for not having TM1RebuildOption in TM1Web?

May be in next release we may add them, but for now I will like to understand what it goes with if we don't have TM1RebuildOption.

Regards,
Deepak Jain

deepakjain2020 wrote:Thanks Wim,
May be in next release we may add them, but for now I will like to understand what it goes with if we don't have TM1RebuildOption.

I have never deleted it and doubt many people will have but the basic concept is that having it set to 1 causes the sheet to do a rebuild on opening; if it isn't there it won't rebuild - so for example if your active form is saved with a subset of 10 elements; then you add 2 more elements to that subset - next time you open the sheet in excel it should only have the original 10 elements until you press the active form rebuild button in the toolbar.
That is what should happen in excel but like I say I have never thought to remove it so i don't really know for definite what would happen and I'm not sure if the web uses the same functionality (I have a suspicion it would just do a rebuild by default anyway.)
Based on the info though it wouldn't take more than a couple of minutes for you to test the impacts in said different scenarios yourself; if you do, it would be interesting to hear what you find.

Hi Declan,

Here are few things, I should list

1. We are having an Active Form.
2. We kept only the Rows having TM1RPTRow formula.

So, Yes, whenever we open in Excel we see only one row which have TM1RptRow formula i.e. as we saved.

I added element to subset, and those were not showing in excel until we refresh.

But, sheet in TM1Web was showing new element in subset when we loaded it.
So, I am presuming TM1Web will recalc automatically when we are opening an application.

Regards,
Deepak Jain

Alan Kirk wrote:

Wim Gielis wrote:Note: I do not like Active forms and would like to not touch them at all, if possible.

OK, do tell... why is that?

I regard them (aside from the occasional glitch I'll admit) as one of the best things Iboglix has done, once you understand how to work them and manipulate them. (And don't go overboard with forms that run to tens of thousands of rows, admittedly, though even then I've found the performance of large forms to be quite good.)

However someone with your experience obviously wouldn't say that for no reason, so you've piqued my curiosity.

I think that the main issue is about zero suppression. When refreshing an Active form (AF for short), it seems that it takes the "situation"/data from the moment when the AF was created (Cube viewer and choose a to create an AF) for hiding or showing rows.
With that I mean, say you have customers in the rows and 1 element (measure or month for example) in the columns.
When changing the column element to a different element within the same dimension, it seems to do the zero suppression based on the values of the original measure/month.
So changing May to June would not show the correct suppressed rows for June but rather those for May (the selection used when creating the AF in the cube viewer).

Or changing SUBNM's in the title elements seems to not lead to a correct display of rows, whereas the cubeviewer does it correctly.

Does this ring a bell ? Sorry for not being very clear

Or, also, pressing F2 > Enter on a DBRW( ) cell in an Active form shows zero ( 0 ), whereas in the Cubeviewer or a "Rebuild of the sheet" it displays the correct value.

And so on.

I am not talking about DBRW's referencing other DBRW's.

The biggest issue seems to be to change an AF afterwards (after a couple of weeks for example). Or when the AF is not equal to what we have in the cubeviewer, then the issues start (in my opinion).

It makes me mad, such that whenever I encounter the slightest issue, I tell the customer to use a static slice, or to completely rebuild the AF from scratch. And then often it does not resolve the issue althogether

Hopefully you can enlighten me on AF

Wim Gielis wrote:

Alan Kirk wrote:

Wim Gielis wrote:Note: I do not like Active forms and would like to not touch them at all, if possible.

OK, do tell... why is that?

I think that the main issue is about zero suppression. When refreshing an Active form (AF for short), it seems that it takes the "situation"/data from the moment when the AF was created (Cube viewer and choose a to create an AF) for hiding or showing rows.
With that I mean, say you have customers in the rows and 1 element (measure or month for example) in the columns.
When changing the column element to a different element within the same dimension, it seems to do the zero suppression based on the values of the original measure/month.
So changing May to June would not show the correct suppressed rows for June but rather those for May (the selection used when creating the AF in the cube viewer).

I've never seen the other issues that you describe (after all, a DBRW is a DBRW and should always return the right value, whether in an active form or not), but I can guess this one. It's most likely a design issue.

When you create an active form from a view you have two choices for your rows in the view; a specified list or a named subset. In (basic) training I tell people to always, always use the named subset. (We have ones covering all N elements and most of the common top level consolidations and children which are rebuilt overnight.) If you don't, then the active form is created with a hidden sheet (named something like {AF01}) which has the elements at the time of creation hard coded. The TM1Rptrow function points to that list. And you will never get more elements in the active form than are in that list, even if they are later added to the dimension, unless you manually hack the list.

If you've specified a named subset on the other hand, then the zero suppression will always work against that subset.

For more advanced users I point out that sometimes you won't have a subset that neatly applies; you may want a drill down on a specified profit centre consolidation, for instance. In such a case I show them how to put the selection of the top level element as a SubNm, and then create an MDX drilldown expression based on the selected element, and replace the TM1RptRow function's source with that MDX expression (which is usually in a hidden row at the top).

But when zero suppression doesn't work, in my experience it's always been down to a hidden, static list being used as the source.

I have to concur with Alan's experiences - generally when an ActiveForm is perceived as incorrect, it is generally down to something I have done i.e. poor planning and/or implementation. Normally you get tripped up when copying sheets and switching columns/rows, manually bringing a title to column/row, workbook scoped named ranges, deleting vital references, adding DBRWs with cube references before the TM1RptView etc. etc. etc.

IMHO ActiveForms are a huge improvement over the hurdles to get a slice *elegantly* into a sheet using all sorts of VBA code (assuming you only need dynamic rows)

I would however like to see some improvements in the code behind applying the formatting as this is where a lot of time is wasted when refreshing data.

Back to the main point of the topic however. I would not recommend deleting any named ranges that the TM1 addin and associated libraries may be expecting to find. I have found on more than one occurrence that ActiveForms freeze and Excel hangs if you delete or rename a reference that it required. My personal preference would be to have it there with the value set so that anyone else who opens the report sees it and can maybe give a thought as to why it has been set to the value it has been set to.

Thank you very much Alan and gtonkin.
Not sure the problems are due to poor implementation by the author of the Active form (AF).

Only yesterday I had again an issue with an AF. There was a Company SUBNM in the titles section, and the MDX statement for the rows dimension was written in a cell.
Actually, in the Cube viewer, the row dimension (Project) was using a dynamic subset prior to bringing the view to an AF in Excel.
I used the same MDX definition from the subset and put it in the Excel cell. I inserted a cell reference into the MDX definition by breaking up that string in smaller pieces. All relatively easy to do. The referenced cell contains hardcoded text.

The MDX statement would return the immediate children of a certain consolidated member (Total Project), combined with the SUNM for Company (dimension in the titles section).
In Excel it works flawlessly. If there are rows to be returned, Excel returns them. TM1 Web too.
In TM1 Web though, if no rows are to be returned after zero-suppression, the DBRW formulas in the grid are updated (to 0), but the same rows of the previous selection stay. These rows do not disappear (after all, no rows should be returned).

This is CX 10.1 (I think), in TM1 Web from TM1 10.2.2.3 TM1 Web takes away the rows as expected and as in Excel.

Now this specific case is due to a bug in the software (well, who would expect this kind of bug but that's a different matter).
But I am sure I can write down many many other situations where AF's do not behave as expected, both in Excel and on the web. And certainly not all due to bad implementation
What Alan wrote down, thanks obviously, I knew all that and applied it so many times in real TM1 projects.