TM1 Active Form allows multiple cube updated in a worksheet?

[tstroumbos]

One more tidbit of information...if I initially upload the websheet with the shorter subset showing (still only loading with the TM1RPTROW), it then won't render the bottom 12 elements of the longer subset.

[whitej_d]

I think the fact that removing title elements from you're TM1RptView formula solved your issue points to a potential issue with the title elements. Do any of the title references contain DBRW formulas, or depend on anything with a DBRW formula? Remember that all references included in the TM1RPTView and TM1RptRow formula must be evaluated first using DBR formulas before any DBRW formulas can be successfully returned. TM1Web also will only recalc on the sheet in focus, so anything relying on another sheet in the workbook won't calculate correctly if recalculated. I believe the calculation order is also slightly different in TM1web to perspectives, so things like having a subnm dependent on a value written to the server using DBSS often don't work. As has already been mentioned, Subnm formulas will work fine if they are blank in perspectives, but will give 'Operation Failed' in TM1Web, so check that all the subnm formulas in your title elements are resolving correctly and not returning blanks.

They can horribly tricky things to troubleshoot as they always try to return the 'stargate' cached view wholesale to the report, so if you change something like a column header or a reference to something unrecognized, it will often not give an error, but just paste the wrong numbers in the wrong place. I've done a lot of work with them and they do break often, but there is almost always a logical reason for them not behaving themselves, it just can sometimes take a few hours to find, and Perspectives is more forgiving than TM1Web. Often, the best way is to rebuild the sheet from scratch bit by bit and test in TM1Web as you build it. Then you can identify the exact point it breaks and fix it. Good Luck!

[Suharsh]

Hi,

I have a query on Active forms. TM1 User guide says, Column A in Avtive form has a value that contains format definition corresponding to level of each subset element.

I want to ask, what if the row dimension does not have any specified subset in the view. I mean in my case, in the row dimension, there are just a couple of handpicked elements from different hierarchies in the dimension. In such case how does Active form creates a value in Column A for elements from different hierarchy and no subsets?

Any help would be greatly appreciated.

Thanks

[Alan Kirk]

Hi,

I have a query on Active forms. TM1 User guide says, Column A in Avtive form has a value that contains format definition corresponding to level of each subset element.

I want to ask, what if the row dimension does not have any specified subset in the view. I mean in my case, in the row dimension, there are just a couple of handpicked elements from different hierarchies in the dimension. In such case how does Active form creates a value in Column A for elements from different hierarchy and no subsets?

Any help would be greatly appreciated.

Weird coincidence; on the train home this very afternoon I was writing up the script for a TM1 Byte on this exact topic.

Short answer: If you use a named subset for a row dimension then the active form's TM1RPTROW function (which generates the rows) uses the name of that subset.

If you use hard coded values, the active form will insert a hidden worksheet containing a list of the selected elements. The sheet will be named something like {AR}01. The list is stored in the hidden worksheet at the row and column corresponding to the start of the active form. The TM1RPTROW function then looks to the range(s) containing the list(s) of elements in the hidden sheets to determine which ones to use in the rows.

[Suharsh]

Thanks Alan, I checked and as you said TM1RPTROW has a worksheet with name {AR}01.

In case of subsets it becomes obvious as to how the Format Definition is applied in the Column A (Like 0, 1, N ). But if the values are handpicked, in that case does TM1 do a relative assigning of these values in column A (because the elements can be at different level in the hierarchy, one can be a 2nd level consolidation member where as other can be a leaf level)

[Alan Kirk]

Thanks Alan, I checked and as you said TM1RPTROW has a worksheet with name {AR}01.

In case of subsets it becomes obvious as to how the Format Definition is applied in the Column A (Like 0, 1, N ). But if the values are handpicked, in that case does TM1 do a relative assigning of these values in column A (because the elements can be at different level in the hierarchy, one can be a 2nd level consolidation member where as other can be a leaf level)

You're confusing two completely independent aspects of active forms.

The formatting (which is determined by the value in column A) has nothing whatsoever to do with whether the question of whether the elements (which are in column B) are hard coded or subset based.

