Page 1 of 1
TM1RPTROW - MDXStatement length limit?
Posted: Fri Oct 01, 2010 10:41 am
by asutcliffe
I've got a valid MDX statement that defines a dynamic subset. However EXCEL complains that my TM1RPTROW formula contains an error. There is nothing else amiss with the formula. Could it be because my MDX statement exceeds some hard limit (ie 256 characters)? I couldn't find this documented anywhere. I'm using 9.4.1 FP3
Note, I'm aware that I can pass TM1RPTROW the name of a saved subset. What I want to do though is to dynamically change the elements in the tuple in the filter function of my mdx statement. Is there a different approach to this that I'm missing?
Cheers,
Alex
Re: TM1RPTROW - MDXStatement length limit?
Posted: Fri Oct 01, 2010 12:58 pm
by kpk
Hi,
"I've got a valid MDX statement that defines a dynamic subset."
What do you mean on valid?
Have you tested/validated it in the TM1 subset editor?
Based on the following node the 255 char is a hard limit or at least it was in 9.1.
http://www.bihints.com/node/73/revisions/view/208/238
"Note, I'm aware that I can pass TM1RPTROW the name of a saved subset. What I want to do though is to dynamically change the elements in the tuple in the filter function of my mdx statement."
From locking (performance) point of view it is better to avoid any dynamic subsets especially in read write TM1 applications, therefore your approach is good.
Re: TM1RPTROW - MDXStatement length limit?
Posted: Fri Oct 01, 2010 1:17 pm
by asutcliffe
kpk wrote:Hi,
"I've got a valid MDX statement that defines a dynamic subset."
What do you mean on valid?
Have you tested/validated it in the TM1 subset editor?
Based on the following node the 255 char is a hard limit or at least it was in 9.1.
http://www.bihints.com/node/73/revisions/view/208/238
Yes, by valid, I mean it does work in the subset editor. I've managed to shorten the string to < 255 and it now works which confirms that this limit still exists in 9.4.1 fp3. Thanks for your help Peter.
Cheers,
Alex
Re: TM1RPTROW - MDXStatement length limit?
Posted: Sun Oct 03, 2010 11:38 pm
by lotsaram
The string character limit is an Excel 2003 limitation NOT TM1 9.4.1
Complex MDX expressions to define TM1RptRow work just fine but you have to use Excel 2007 or 2010 (Excel 2010 isn't officially supported but so far in my testing with TM1 9.5.1 it seems to work just fine.)
If you have a cube with a lot of dimensions the other formula you have to watch out for in Excel 2003 which can break the cell character limit is TM1RptView.