TM1 Forum

When I slice out an activeform to Excel I immediately get zeroes instead of the data that I know is there, because I just saw it in the cube view I sliced out from.

If I press F2 on a cell that should have data and then hit enter then the correct number comes through. My usual fix for this problem is changing all the DBRWs to DBRs, but in the case of an active form this seems to break things next time I rebuild the sheet.

I've taken a look at http://forums.olapforums.com/viewtopic. ... 912#p14853 and http://forums.olapforums.com/viewtopic.php?f=3&t=3332 without much luck. The TM1RPTVIEW looks fine and I've just installed SP3 into Excel 2003. I'm using Cognos Express v9

Any ideas or workarounds?

Martin

An oldie but a goodie, ... try restarting Excel.

No such luck.

By chance the values that aren't coming through aren't rule calculated and not properly fed?

No, they're all simple values. Nothing more complex going on than a bunch of consolidations.

I have discovered that Shift + F9, F9 and Alt+F9 all return zeroes. But If I go to the TM1 menu and select "Save workbook on TM1 server" and try to save it as an application then the status bar says "Now saving temporary file" for a few minutes, then it calculates the numbers correctly and everything is fine and dandy. However this only works the first time I open the workbook and it stops working as soon as I hit shift+f9 or any other combo.

What does the "Save workbook on TM1 server" run that the calc methods don't run?

I've also discovered that the TM1 Web report is fine. As that's what I'm trying to create I guess it's not that big a deal, but it perplexes me that this can happen.

When I slice out an activeform to Excel I immediately get zeroes instead of the data that I know is there, because I just saw it in the cube view I sliced out from.

If I press F2 on a cell that should have data and then hit enter then the correct number comes through

I have managed to get the exact same effect in the past when I made a mistake when modifying the active form after I had first added it to the spreadsheet.
I would check the Title Element cell references in the TM1RPTVIEW formula.
Are they referencing the right cells? Have you subsequently modified one of those cells so that they no longer match correctly?

No, that was suggested in one of the posts I found while searching and I tried it to no avail. It happens from the moment I create the active form, before I've made any changes to the worksheet at all.

Sometimes the calculation cache used by active forms can get a little screwy. I have seen similar issues come up (sometimes rather than zeros you get the values from the first row repeated all the way down, or values from one column repeating in all the other columns). Sometimes changing the "report view name" in the TM1RptView formula to a new unique name (have to change the name of the TM1RPTDATA named range as well) and rebuilding will fix it. I haven't ever seen this persist over server sessions so if renaming doesn't help and you have already tried a client restart then a server restart would be the last resort.

I'm experiencing the same phenomenon as you describe here. There are no rules on the cube, there's no aliases turned on, I'm "slicing" a very basic Active Form and getting all zeros. I can get the values to show up by just performing an Excel Save, but refreshing/rebuilding the Active Form causes the values to revert to 0. It is a zero-suppressed view, so it is recognizing that there are values there, but they won't show up.

Have you discovered anything further? I have restarted my Excel, restarting the TM1 Service to no avail. Running 9.5.1 HF12.

I'm committing heresy here, but you could always tell Excel to calculate the sheet. I have had this issue before where this has solved it. Wish I could explain it.

Hi

I am just wondering if there is a difference in the version of Excel that you have on the web server compared to the client? That might perhaps explain why it worked in TM1 Web but not in the Client.

I was having so many problems with Excel 2007 and TM1 Web that I had to revert to developing in Excel 2003. That seems more stable. Having said that we only have one Active Form remaining in the system, because they aren't that reliable and they are very slow.

Regards


Paul Simon

Active Forms are flakey at best, I think it was Alan who mentioned about forcing excel to recalc its entire calc tree by using shift+ctrl+alt+f9

this may not be your problem but worth a try, let me rack my brains - I had all manner of issues with active forms recently so let me have a think

Interesting - the ctrl-shift-alt-F9 does the trick. Thanks for the tip.

I do know that AF's are quirky, but we've rolled out some pretty elaborate AF-based models and we've always gotten them to do what we needed ("creativity" is key). Not getting numbers in the AF directly after slicing it from the cube-view is troubling however.

