Distinguish String elements in an MDX

[Wim Gielis]

Hello all,

As the title of the topic says, does anyone know how to distinguish string elements from numeric elements in an MDX-statement ?
I would like to, for example, have an MDX-driven subset on a dimension, that filters out all lowest-level numeric elements - no string elements.

I checked the properties, CurrentMember and so on, but could not find anything useful. Maybe it's because TM1 does not expose this information in its application of MDX statements ?

Thanks !

[declanr]

Not aware of any mdx filter function for it but I can't say I have ever tried; if there was a function I would assume it must be a TM1 specific function as opposed to standard MDX.

Suppose to get around it you could have a TI populate an attribute on the dim that just places the result of a DType in the attribute. Then filter that in the MDX.

[Wim Gielis]

Thank you Declan. You confirm my findings.

The purpose is to only use MDX, such that the dynamic subset responds to changing dimension elements/hierarchies without running any additional TI process.
I would also like to avoid a rule with Dtype.

Thanks !

[gtonkin]

Hi Wim,
I generally ensure all my N and C measures have formats configured with comma, percentage etc. etc. The remaining measures would then generally be the strings.
I then use MDX as follows to select them:

Code: Select all

FILTER([Measures].Members, [Measures].Currentmember.Properties("Format") = "")

Obviously not fool-proof but in your case I get the feeling you are meticulous so should work.

[Wim Gielis]

Hello gtonkin,

Thanks, it's 1 step in the good direction, I hadn't thought of that.
I'll be looking for other solutions and keep this one in mind.

Wim

[lotsaram]

Thank you Declan. You confirm my findings.

The purpose is to only use MDX, such that the dynamic subset responds to changing dimension elements/hierarchies without running any additional TI process.
I would also like to avoid a rule with Dtype.

Thanks !

Why don't you want to set an Attribute with DType?

[ardi]

I have done this with a Text Attribute Element_Type and in the TI that loads my measures dimension, I populate this attribute, but you can have a Rule to automatically maintain that attribute. Basically same this as lotsaram is proposing.

[Wim Gielis]

Hello all,

Thanks for the replies.

The scenario is as follows: I would like to use TI to create an MDX-driven subset to gather orphan elements.
I could do this in 1 dimension, or all dimensions of a certain cube, or all application dimensions, ...
I would prefer to only do the SubsetCreateByMDX and that's it. No additional attributes dimensions and cubes, no rules, and so on.

Whenever the dimension changes, the subset should update (as is usual with dynamic subsets ) without running the TI process again.

And by any chance... a method that is fool-proof

Wim

[gtonkin]

Cannot do fool-proof but may have last resort for you as follows:

Code: Select all

FILTER(
FILTER(
FILTER( [Measures].Members,
COUNT([Measures].CurrentMember.Ancestors)=0),
COUNT([Measures].CurrentMember.Children)=0),
[Measures].Currentmember.Properties("Format") <> "")

OR

Code: Select all

FILTER(
FILTER(
FILTER( [Measures].Members,
[Measures].CurrentMember.Parent.Name=""),
COUNT([Measures].CurrentMember.Children)=0),
[Measures].Currentmember.Properties("Format") <> "")

I have not been able to establish what, if any Property could be used to derive the DType and filter on Simple, Consolidated or String - may someone else has stumbled onto this and can share then you would have your solution.

[ardi]

Hello all,

Thanks for the replies.

The scenario is as follows: I would like to use TI to create an MDX-driven subset to gather orphan elements.
I could do this in 1 dimension, or all dimensions of a certain cube, or all application dimensions, ...
I would prefer to only do the SubsetCreateByMDX and that's it. No additional attributes dimensions and cubes, no rules, and so on.

Whenever the dimension changes, the subset should update (as is usual with dynamic subsets ) without running the TI process again.

And by any chance... a method that is fool-proof

Wim

By Orphan Elements you mean leaf numeric elements that do not have a parent? In that case you can write an MDX to do that, but if you want to distinguish String Elements from N elements that I dont think you can achieve it without additional attributes.

[Wim Gielis]

By Orphan Elements you mean leaf numeric elements that do not have a parent? In that case you can write an MDX to do that, but if you want to distinguish String Elements from N elements that I dont think you can achieve it without additional attributes.

That's indeed the subject of this topic

I had hoped for a reliable MDX-way of separating n and s type elements.

[Trevor MacPherson]

I might be missing something, but if the dimension had at least one cube that it was used in (as the format dimension), then an expression should demonstrate the strings like so:
EXCEPT({TM1SUBSETALL( [MyDim] )},{ FILTER( {TM1SUBSETALL( [MyDim] )}, [MyCube].[MyDim].CurrentMember<3.402823E38 AND [MyCube].[MyDim].CurrentMember>-3.402823E38)})
It doesn't matter if the cube is populated or not.
I haven't tested thoroughly, but it works in the small sample of cubes I've tried.

[Wim Gielis]

And then George Tonkin came with the solution !

Code: Select all

Filter( TM1SubsetAll( [Revenue_Msr] ), [Revenue_Msr].CurrentMember.Properties("ELEMENT_TYPE")="1")

1 = N type
2 = S type
3 = C type

[Mark RMBC]

Talk about dogged persistence, an example to us all!

I was then wondering if Element_Weight would return anything but doesn't!

I did get Element_Index to work,

so for example,

Code: Select all

Filter( TM1SubsetAll( [Revenue_Msr] ), [Revenue_Msr].CurrentMember.Properties("ELEMENT_INDEX")>"2")

regards,

Mark

[gtonkin]

Try something like this Mark:

Code: Select all

FILTER(TM1SubsetAll([account1]),
[account1].currentmember.properties("MEMBER_WEIGHT")<>"1.000000")

or
FILTER(TM1DrillDownMember(TM1SubsetAll([test]),ALL,Recursive),
StrToValue([test].currentmember.properties("MEMBER_WEIGHT"))=-1)

[lotsaram]

And then George Tonkin came with the solution !

Code: Select all

Filter( TM1SubsetAll( [Revenue_Msr] ), [Revenue_Msr].CurrentMember.Properties("ELEMENT_TYPE")="1")

1 = N type
2 = S type
3 = C type

I wish there was a like or +1 button. I never knew this.

[Mark RMBC]

Hi George,

The Member_Weight does work, how long has that been around?

I obviously need to pay more attention

regards,

Mark

[gtonkin]

Hi George,

The Member_Weight does work, how long has that been around?

I obviously need to pay more attention

regards,

Mark

Probably since forever but undocumented like many other functions and properties unfortunately.

[Mark RMBC]

Cheers George.

I even asked IBM about this, re this forum discussion:

viewtopic.php?t=14986

It is even undocumented to them!

Good work for joining the dots

[Wim Gielis]

And then George Tonkin came with the solution !

Code: Select all

Filter( TM1SubsetAll( [Revenue_Msr] ), [Revenue_Msr].CurrentMember.Properties("ELEMENT_TYPE")="1")

1 = N type
2 = S type
3 = C type

I wish there was a like or +1 button. I never knew this.

Credits to George