TM1 Forum

Hi,

I have created an active form in Excel 2003 with TM1 9.5.2. In the first colmn the active form is displaying all of the cost centres of the cost centre dimension. The formula looks like this:

=TM1RPTROW($B$9,"tm1_tcms:T1","",'{AR}441'!$B$18:$B$160,"",0)

and returns a value of (lets say) "ABC123" which is the cost centre name.

The thing is, the customer wants to see both the Cost Centre in one column and then the cost centre description in the next column.

I have inserted a column to the right of this before the data starts. In this colmn I want it to display the Alias for the Cost Centre name which is a Desription of the cost centre. The formula looks like this:

=TM1RPTROW($B$9,"tm1_tcms:T1","",'{AR}441'!$B$18:$B$160,"Description",0)

However - this still returns the value "ABC123"!!

To try and solve the problem, I changed created a new view to download and changed the dim elements to alias in teh view. I then downloaded this as a new Active Form on a seperate sheet in the workbook. The formula that TM1 automatically came up with is:

=TM1RPTROW($B$9,"tm1_tcms:T1","",'{AR}411'!$B$18:$B$160,"Description",0)

And this returned the Alias name - which is what I wanted.

I have tried copying this formula into the column that I created in the first worksheet and this has not worked. Worksheet 'AR411' (as shown in the second formula), is the one with the descriptions in - I have made sure that the formula in the inserted column is referencing this worksheet - still no luck.

Any suggestions would be much appreciated.

Many thanks.

I applaud your effort but you certainly made it a lot harder on yourself than you needed it to be. In the first place, the TM1RPTROW formula is only needed in an active form when you want that formula to be a drillable dimension. Since you are just using the description in this report as additional information in a second column, you could have just used the DBRA function to pull it back. I don't know what the name of your cost center dimension is but it would look something like this (assuming you want to put the description in column D, your account number is in column C, and your active form starts in Row 12):

=DBRA("tm1_tcms:costcenter",C12,"Description")

Tomok,

Thank you very much...thats exactly what I wanted. I was getting carried away with the TM1RPTROW formula!

Thanks again!