Active Forms - Data row suppressed after rebuild

[Nessie]

Hi,

I have an active form for project data e.g.

Actuals $
Project A 100
Project B 150
Project C 100
-Total 350

As time goes on this list grows as new projects are started up but what is happening when I rebuild my worksheet is the total reflects the correct new amount (total for all projects) but not all projects are shown in the report. To make them show I have to Minimise the Total row and then Expand it again. Then all the projects appear. I have tried saving at this point but when I rebuild the worksheet again the new project is hidden again e.g.

Actuals $
Project A 100
Project B 150
Project C 100
-Total 550

When I want it show:
Actuals $
Project A 100
Project B 150
Project C 100
Project D 200
-Total 550

Does anyone know how to overcome this? I want the full project list (i.e. the ones with $'s) to show when a rebuild is performed.

Thanks
Nessie

[!TM1Rules]

Hi Nessie,

Probably the easiest way would be to change the formula to reference a Dynamic Subset

CURRENT EXAMPLE: STATIC SUBSET
=TM1RPTROW($B$9,"Production:PJ_Dim","",'{AR}01'!$B$16:$B$23,"PJ_Dim & Description",0)

Note: PJ_Dim is the name of the dimension.

REVISED EXAMPLE: DYNAMIC SUBSET
=TM1RPTROW($B$9,"Production:PJ_Dim","PJ_DIM_Dynamic",0)

Note: PJ_DIM_Dynamic is a dynamic subset which has been created in the PJ_Dim dimension.

The above example looks at a dynamic subset in the PJ_Dim dimension and when the workbook opens any new projects (elements) will be included.

Hope that helps.


Cheers,
!TM1Rules

[declanr]

Hi Nessie,

Probably the easiest way would be to change the formula to reference a Dynamic Subset

CURRENT EXAMPLE: STATIC SUBSET
=TM1RPTROW($B$9,"Production:PJ_Dim","",'{AR}01'!$B$16:$B$23,"PJ_Dim & Description",0)

Note: PJ_Dim is the name of the dimension.

REVISED EXAMPLE: DYNAMIC SUBSET
=TM1RPTROW($B$9,"Production:PJ_Dim","PJ_DIM_Dynamic",0)

Note: PJ_DIM_Dynamic is a dynamic subset which has been created in the PJ_Dim dimension.

The above example looks at a dynamic subset in the PJ_Dim dimension and when the workbook opens any new projects (elements) will be included.

Hope that helps.


Cheers,
!TM1Rules

This will sort out your issue and I will stress that you always used a named subset in your tm1rptrow formula for performance issues, you can sometimes see a VERY dramatic difference in performance when you get rid of the hidden {AR} sheets... particularly if you are using TM1 Web to view your active forms.

However I would just like to clarify the use of STATIC and DYNAMIC terms above, !TM1Rules has used the term dynamic to mean that the active form will automatically derive the elements within a subset and static to say it will stick with what was there the first time.

In reality the use of {AR} sheets is just pulling a list of elements, hence it being the 4th parameter in the TM1RPTROW statement instead of the 3rd (which is for subsets.)
A STATIC subset is one that is manually maintained (although it can also be done through TI during the dataloads where new elements are added.)
A DYNAMIC subset is one that uses MDX code to automatically assess which elements appear.

Neither of these types of subset are specific to Active Forms, they exists within TM1 and just happen to be useable in active forms.

I would recommend the use of STATIC subsets in active forms where possible over DYNAMIC subsets, I in fact tend to use static subsets wherever possible (TIs etc) as you are just giving TM1 one less job to do in calculating which elements to show... this can particularly see improvements for TI performance so I use it everywhere; although the MVPs etc are likely to disagree with most things I say as everything I do is personal preference through trial and error as opposed to any actual sense of "real" knowledge...

[!TM1Rules]

Hi,

Just one other thought.. you can toggle suppress zeros in the active form.. TM1RPTVIEW formula (See examples). This will let you build the report that can either show only projects containing $ (zero suppress ON) or show ALL projects regardless of whether project contains $ (zero suppress OFF).

EXAMPLE: Zero Suppress ON
=TM1RPTVIEW("Production:CubeName", 1,

EXAMPLE: Zero Suppress OFF
=TM1RPTVIEW("Production:CubeName", 0,


Cheers,
!TM1Rules