[Help] Building a Subset via MDX in TI?

Post Reply
User avatar
Roger_Lewin
Posts: 17
Joined: Mon Nov 03, 2008 8:25 am
Version: 9.4 MR1 FP1
Excel Version: 2003
Location: Ratingen, Germany
Contact:

[Help] Building a Subset via MDX in TI?

Post by Roger_Lewin »

Hi there,

i'm quite lost with building a subset in a TI Process. What I want to do, is to filter the elements of a dimension called "Kostenstellen" by a pattern ("*Fst*")in an Alias called "Bezeichnung". The Principal Element Name does not contain the string, only the Alias does.

What I've tried so far is several versions of:
SubsetCreatebyMDX( 'Füllstellen', '{FILTER( {TM1SUBSETALL( [Kostenstellen] )}, [Kostenstellen].[Bezeichnung] = "*Fst*")}');
or
SubsetCreatebyMDX( 'Füllstellen', '{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Kostenstellen] )}, "*Fst*")}');

None of them worked. All i got was an error message "Subset can't be created".

Can you help me?

Roger

(Edit: Typos)
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: [Help] Building a Subset via MDX in TI?

Post by Steve Rowe »

I did a record expression using the subset editor and got this back.

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Type] )}, "*test*")}
Where Type is the dimension name

not sure how you get the Alias name but the above syntax works
If you are confident that the MDX is correct (try pasting it into the expression window of a dynamic subset) I'd change your subset name, maybe it's not coping with the ü ??

HTH
Technical Director
www.infocat.co.uk
User avatar
Roger_Lewin
Posts: 17
Joined: Mon Nov 03, 2008 8:25 am
Version: 9.4 MR1 FP1
Excel Version: 2003
Location: Ratingen, Germany
Contact:

Re: [Help] Building a Subset via MDX in TI?

Post by Roger_Lewin »

Thx, but it seems, the problem is using the alias name.

if i use a recorded expression in subset editor, it works, if the aliases are activated. the principal name does not contain the searchpattern, so without alias, no members are returned.

i don't think it's caused by the german special character 'ü', because it's encapsulated in the protective aprostrophes.

nonetheless i found a workaround, but it still interests how to solve such thing via MDX :)
ScottW
Regular Participant
Posts: 152
Joined: Fri May 23, 2008 12:08 am
OLAP Product: TM1 CX
Version: 9.5 9.4.1 9.1.4 9.0 8.4
Excel Version: 2003 2007
Location: Melbourne, Australia
Contact:

Re: [Help] Building a Subset via MDX in TI?

Post by ScottW »

I think you just need to use the SubsetAliasSet function.

zB.
SubsetCreatebyMDX( 'Füllstellen', '{FILTER( {TM1SUBSETALL( [Kostenstellen] )}, [Kostenstellen].[Bezeichnung] = "*Fst*")}');
SubsetAliasSet( 'Kostenstellen', 'Füllstellen', 'Bezeichnung' );

Problem erledigt! Except that I just noticed from your original post that the MDX returns a null set. TM1 won't let you create an empty subset. It's no good 1st declaring the alias since you can't set properties for an object that doesn't yet exist.

You could modify the filter string so that the expression will return a value then edit the expression manually after the alias has been set, except that I'm guessing that the whole purpose of creating the subset in TI is to avoid manual editing. Or you could create a dummy element in the dimension that matches your wildcard search, then create the subset, set the alias, then delete the dummy.

zB.
DimensionElementInsert( 'Kostenstellen', '', 'ZDummy_Fst', 'N' );
SubsetCreatebyMDX( 'Füllstellen', '{FILTER( {TM1SUBSETALL( [Kostenstellen] )}, [Kostenstellen].[Bezeichnung] = "*Fst*")}');
SubsetAliasSet( 'Kostenstellen', 'Füllstellen', 'Bezeichnung' );
DimensionElementDelete( 'Kostenstellen', 'ZDummy_Fst' );

If neither of these solution suits then your issue is probably intractable, unless someone has a brighter idea.
Cheers,
Scott W
Cubewise
www.cubewise.com
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: [Help] Building a Subset via MDX in TI?

Post by Steve Vincent »

or create the MDX subset but just to show all elements, then turn on the alias for the subset, then rebuild the same subset but with the criteria you really wish to search with.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
Roger_Lewin
Posts: 17
Joined: Mon Nov 03, 2008 8:25 am
Version: 9.4 MR1 FP1
Excel Version: 2003
Location: Ratingen, Germany
Contact:

Re: [Help] Building a Subset via MDX in TI?

Post by Roger_Lewin »

Thank you very much to both of you
Post Reply