TM1 Active Form allows multiple cube updated in a worksheet?

[plkkw]

Hi,

I found most of my company Active forms (excel) containing one cube only. Does TM1 allows multiple cubes updated in one worksheet?

[qml]

One Active Form can use one cube only, but you can have multiple Active Forms in one worksheet. Check out the Insert Active Form button on the TM1 Active Form toolbar.

[lotsaram]

One Active Form can use one cube only, but you can have multiple Active Forms in one worksheet. Check out the Insert Active Form button on the TM1 Active Form toolbar.

I wouldn't say that. One active form report row section can actually contain references to multiple cubes or multiple asymetric slices of the same cube. all that is needed is separate VIEW formulas for each independent slice in addition to the TM1RPTVIEW that is associated with the active form.

[qml]

You know what, you're actually right, lotsaram.
However, from my experience with version 9.5.1, using one Active Form with multiple VIEW formulas is very buggy. I've experienced it break spectacularly very often and my only workaround was to create a virtual cube that uses rules to pull values from all the underlying cubes and then to reference this one virtual cube only in my Active Form. Don't know if this has been fixed or if it was just my luck or what.

[lotsaram]

You know what, you're actually right, lotsaram.
However, from my experience with version 9.5.1, using one Active Form with multiple VIEW formulas is very buggy. I've experienced it break spectacularly very often and my only workaround was to create a virtual cube that uses rules to pull values from all the underlying cubes and then to reference this one virtual cube only in my Active Form. Don't know if this has been fixed or if it was just my luck or what.

My experience is actually the opposite. I do this all the time and haven't seen any issues with this approach. I have actually found that if the "true" active slice is as constrained and limited as possible (you need to have at least one column for the active form to work and generate the row set) then you get much better performance and reliability with VIEW formulas.

[qml]

Cheers, lotsa, I'll give it one more go then, maybe I'll change my opinion.

[tstroumbos]

Hey guys, I'm experimenting with a single active form which can be rebuilt based on a cube selection. The form works beautifully in perspectives (user selects cube, recalcs to get proper dimension values, then rebuilds to get the new cube values). My problem now is that the form won't work on TM1Web. From what I can see, the reason for this is that after changing the cube selection, the row containing the TM1RPTROW goes blank until you rebuild the form. For whatever reason, TM1Web can't handle the rebuild.

I've tried the following workarounds to no avail:

1. An IF statement in the TM1RptRow: =IF(CUBE="Finance JDE",TM1RPTROW($B$12,SERVER&":jde_account","Standard PL",,,,,0,),TM1RPTROW($B$12,SERVER&":sap_func_account","Standard PL",,,,,0,))
2. An IF statement in a 2nd column (the DBRW ref, to keep that 1st row from going blank): =IF(DBRA(SERVER&":"&ACCTDIM,B28,"description")=" ","Sales", DBRA(SERVER&":"&ACCTDIM,B28,"description"))
3. A separate tab which passes the cube selection to the active form named range CUBE using the advanced properties on an action button (this opens a new version of the app in perspectives but just switches tabs on TM1Web).

Any thoughts on getting that TM1RptRow to stay active? A couple notes:

1. The cube's dims are in the same order, and for two of the dims I have a named range making a substitution based on the cube name. For example "ACCTDIM" changes from jde_account to sap_account based on the selection. This ACCTDIM is referenced in the TM1RptRow function.
2. I have a separate TM1RptView statement for each cube, and the cell the DBRWs refer to is =IF(CUBE="Finance JDE",B10,B11)
3. The TM1RptRow refers to a subset "StandardPL" which exists on both rowset dimensions.
4. Autorecalc is on.

Again, this all works fine in perspectives, it's just that "break" of the TM1RptRow function which causes the issue on the web. It will render regardless of which cube I start with, but when I change cubes it won't render.

Screen shot attached. Thanks in advance!

[tomok]

A lot of people assume that anything that works in Perspectives, as long as they don't use any unsupported Excel functions, is going to work in TM1 Web. As long as you stay pretty straightforward that holds true. When you start integrating IF statements into DBRW, VIEW and TM1RPTROW functions it''s going to be hit and miss. It looks you've encountered one of the "misses". Everything you've tried appears reasonable. Unfortunately, I don't have any suggestions for you. You may be out of luck on this one. I'm pretty sure IBM support would be no help either.

[lotsaram]

Hey guys, I'm experimenting with a single active form which can be rebuilt based on a cube selection. The form works beautifully in perspectives (user selects cube, recalcs to get proper dimension values, then rebuilds to get the new cube values). My problem now is that the form won't work on TM1Web. From what I can see, the reason for this is that after changing the cube selection, the row containing the TM1RPTROW goes blank until you rebuild the form. For whatever reason, TM1Web can't handle the rebuild.

I think your problem is not to do with active forms and the TM1RptRow formula per se but is more of a generic problem with TM1 websheets that any "parametized"TM1 formula's arguments are only evaluated once on the initial recalculation or rebuild and not on subsequent recalculations. This also happens for example with SubNm formula on the web where the dimension or subset name is set with an IF or VLookUp - in Excel works beautifully but in the web the paramater can only be changed once whereafter it fixed. It seems that a formula driven parametization/argument inside a TM1 formula in TM1 Web becomed hardcoded upon first evaluation.

