TM1 Perspectives - maintable code, TM1RptTitle/TM1RptView

[baggottm]

Writing maintainable/resuable TM1 Perspectives code can be challenging.

In a TM1 Active Form, if you try to make a TM1 Perspectives TM1RptView function easier to maintain, by having the TM1RptTitle function reference variables instead of constants, you will find the following limitation.

The Element (2nd parameter) MUST reference a cell located on the same worksheet page as the TM1RptTitle function itself. This restriction does NOT apply to the 1st parameter, the Dimension name.

For example
=TM1RPTVIEW(View1_ServerDataCube, 0, TM1RPTTITLE(Dim_Versions,$C$11), TM1RPTTITLE("SalesPlanning_KPI:Companies",$C$13), TM1RPTTITLE("SalesPlanning_KPI:Currency",$C$14), TM1RPTTITLE("SalesPlanning_KPI:Brands",$C$15), TM1RPTTITLE("SalesPlanning_KPI:Product Groups",$C$16), TM1RPTTITLE("SalesPlanning_KPI:Customer Type",$C$12),TM1RPTFMTRNG,TM1RPTFMTIDCOL)

In the first TM1RptTitle function,

• The first parameter, Dim_Version, can reference a named cell with a workbook-wide scope.

• The second parameter, $C$11, must reference a cell located on the same worksheet as the TM1RptView function. Referencing a cell like Element_versions, or even Metadata!$A$1, will return an empty sheet.

You could have a cell on the main ActiveForms sheet with local cells for each of the names that you want to generalise and have these cells refer to resusable names like Element_Versions, on a worksheet like "Metadata". Not ideal, but achieves maintainability.

Some might consider this a TM1 bug, but it is unlikely ever to be fixed IMNSHO.

[rmackenzie]

The Element (2nd parameter) MUST reference a cell located on the same worksheet page as the TM1RptTitle function itself. This restriction does NOT apply to the 1st parameter, the Dimension name.
...
Some might consider this a TM1 bug, but it is unlikely ever to be fixed IMNSHO.

I'd suggest that this is just the way it works, and not a bug. Just because you find it counter-intuitive doesn't mean there is a fault - probably you need to adjust the way you are writing your control sheets to work within the boundaries of the tool.

[declanr]

I actually have to say I prefer it being this way, with the whole point of active forms being so dynamic I like users to be able to see exactly what slice points they are looking at; through the elements being on the same screen as the data.

I haven't used multipage workbooks in a while as per your example, since I tend to do most stuff in the web. I do however set up individual websheets; that to a users perspective appear to be part of a bigger "book" where they can navigate to other tabs using hyperlinks.
In order to allow the sharing of title elements through these I originally used action buttons to navigate between the sheets and pass title elements but I found that the passing of title elements via action buttons is more cumbersome and slightly flaky than i'd really like... also on an aesthetic point; it is much easier to nicely space out hyperlinks than it is action buttons (even ones formatted as hyperlinks.)

The method I go with now is having each sheet share their SubNm's based on control cubes that contain the }clients dimension; that way they can make their title element selection on any single page and have it pass through to others.

[rmackenzie]

The second parameter, $C$11, must reference a cell located on the same worksheet as the TM1RptView function. Referencing a cell like Element_versions, or even Metadata!$A$1, will return an empty sheet.

BTW it works for me if the formula in e.g. $C$11 is:

Code: Select all

=SomeSheet!A1

and the TM1RPTTITLE function still references the cell local to it's sheet.