MDX to get User Cubes Names

[schlemiel29]

Hi,
I'm trying to define a Picklist for the user for selecting one of his own, non system, cubes.
I know, I find the names in the }Cubes dimension. But there are also all system cubes, beginning with "}".
Now I want to create a subset with all non }-cubes using a MDX statement.
Unfortunatly I didn't get a solution.

TM1FILTERBYPATTERN is filtering positive, so anything like '}*' will be in, but I need the others!

Code: Select all

FILTER( {TM1SUBSETALL( [}Cubes] )}, NOT StrToMember([}Cubes].CurrentMember.Name).StartsWith("}") )

leads to an error, maybe StartsWith is unknown? I could not find the TM1 list of MDX statements for string handling.

BTW:

Code: Select all

FILTER( {TM1SUBSETALL( [}Cubes] )},  [}Cubes].CurrentMember.Name = '}Cubes' )

is syntactically correct.

Also my attempt to use SUBSTRING() was unsuccessful. I read an article for defining a rule doing the SubString there, but I can't believe that this would be the solution today.

So any hints for me?
BR
Dirk

[schlemiel29]

After searching for hours and posting this question, I fould the magic "EXCEPT".

Code: Select all

{ EXCEPT(
{ TM1SUBSETALL( [}Cubes] )},
{ TM1FILTERBYPATTERN( {TM1SUBSETALL( [}Cubes] )}, "}*") }
)}

Worked for me. But if anybody else will face this problem in the future again, here is the code.
BR
Dirk

[gtonkin]

Another option for you loosely based on your first example and using INSTR:

Code: Select all

FILTER(
	{ TM1SUBSETALL( [}Cubes] )},
	INSTR(1,[}Cubes].[}Cubes].CurrentMember.Name,"}",1)=0
	)

[schlemiel29]

Nice! Thanks!