workaround 256 character limit

[tez]

Hi

I'm trying to join some mdx's in TM1 active worksheet. It works fine for just one union, but once I add another union, the character limit stops it from working.

I have tried to place the mdx's in separate cells & use concatenate to join them, but it still doesn't seem to work.

My mdx for the union that works is...

=("{HIERARCHIZE({UNION({FILTER( {TM1SUBSETALL( [Cash_Flow_Dev_Line_Item] )}, [Cash_Flow_Dev_Line_Item].[Division_Usage] = ""ALL"")},{FILTER( {TM1SUBSETALL( [Cash_Flow_Dev_Line_Item] )}, [Cash_Flow_Dev_Line_Item].[Division_Usage] = ""RC-CI-SP"")})})}")

This is the mdx when I expand it to include one more union...

=("{HIERARCHIZE( {UNION( {UNION( {FILTER( {TM1SUBSETALL( [Cash_Flow_Dev_Line_Item] )}, [Cash_Flow_Dev_Line_Item].[Division_Usage] = ""ALL"")}, {FILTER( {TM1SUBSETALL( [Cash_Flow_Dev_Line_Item] )}, [Cash_Flow_Dev_Line_Item].[Division_Usage] = ""RC-CI-SP"")})},{FILTER( {TM1SUBSETALL( [Cash_Flow_Dev_Line_Item] )}, [Cash_Flow_Dev_Line_Item].[Division_Usage] = ""FM"")} )} )}")

The maximum number of unions I have is 5.

If anyone has any ideas, it would be greatly appreciated.

Cheers

[jstrygner]

You can try to define each union part as a separate subset. Then you can create another main subset that does union on those subsets, something like:

UNION({UNION({[Dimension].[Subset1]}, {[Dimension].[Subset2]})}, {[Dimension].[Subset3]})

The above is not tested, just a hint where to go (there might be some brackets/syntax mistakes).

[lotsaram]

The character limit is imposed by Excel 2003 not TM1. The only thing you can do is to reduce the number of characters in your MDX statement.

jstrygner's suggestion will help but you will need to then do additional development by having public dynamic subsets to replace your MDX
{FILTER( {TM1SUBSETALL( [Cash_Flow_Dev_Line_Item] )}, [Cash_Flow_Dev_Line_Item].[Division_Usage] = "ALL")}
{FILTER( {TM1SUBSETALL( [Cash_Flow_Dev_Line_Item] )}, [Cash_Flow_Dev_Line_Item].[Division_Usage] = "RC-CI-SP")}
{FILTER( {TM1SUBSETALL( [Cash_Flow_Dev_Line_Item] )}, [Cash_Flow_Dev_Line_Item].[Division_Usage] = "FM")}
etc.

You will need to make sure that the names you give the subsets don't conflict with any element names.
You can also save characters by removing spaces from the expression.
Most drastic would be to change the dimension name to something more concise.

If you upgrade to Excel 2007 then this limitation isn't there.

[qml]

The character limit is imposed by Excel 2003 not TM1. (...) If you upgrade to Excel 2007 then this limitation isn't there.

TM1 9.0 is not supported on Excel 2007. And I'm pretty sure the MDX length limitation in that version was not caused by Excel, but by TM1 as such.
Edit: Sorry lotsa, just noticed the OP has Cognos Express 9.0, not TM1 9.0. Progressive blindness is an awful thing.
So yes, Excel upgrade should solve this. If that's not an option, then the workaround suggested by jstrygner is the best one in my opinion. If you want to hide the intermediate subsets from the users then you can always create them from a TI using SubsetCreateByMDX and name them with a "}" as the first character in their names.

[lotsaram]

The character limit is imposed by Excel 2003 not TM1. (...) If you upgrade to Excel 2007 then this limitation isn't there.

TM1 9.0 is not supported on Excel 2007. And I'm pretty sure the MDX length limitation in that version was not caused by Excel, but by TM1 as such.
Edit: Sorry lotsa, just noticed the OP has Cognos Express 9.0, not TM1 9.0. Progressive blindnes is an awful thing.
So yes, Excel upgrade should solve this. If that's not an option, then the workaround suggested by jstrygner is the best one in my opinion. If you want to hide the intermediate subsets from the users then you can always create them from a TI using SubsetCreateByMDX and name them with a "}" as the first character in their names.

CX 9.0 is effectively TM1 9.4.1 FP2 but you beat me to it, my finger was on the trigger.

[Gregor Koch]

HI

Most drastic would be to change the dimension name to something more concise.

I take it the 'drastic' indicates that you'd actually recreate the dimension?
I think the better way of cutting down the length of dimension name is to create Aliases on the dimension.
This is easily done and saves you from recreating cube or dimensions.

[Gregor Koch]

Just a few more thoughts on the original problem:

Although the usage of other dynamic subsets in the query will work fine, I still try to avoid that as much as I possibly can. Even if Excel 2007 (TM1 9.4 and up) is used and you don't have the character limitation you are encountering.
As far as I know, and happy to be corrected on that one, the usage of public dynamic subsets issues a lock on the server (9.4, not sure about 9.5) whereas a full MDX query that is not using dynamic subsets in the Active Form doesn't.

A lot of times it is sufficient to create the subsets you need to use in your query or even the full subset you need in your AF overnight and then filter on the data (if necessary).
Basically I just ask the question as to how often for example

[Cash_Flow_Dev_Line_Item].[Division_Usage]

changes from a "" (empty) to a "FM", how it is changed and how soon this needs to be reflected in the report. Obviously if the answers are: 1. "very often, daily, during the day", 2. "manually", 3. "now" than you are 'stuck' to dynamic subsets. But if the answer is "daily, but the information is loaded (every night) from a source system and it needs to be available after that load" then I think you are better off with static subsets.

Another approach to replace your UNION statements is to have one Attribute which combines the logic of the different values you need to filter on (and use the dimension ordering) or even an attribute for the sole purpose of storing the order of elements in a report.
This doesn't mean that I don't use dynamic subsets at all, it's just the when and where.

And on the usage of the "}" for the naming of subsets: We had the same idea and used it until funny things started happening. Not that it crashed the server but, and this is anecdotal, subsets would vanish although Control Objects where visible and they wouldn't be properly deleted. Maybe we did things wrong there and it is fine to use this approach as I have used it many times for the naming of cubes with no issues. But after all if you try to do assign a "}name" through the Subset Editor you get a little warning that TM1 doesn't like it and maybe wants to keep it for UDCs ("}ROLLUP"). Really not sure if this makes sense and am actually interested in other people's experience here.

[tez]

Hi

Thank you to everyone with your suggestions...I was pretty much stuck, but you have given me different ways to look at problems we encounter in CX....& also TM1Web.

jstrygner - I went with the subset unions suggested - thanks, that works great & gives us room if the division_usage changes down the track. We also had "division" subsets as each division had their own names (aliases) for the elements, but I have now combined the mdx in a dynamic subset, so mdx is not needed in the active form & looks alot cleaner.

Gregor - I am interested in how to use a dimension alias - we have created one for the Cash_Flow_Dev_Line_Item dim (being CF)....can you only use this within Server Explorer for subsets, or can you use the alias in mdx in an active form?

Many thanks again!

[lotsaram]

One thing I neglected to mention in terms of being economical with syntax, typically you can replace
this
{UNION( {Set 1}, {Set 2} )}
with this
{Set 1} + {Set 2}