[Help] Building a Subset via MDX in TI?

[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)

[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

[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]

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.

[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.

[Roger_Lewin]

Thank you very much to both of you