TM1 Forum

Hi all,

Continuing my journey and would love a bit of advice regarding Perspectives and this TM1RPTROW function. I believe I've got my head around it by digging into the Internet and looking at Planning Sample, but could do with a level-set if anyone's willing to provide it... or a pointer to a Getting Started document (which, unless I'm blind, doesn't seem to be in existence).

First question is - can I do this?

Start up Perspectives, connect to Planning Sample, insert an Active Form, choose Cube plan_ExchangeRate and View plan_exhange_rate_input_by_rate and you are presented with a table where the columns are filled-in with the contents of the "All Exchange Rates" Subset from the plan_exchange_rates Dimension. All good, I see rows containing local exchange rate, period beginning exchange rate, period ending exchange rate, planned exchange rate, current exchange rate and corporate exchange rate - 6 items, 6 rows.

The Excel formula for populating that is also simple and understandable - TM1RPTROW($B$9,"Planning Sample:plan_exchange_rates","All Exchange Rates") - and my question centres around this.

Can I copy that cell's formula and paste it elsewhere in the spreadsheet's worksheet and should I expect it to automatically 'unroll' all those 6 rows for me when I do that?

If I do that, and copy the same formula to cell B25 (just below the existing stuff) then I get a single row returned which contains local exchange rate - but not the other 5 rows I might expect to be returned.

Am I going about this incorrectly? Can I make copies and amendments to a cell containing a TM1RPTROW formula in this manner? Can I have more than 1 TM1RPTRow per worksheet? Do I have to insert another time from the Active Form menu in Perspectives? I did try the Rebuild buttons but no joy and this is so simple I'm thinking that I must be doing it wrong.

All the components of an active form have to work together. Those components are all the TM1RptXXX functions, as well as the TM1RPTXXX ranges that are created in the worksheet. If any of these are missing our out of sync then your report is likely to stop working. While it is possible to create an active form from scratch if you know exactly what you are doing it is not easy and would involve creating those ranges yourself, as well as configuring the formulas just right. You will not find any documentation on how to do that and I'm not going to ever write a post on how to do it either. Way too much involved. You should just stick to using the functionality built in to create active forms. That includes adding a second active form range to an existing report.

Yes you can achieve what you want and have multiple working TM1RPTROW formulas on various rows. Each one will have to sit in its own Active Form (a worksheet can have multiple Active Forms) and have a corresponding TM1RPTDATARNG* named range (which is used to dynamically rebuild content). The easiest method is to use the 'Insert Active Form' once you have the first Active Form created in your worksheet. You will then be able to amend it as needed.

Hi Hippo - try this guideline may shed some light on things for you. As Tom pointed out - there are many gotchas and even more if you are trying to use it on the Web i.e. Named ranges in the control formulae will work through perspective but not on web, even if scoped to the worksheet.

I can only recommend saving your templates often and date and timestamping to ensure you have backup versions. Active forms can crash for no apparent reason!

Hippo, let me add that the responses so far are all good information. Let me just clarify what I said. While it is possible to have more than one active form area in a report, you can't just copy a single TM1RPTROW formula from one spot in a workbook to another and expect it to redraw lines in that new area. That's because ALL the components of the active form have to be in place for the new TM1RPTROW formula to work. If you just copy the formula from one area to another I can almost guarantee you that it won't work as you expect. That's why it's best to use the right-clink, Add Active Form, functionality in Perspective to add that new section.

That is an useful link,gtonkin .Thanks for sharing it.

Thanks all - I appreciate the input, and the warnings. I'll certainly take a look at the link too.