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
TM1RPTROW - MDXStatement length limit?
-
asutcliffe
- Regular Participant
- Posts: 164
- Joined: Tue May 04, 2010 10:49 am
- OLAP Product: Cognos TM1
- Version: 9.4.1 - 10.1
- Excel Version: 2003 and 2007
-
kpk
- MVP
- Posts: 214
- Joined: Tue Nov 11, 2008 11:57 pm
- OLAP Product: TM1, CX
- Version: TM1 7x 8x 9x 10x CX 9.5 10.1
- Excel Version: XP 2003 2007 2010
- Location: Hungary
Re: TM1RPTROW - MDXStatement length limit?
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.
"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.
Best Regards,
Peter
Peter
-
asutcliffe
- Regular Participant
- Posts: 164
- Joined: Tue May 04, 2010 10:49 am
- OLAP Product: Cognos TM1
- Version: 9.4.1 - 10.1
- Excel Version: 2003 and 2007
Re: TM1RPTROW - MDXStatement length limit?
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.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
Cheers,
Alex
-
lotsaram
- MVP
- Posts: 3706
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: TM1RPTROW - MDXStatement length limit?
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.
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.