MDX to filter attribute and cube data

[vigneshg86]

Hi James,

Company is an attribute of a cost center. There could be more than one company mapped to a cost center. but there is only one to one relationship for a CC.

All my company attribute has got only four letters, below is the list of values i have for the company attriute at the moment

FHFM
LYPP
LYPM
LYPE
FHFE
APHE
APHS
CHEE
CHEM
EAPL
EAPE

as you can see, company attrbute has got E at other points too (eg LYPE)

Hope this answers your question.

cheers,

[jdeberardis]

Hi vineshg86,

Try this:

Code: Select all

{ FILTER( { TM1FILTERBYLEVEL( { TM1SUBSETALL( [cost centre] ) }, 0 ) }, LEFT( [cost centre].[company], 1 ) = "E" ) }

Cheers,
James

[vigneshg86]

OMG James!! You are simply a legend!!!

That works perfect. Thanks a lot again.

Cheers

[vigneshg86]

I should also add that you are a master of MDX

Cheers

[EvgenyT]

Good thread here.

You can also use

{ FILTER( { TM1FILTERBYLEVEL( { TM1SUBSETALL( [cost centre] ) }, 0 ) }, RIGHT( [cost centre].[company], 1 ) = "E" ) } to imitate ends with * wildcard.

Oddly enough MID function doesn't seem to compile, so I have a feeling its not supported by TM1.

Evgeny

[jdeberardis]

I should also add that you are a master of MDX

Cheers

haha, no , not really but thank you for the kind words. It was great to work this out as it helped me with the issue i was having.

All the best

[vigneshg86]

Thanks Evgeny for your help too. I will try your expression soon.

James, Please stay in touch through this forum or email. my email address is (sgvignesh@hotmail.com)

Best of luck

Cheers