MDX to filter attribute and cube data

[vigneshg86]

Hi All,

I am trying to write a MDX statement which should filter by a specific attribute first and then filter by a specific measure element for all values greater than zero.

I am getting the syntax error when i did the following. As you all know, error information given by subset editor is not easily interpretable at all.

{FILTER
(
{TM1SUBSETALL( [Task List FC] )},
[Task List FC].[Plant] = "FX07")
},
[Task List Library FC].([Measure Task List Library].[Contractor Hrs]) > 0 }

where "plant" is one of the attribute
"Task List FC" is the dimension
"Task List Library FC" is the cube
"Contractor Hrs" is the measure

Task list Library FC is two dimensional cube

can yo please help me correcting the syntax??

Note : this is working

"{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Task List FC] )}, 0)}, [Task List Library FC].([Measure Task List Library].[Contractor Hrs]) > 0 )}"

but I need to add the filter by Plant attribute here, when I try to do that, it gave me syntax error..

Regards,

[EvgenyT]

there you go:

(FILTER({FILTER( {TM1SUBSETALL( [Task List FC] )}, [Task List FC].[Plant] = "FX07")}, [Task List Library FC].([Measure Task List Library].[Contractor Hrs]) > 0 ))}

[vigneshg86]

Thanks EvgenyT. It works perfect. Much appreciated.

Cheers

[EvgenyT]

Thanks EvgenyT. It works perfect. Much appreciated.

Cheers

No probs. You are welcome.

you could also do this way...

(FILTER( {TM1SUBSETALL( [Task List FC] )}, [Task List FC].[Plant] = "FX07" AND [Task List Library FC].([Measure Task List Library].[Contractor Hrs]) > 0 )}

And the reason you were getting an error is because Filter function needs to have a "correct" set specified.... you had:

{FILTER ( {TM1SUBSETALL( [Task List FC] )},[Task List FC].[Plant] = "FX07")}, [Task List Library FC].([Measure Task List Library].[Contractor Hrs]) > 0

The part where you closed your Filter set was "FX07")}, consequently [Task List Library FC].([Measure Task List Library].[Contractor Hrs]) > 0 couldnt compile as it was not a part of any function.

Also you had "{" in front of the FILTER where you needed "("

Hope it all makes sence

Thanks

ET

[vigneshg86]

Yes. Thanks again.

You were too quick to post a reply mate. I just tried using the AND in the middle and I got the same result too. Anyway, a big thanks for all your time.

Cheers

[EvgenyT]

Yes. Thanks again.

You were too quick to post a reply mate.

Yeap.. if you use AND you get the same result. I prefer using (FILTER({FILTER to "eliminate" unwanted sets first and then apply 2nd Filter after, just gives you more visibility/tracing I guess, but its just me

Thanks

ET

[vigneshg86]

Sure mate. I will stick to your suggestion using double FILTER as opposed to using AND.

thanks again my friend.

[EvgenyT]

All good mate.

Its good to try out all scenarios to understand how they affect your dimension elements.


ET

[vigneshg86]

Ofcourse, I totally agree with you mate...

[vigneshg86]

Hi All,

I am trying to create a MDX to list all the elements in one of the dimension based on an attribute value starting with something (100). Basically a wildcard search

{FILTER( {TM1SUBSETALL( [Task List FC] )}, [Task List FC].[Assembly] = "*301*")}

Task List FC is the dimension
Assembly is the attribute of the Task List FC dimension
301* is a wildcard search to list all elements starts with 301

Can you please help me creating a MDX expression? It returns nothing for me.

I also tried replacing FILTER with TM1FILTERBYPATTERN, it is giving away syntax error

[tomok]

Did you know there is a way to record MDX queries so that it shows you the code, much like the macro record function in Excel? Double click on the dimension to bring up the Subset Editor, choose View, Expression Window, then Tools, Record Expression. Now click on Show All and then Filter By Wildcard and type in 301* and click on OK. Your MDX code will show in the window a the bottom.

[vigneshg86]

Thanks for that Tomok..

My problem is actually trying to wilcard an attribute, the way you said is helpful if i want to wildcard based on the principal element name.
I am trying to get all the elements for which one of the attribute starts with 301.

Do you think it is doable?

Thanks again

[jdeberardis]

Thanks for that Tomok..

My problem is actually trying to wilcard an attribute, the way you said is helpful if i want to wildcard based on the principal element name.
I am trying to get all the elements for which one of the attribute starts with 301.

Do you think it is doable?

Thanks again

Not to necro-thread here but am also looking for a solution to filtering by attribute with wildcard. Another thread suggests using INSTR but this is not working for me. I've toyed with the INSTR value (different iterations of * and no *).

Code: Select all

 { FILTER ( { FILTER( {TM1FILTERBYLEVEL( { TM1SUBSETALL( [dimension] ) }, 0 ) }, [dimension].[Attribute] = "Attribute" ) }, INSTR( [dimension].[Attribute], "*AttributeValue*" ) > 0 ) }

I've also tried using TM1FILTERBYPATTERN and also EXCEPT but I cannot get this to return the values required.

Is there a way to wildcard attributes in TM1 MDX ( the record expression will only take you so far).

Cheers
JDB

[vigneshg86]

Sorry mate, I still couldn't figure out how to do that.

Cheers

[jdeberardis]

Sorry mate, I still couldn't figure out how to do that.

Cheers

I think I have it bud:

Code: Select all

{ FILTER(  { FILTER( {TM1FILTERBYLEVEL( { TM1SUBSETALL( [dimension] ) }, 0 ) }, [dimension].[Attribute] = "AttribValue" ) }, INSTR( 1, [dimension].[Attribute], "AttribValue" ) < 1) }

I noticed in the

Code: Select all

INSTR( 1, [dimension].[Attribute], "AttribValue" ) < 1)

, the "AttribValue" is case sensitive. The < 1 means I am excluding instances of the AttribValue.

Hope this helps!

[vigneshg86]

Sorry mate, it doesn't work for me

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

i wanna list all cost centres which have company attribute starting with the value E

Any help will be much appreciated?

Cheers

[vigneshg86]

Sorry it is

{ FILTER( { FILTER( {TM1FILTERBYLEVEL( { TM1SUBSETALL( [cost centre] ) }, 0 ) }, [cost centre].[company] = "EAPL" ) }, INSTR( 1, [cost centre].[company], "E*" ) < 1) }

it returns only the cost centre element which has the company attribute value of EAPL

[jdeberardis]

Sorry it is

{ FILTER( { FILTER( {TM1FILTERBYLEVEL( { TM1SUBSETALL( [cost centre] ) }, 0 ) }, [cost centre].[company] = "EAPL" ) }, INSTR( 1, [cost centre].[company], "E*" ) < 1) }

it returns only the cost centre element which has the company attribute value of EAPL

Try this

Code: Select all

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

see this for the syntax on the INSTR function.

http://technet.microsoft.com/en-us/libr ... 58424.aspx

This works in TM1 as well.

Cheers,
JDB

[vigneshg86]

Hi jdeberardis,

It still returns only one element (same result). Any thoughts?

Cheers

[jdeberardis]

My apologies, I was basing the code of what i had done and was thinking you could dissect a solution from that.

Two questions;

1. Are all your company cost centres four letters and upper case?
2. Do your cost centres have an "E" at other points eg. ALPE, AEPL etc

Cheers,
James