In terms of a solution there's the "user training" approach which could also be called the "live with it" approach. You make the users aware of the issue and train them that the first thing they have to do on navigating to the report is select JDE or SAP and then refresh the report and that it is not possible to thereafter change the cube without closing and reopening the report. Or there is a "design" approach (that involves some training as well) where you have a "Set cube change" or "Set source ERP system change" button next to the drop-down cell where the cube name is being selected. All this button is doing in the background is navigating to the same report using the advanced options to set the cube name value to the target cell. This should work as required.

[tstroumbos]

Thank you, that's good "why" information, I hadn't known about that limitation on the web. I am open to either changing design or training my users, if I can find a solution which works.

That said, I did try both of those options, to no avail. When I upload the report with a blank cube selection, it will still only render for one cube selection. Somehow, it's holding the value of whichever cube was last successfully rendered on the web (if I upload the sheet with SAP selected, then upload with a blank selection it will only render SAP and vice versa). I've closed all browser windows, uploaded a completely different file, and changed the cube selection from a SUBNM to a dropdown. Still nothing.

As well, I did try sending the cube value to my named "CUBE" cell using the advanced options and it didn't work either. I get no value in the CUBE cell. See the attached screen shot for the options I selected, maybe I did something wrong. This is true whether I use a SUBNM or a Value as my source, I still don't get any result in the target cell.

[tstroumbos]

Quick update, I was able to get the value to pass over (I needed to remove the "=" from the target object cell in the advanced options for the action button). Unfortunately, now I'm still getting the same result: it will only render the cube which was last successfully rendered on the web.

[tomok]

Where is the range CUBE? Is it in the same tab as the active form? If not, move it to the same tab as the TM1RptView and TM1RptRow formulas.

[tstroumbos]

Thank you, it is in the same sheet as the TM1RptView and TM1RptRow formulas...and again when I do all this in Perspectives it works. I see the value being passed into the named range.

I agree that we may be out of luck and have to go with a separate report for each cube, which is what we were trying to avoid.

[tomok]

2. I have a separate TM1RptView statement for each cube, and the cell the DBRWs refer to is =IF(CUBE="Finance JDE",B10,B11)

This may be your problem with TM1 Web. Have you explored having just one TM1RptView statement and modifying it to point to the CUBE range to build the cube reference? If the Title dimensions are the same for both cubes this might be worth trying.

[lotsaram]

Changing cubes works for me just fine with a forced re-navigation. If it isn't working for you then I think there must be something specific about how you have used the TM1RptView or TM1RptRow formulas.

[tstroumbos]

Hey guys, we were able to get the cube change to work without forced re-navigation even. We had to change the TM1RptView so it didn't include any call outs to title elements:

=TM1RPTVIEW(SERVER&":Finance JDE:1", 0,TM1RPTFMTRNG,TM1RPTFMTIDCOL)

Now, the report works beautifully on the web and in perspectives. Thank you for all your help troubleshooting!

[tomok]

Hey guys, we were able to get the cube change to work without forced re-navigation even. We had to change the TM1RptView so it didn't include any call outs to title elements:

=TM1RPTVIEW(SERVER&":Finance JDE:1", 0,TM1RPTFMTRNG,TM1RPTFMTIDCOL)

Now, the report works beautifully on the web and in perspectives. Thank you for all your help troubleshooting!

Call me a party pooper but I've got to think there are some negative performance consequences to removing the reference to title elements. Probably has to do with the efficiencies around the report using stargate views. I don't know this for a fact but the references to title elements are there for a reason, not just to look pretty.

[lotsaram]

Call me a party pooper but I've got to think there are some negative performance consequences to removing the reference to title elements. Probably has to do with the efficiencies around the report using stargate views. I don't know this for a fact but the references to title elements are there for a reason, not just to look pretty.

Removing one or two title dimensions from the TM1RptView is a nice technique to be able to do some fancy stuff on the fly like swap around title and row dimensions but I would also add my voice to the concerned party poopers - taking out all dimension:element references from the view is the equivalent of building a view of the entire contents on the cube, once you get any volume of data in the cube I would think this would make the report very slow or even choke the server entirely.

[tstroumbos]

I guess I hadn't expanded my thinking to think about why those title elements are there. I appreciate the feedback, and I'll definitely talk with the team about possible performance impacts down the road. You guys are definitely MVPs, I appreciate all your insight.

[tstroumbos]

Hey fellas,

I've been having no issues so far with my multi-cube active form insofar as performance goes, but the other day I encountered a new issue for which I can't find a solution. One of these reports is rendering the same subset, "Executive BS" (pun intended), regardless of the cube (and thus dimension) that is selected. On one dim, the subset is quite different than on the others. As usual, everything renders beautifully in perspectives, but when it's on TM1 Web, the smaller subset returns "System parameter Type Invalid" for the "missing" subset items.

I always upload with only my TM1RPTROW in the active form, to prevent caching, but somehow it seems it's still caching the data range somewhere. Any thoughts? I know this may not be the best forum for this, but it's a unique active form with all the multi-cube stuff so I felt it made the most sense to continue this thread.