When you create a new active form by default you get a formula put in column A, that formula being (let's use the case of a form with two row dimensions) something like this:

Code: Select all

=IF(TM1RPTELISCONSOLIDATED($C$23,$C23),IF(TM1RPTELLEV($C$23,$C23)<=3,TM1RPTELLEV($C$23,$C23),"D"),"N")

(The actual column specified will be the last of the row dimensions. So if you had three of them it would point to column D, say.)

That formula is a little hard to get your head around if you aren't familiar with the TM1RPT functions, and doing so is left as an exercise for the reader if you want to. You'll find the relevant documentation of each function in the Reference Guide. (Though the gist of it is that it works out whether it's a consolidation or an N level. If it'[s a consolidation, how many levels away from the top level consolidation is it? (0, 1, 2, 3 or more (D).) There are a few important things to note about it, though:
- The default formula shown above will return one of 6 values; 0, 1, 2, 3, D or N.
- Each potential value corresponds to the formatting that you have specified (or has been specified for you initially) in the formatting block, rows 2 to 7. Column A contains the code, the remaining columns contain the formats to be applied to each column. More importantly, the formatting block is a named range (created when the active form is inserted) called TM1RPTFMTRNG.

Now here are the parts that you need to be clear on:
- The formula shown above is only a suggestion. You can replace it with any formula you like by changing the formula in column A, on the first row of the active form only. (You cannot have different formulas on different rows since when you rebuild the form it copies the master row down.) Want the formatting to depend on nothing more than the level of the element? Replace it with an old fashioned ElLev function. Want to base it on just the first character of column B? The Excel Left() function will work just fine. It doesn't matter whether the elements came from a named subset or not; that has no bearing on the formula that you have in column A. All the formula in column A does is tell the form to look up the corresponding row in the formatting block.
- The other thing that you have to do is make sure that for all potential values that you return in your formula, you have a corresponding row in TM1RPTFMTRNG telling the active form how you want to format any rows which return that value. You do not have to use the default values of 0 to 3, N and D. You do not have to use exactly 6 values. You can use more than 6 values or fewer than 6 values, as long as you make sure that you expand or contract the TM1RPTFMTRNG range to match the number of values that you're using.

I hope that clears things up. Failing that you can always wait for the video but it's about the 7th one in the production queue and may be a while...

[Suharsh]

Well Alan, Thanks for a superb and very clear explaination. I would rather say, this is much more clear than given in the User Guide.

To conclude I want to get 1 thing right in my head. As you shown in the example, that if there are 2 row dimensions, the value in column A is picked up checking column C Element "$C$23" if it is consolidated or a leaf level.

ScreenCapture.JPG (24.61 KiB) Viewed 8329 times

Can I understand that if there are more than 1 dimension in the row, it would pick the last dimension to check if the element is consolidated and accordingly determine the Values (0, 1, 2, 3, D, N) in Column A.

(Say, there are 3 dimension in the row, then Value in column A would be calculated by checking the Element level in Column D like

=IF(TM1RPTELISCONSOLIDATED($D$23,$D23) )

If Yes, Is there a way I could highlight the consolidated elements shown in the below screenshot (Just these elements and not the entire column)

Capture.JPG (65.35 KiB) Viewed 8329 times

[Alan Kirk]

To conclude I want to get 1 thing right in my head. As you shown in the example, that if there are 2 row dimensions, the value in column A is picked up checking column C Element "$C$23" if it is consolidated or a leaf level.

...

Can I understand that if there are more than 1 dimension in the row, it would pick the last dimension to check if the element is consolidated and accordingly determine the Values (0, 1, 2, 3, D, N) in Column A.

(Say, there are 3 dimension in the row, then Value in column A would be calculated by checking the Element level in Column D like

=IF(TM1RPTELISCONSOLIDATED($D$23,$D23) )

That's correct, the default formulas check the innermost dimension's elements, which makes sense for most active forms. But again remember that this is not mandatory. You could conceivably change the formula on the master row (the first row of the active form) to look at column C, or column B if you wanted to. It wouldn't necessarily make sense in most contexts, but it's technically possible.

If Yes, Is there a way I could highlight the consolidated elements shown in the below screenshot (Just these elements and not the entire column)

The attachment Capture.JPG is no longer available

In the example that you're showing they're all N elements at the innermost level so honestly, I wouldn't even bother using TM1RPT formulas to define the formatting code. Instead I'd use the standard Excel formula (let's say that the active form starts on row 18)

Code: Select all

=IF(C18<>C17,"T","N")

(T being for "Top" in this case, though the code that you choose isn't important.)

In the formatting block you could get rid of all but two of the rows. One will be N for standard rows, and one will be T which is formatted in exactly the same way as the N one except for the highlighting of column C.

That would look a little somethin' like this:

ActiveFormFormatting.jpg (191.25 KiB) Viewed 8322 times

The same basic principle applies if you had a combination of consolidations and N elements; you could use the default formatting formulas, just modified with an extra And() function to check whether there's a change in the column value as well. For each "standard" format (the "N" format in my example, 0 to 3, N and D in standard formatting codes) add an extra one which applies if the value in that column changes from the value above (the "T" one in my example, make up your own if you're using the standard formats and codes).

[Suharsh]

Thanks Alan.

That really did help.