Excel Range names in TM1 Formula

[pmakulski]

I have an error message that I think I have traced back to using excel range names.

In an Active form, I have given a meaningful range name to all of the dimension fields.
So now my TM1 formula is more readable:
e.g. =DBRW(ExpenseCube,Sources,MajorProduct,$B26,Organization,D$14,Scales,Years,D$13,"Expenses")
instead of
=DBRW($B$8,$D$1,$D$2,$B26,$D$3,D$14,$D$4,$D$5,D$13,$D$6)

Now, this seems to work fine on

• A Slice in Excel
• A Slice on the Web
• An Active Form in Excel

But in an Active Form on the web, I get an error message.

Code: Select all

Operation Failed
System has encountered a problem while handling your request.
Please try again.
If you keep having trouble, please contact your system administrator for assistance.

Unfortunately, I'm the system administrator and I have no idea why this doesn't work.

(Excel 2003, TM1 9.5.2 IE 8)

[pmakulski]

I have confirmed it.
If my DBRW has no excel range names in it, it works on web and in excel.
But if I use just a single range name in the formula, it doesn't work on the web.

I thought maybe I was using a key word or something. But I tried making the range name sOrganization instead of Organization, which made no difference.

I've taken the range names out of the formulas for now. But I really do think they are more readable/user-friendly than all those absolute cell references.

[tomok]

I use range names all the time in active forms and have never experienced a problem in Excel or TM1Web. However, I usually don't give a range name to the cell that contains the VIEW statement. Try leaving that parameter as the cell reference and use range names in the others. Also, make sure that your range names are defined as workbook-wide and not sheet-wide.

[pmakulski]

I've done a bit more puzzling with it. Turns out the problem is not the DBRW formula. My range named formula works.
=DBRW($B$16,Sources,MajorProduct,$B25,Organization,D$13,Scales,Years,D$12,"Expenses")

The problem is the TM1RPTVIEW.
This works:

Code: Select all

=TM1RPTVIEW("expensedev:ExpenseUserView:1",1, TM1RPTTITLE("expensedev:Sources",$G$8), TM1RPTTITLE("expensedev:MajorProduct",$G$10), TM1RPTTITLE("expensedev:Organization",$G$17), TM1RPTTITLE("expensedev:Scales",$G$11), TM1RPTTITLE("expensedev:Years",$G$9), TM1RPTFMTRNG,TM1RPTFMTIDCOL)

This doesn't:

Code: Select all

=TM1RPTVIEW("expensedev:ExpenseUserView:1",1, TM1RPTTITLE("expensedev:Sources",Source), TM1RPTTITLE("expensedev:MajorProduct",$G$10), TM1RPTTITLE("expensedev:Organization",$G$17), TM1RPTTITLE("expensedev:Scales",$G$11), TM1RPTTITLE("expensedev:Years",$G$9), TM1RPTFMTRNG,TM1RPTFMTIDCOL)

I'm guessing there is some sort of conflict inside TM1RPTTITLE, perhaps my name Source conflicts with its name Source.