Page 1 of 1
Moving Active Forms
Posted: Thu Dec 30, 2010 7:09 pm
by beaulini
I've created 2 active forms from the cube viewer that work when left in the Excel file that is automatically generated, but when I copy and paste to another workbook or use the "move" functionality in Excel to put these active forms in another workbook the TM1RPTROW formula returns a blank cell and all of the the calculations on the sheet go blank as well. I'm using TM1 9.5.1 and Excel 2007. All formulas appear to be referencing the correct cells on the page but I don't get any data.
Re: Moving Active Forms
Posted: Fri Dec 31, 2010 1:12 am
by lotsaram
In addition to cell formulas active forms also rely on three named ranges:
TM1RPTFMTRNG
TM1RPTFMTIDCOL
TM1RPTDATARNGx
If you don't have these ranges and also critically if the "x" in "TM1RPTDATARNGx" doesn't match to the identifier in the TM1RPTVIEW formula then the active form report will not work.
If you know what you are doing you can cut and paste reports from one workbook to another or you can create the named ranges manually. However if you don't know what you are doing I would suggest you might be better off moving additional content into the workbook with the active form rather than the other way round.
Re: Moving Active Forms
Posted: Fri Dec 31, 2010 1:27 am
by tomok
When you create an active form several things are done:
1) A formatting section is put at the top of the sheet. This section is given the range name, TM1RPTFMTRNG.
2) A formatting key lookup table is created, which is given the range name TM1RPTFMTIDCOL.
3) The report rows are defined and this is given the range name TM1RPTDATARNGARPT1 (where ARPT1 is the name of the active form area. If you have several active forms in a single sheet you will have several of these ranges).
4) The view statement formula, TM1RPTVIEW formula is built.
5) SUBNM formulas are built for all the title dimensions.
6) TM1RPTROW formulas are built for all the dimensions in columns.
7) DBRW formulas are built.
8) A formula is built in column A (based on the hierarchy levels) that is the lookup key for item 2.
As you can see, quite a bit is done when you create an active form. While it possible to create an active form manually, or copy one from another location, it is more than just copying the contents of the cells. Your best best is just to create a new active form in the tab and/or sheet you want it to be in, rather than trying to copy it in. As long as you have the view already created all you have to do is right click in the sheet and choose Insert Active Form.