Interesting - the ctrl-shift-alt-F9 does the trick. Thanks for the tip.

I do know that AF's are quirky, but we've rolled out some pretty elaborate AF-based models and we've always gotten them to do what we needed ("creativity" is key). Not getting numbers in the AF directly after slicing it from the cube-view is troubling however.

achaves, did you have the same issue as Martin?

I'm not sure the alt+shift+ctrl+f9 is documented unless it is a native excel function, Alan would probably be able to tell you (can't remember the thread title I got this info from so can't check)

I have trouble with AF's when trying to have more than one on a sheet, I always managed to achieve what I need but every now and then one of the AFs would get corrupted and I'd have to trick Excel into rendering them properly

dubs wrote:

Interesting - the ctrl-shift-alt-F9 does the trick. Thanks for the tip.

I do know that AF's are quirky, but we've rolled out some pretty elaborate AF-based models and we've always gotten them to do what we needed ("creativity" is key). Not getting numbers in the AF directly after slicing it from the cube-view is troubling however.

achaves, did you have the same issue as Martin?

I'm not sure the alt+shift+ctrl+f9 is documented unless it is a native excel function, Alan would probably be able to tell you (can't remember the thread title I got this info from so can't check)

It's documented in a somewhat piecemeal fashion. It is mentioned in the Keyboard Shortcuts section of the help as far back as Excel XP:

CTRL+ALT+SHIFT+F9
Rechecks dependent formulas and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.

Which is fine as far as it goes, but doesn't really explain it. To the casual user, it would be difficult to understand the difference between that and CTRL+ALT+F9, which is described as "Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation."

A half way decent guide to the calculation model that was introduced in Excel XP (which is where the command was first introduced) is in this MSDN article, though it also doesn't go into the dependency tree as such or even mention [Ctrl]+[Alt]+[Shift]+[F9]. The first MSDN article that I'm aware of which does (properly) is in relation to Excel 2010; specifically, this one.

It's also worth being aware of the command [Alt]+[F9] which is not an Excel standard, but rather an Iboglix customisation to rebuild an active form. It's mentioned by Rebecca (SummerBrewGal) in this thread, though Martin previously mentioned above that that command didn't solve his problem.

Heard from Support: This is a known issue that is fixed in 9.5.1 FP1 and 9.5.2 (both due out later this month). See notes below:


Some Active Forms that work in v9.4 and v9.5.0 stop working in v9.5.1.
Trying to recreate the Active Form from scratch in version 9.5.1 also
does not work the Active Form always returns zeroes/blanks after
a rebuild. This is triggered when using a specific number of dimensions.

History
=====
******* was running in v9.4 and working as expected. Prior to a
client demonstration in v9.5.1 they started up the db to test and
found half of the active form based reports were blank or they would
crash Excel. When they click on any DBRW and press F2 then F9 and values were there, reflecting what is in the cube.
Pressing F2 then enter will make the values come back to Excel. But when rebuilding the Active Form, everything returns to zeroes.

The workaround:

Change your DBRW's to DBR's - BUT you need to keep one column of DBRW's. Now the column that stays DBRW will still return only 0's, so you have to make it a dummy column and hide it, but it triggers the DBR's to calculate.

Don't you love TM1?

I have had this problem before as well. The most effective way to solve this for me was to turn on my automatic calculation in Excel prior to creating the active form. Also leave the calculation on automatic while modifying the report. I know it might take a bit longer but it has solved all my problem so I will take waiting a bit longer for recalcs the whole time hands down!

I think the specific number of dimension for a cube to trigger this behaviour for active forms is 10 dimensions.
( 10 works / 11 does not work in 9.4MR1 HF 16 ).

You can work around when building an active form which is meant to be a report
but I am not sure of what work around to use in a case of a drill down where the active form is generated dynamically...

So far CTRL ALT SHIFT F9 seems to be the best alternative ...

Hope that helps,


Kind Regards,

The solution is in the way you create and maintain the active form. Can you create an active form from a view and it works each and every time in its native form?

If you can, then when it is modified, that is when it will break.

Very tempromental thing these active forms. Try working only within the range that is originally created by the active form. You don't need to maintain a DBRW column with the rest being DBR to make it work.

Marco