Page 1 of 1

MDX to Filter on Numeric Attribute

Posted: Tue Dec 05, 2017 6:00 pm
by gtonkin
I am trying to find a better way to filter on a Numeric Attribute (contains 0/1) where the name of the Attribute also happens to be the name of one of the Members.
Using the standard syntax to filter on an attribute returns the context error as the MDX is ambiguous.
Using the following code does not work as the value being evaluated seems to be something other than 1:

Code: Select all

{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)},
[<dim>].CurrentMember.Properties("<attr>")="1")}
Note: Converting the result using StrToValue yields an error confirming that both numeric and string attributes are treated as strings in MDX

Getting a bit more creative, the following does seem to work:

Code: Select all

{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)},
INSTR(1,[<dim>].CurrentMember.Properties("<attr>"),"<value>",0)>0
)}
So,
-Any better ideas to get the MDX more simple for future reviewers/maintainers?
-Anyone know what is returned by [<dim>].CurrentMember.Properties("<attr>") other than a plain old 1?
-Any way to TRIM the result - tried trim, rtrim, ltrim?
-Is there a way to fully qualify the attribute within the MDX, similar to how you would need to address ambiguous elements in Rules?

And lastly, yes, I could have changed the name of the attribute as an easy way out but where's the challenge then?

Re: MDX to Filter on Numeric Attribute

Posted: Tue Dec 05, 2017 7:50 pm
by tm123
You can use the }ElementATtributes_dimName cube in your filter. Can you check if MDX below will give you want you want?

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)}, [}ElementAttributes_<dim>] .([}ElementAttributes_<dim>].[<NumericAttribName>])=1)}

Re: MDX to Filter on Numeric Attribute

Posted: Wed Dec 06, 2017 6:15 am
by gtonkin
Hi TM123, it does indeed give me the results-too long looking at a problem from one angle. Thanks for the assistance!
FYI - my initial MDX seems to work fine on PAX 2.0.1 - something must have been fixed.

Still curious though about the answers to some of the other questions posed in my initial post.

Re: MDX to Filter on Numeric Attribute

Posted: Wed Dec 06, 2017 9:40 am
by lotsaram
Hey George this looks a lot like a PMR which I had open. 89862,112,848 "Inconsistent behavior in TM1 MDX evaluation"
PMR wrote:There are 3 ways which TM1 allows attributes to be queried via MDX which deliver inconsistent results. TM1 allows both string and numeric attributes

1/ TM1 "shorthand" MDX notation e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)}, [Date].[Serial TM1] > 20000 )}

The issue with TM1 shorthand notation (which is what is traditionally recorded in the architect/perspectives subset editor) is that it is ambiguous whether a member, a subset or an attribute is being referenced which can lead to inconsistency in the case of name conflicts.

2/ TM1 "industry standard MDX" using member properties. e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)} , [Date].CurrentMember.Properties("Serial TM1") > 20000 )}

This syntax is generally preferred as it conforms to Microsoft MDX norms and is non-ambiguous. However in TM1 this notation forces all member properties to type STRING so the above member set query would fail and return an empty set.

3/ "background attribute cube query" e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)}, [}ElementAttributes_Date].( [}ElementAttributes_Date].[Serial TM1] ) > 20000 )}

This member set query will work for a numeric attribute.

All 3 of the above queries work and return consistent results for String type attributes but the standard member properties type notation does not work for numeric attributes and the type is coerced to string. Behavior should be consistent for all 3 expression types.

Currently developers are forced to code a workaround by using the 3rd expression type for numeric attributes.
Support did open a collab with development but they came back with "this is expected behavior." Essentially from the point of view of MDX all member properties are expected as strings and you can't retype them. So the only work around is to query the attribute cube.

Re: MDX to Filter on Numeric Attribute

Posted: Wed Dec 06, 2017 9:56 am
by gtonkin
Thanks for the response and info Lotsa - will be using option 3 for the foreseeable future as many clients are still on 10.2.2 FP4.

Re: MDX to Filter on Numeric Attribute

Posted: Wed Dec 06, 2017 10:24 am
by Steve Rowe
Sort of off topic but...
Isn't this a symptom of the way numeric attribute are implemented in the first place? Numeric attributes aren't really numeric cells in the same way that data is and IMO are just a "mask" on a string attribute. Personally I rarely use them since they behave like strings and I can't write rules versus them.

If I want a numeric property of an element I'll build a cube for this purpose, though lotsarams use case of time is a good example where I wouldn't....

Re: MDX to Filter on Numeric Attribute

Posted: Wed Dec 06, 2017 10:31 am
by gtonkin
Steve Rowe wrote: Wed Dec 06, 2017 10:24 am ...Personally I rarely use them since they behave like strings and I can't write rules versus them...
Thanks Steve, agree on the strings and the usage of another cube as you can secure better as well as use picklists to populate the text "attribute".

Re: MDX to Filter on Numeric Attribute

Posted: Wed Feb 02, 2022 11:09 am
by markhackett
I realise this is an old post, but I was looking for a way to convert a string attribute to numeric value for checking in an MDX expression. It looks like the StrToValue function allows you to convert the string to a numeric value, so you can use this in your MDX expression, e.g.

{
FILTER(
{TM1FILTERBYLEVEL({TM1SUBSETALL([Dates])},0)},

(StrToValue([Dates].[LeafIndex])>100)
)
}

In the above example, LeafIndex is a text attribute (probably shouldn't be text, but it is). Just wanted to update this post in case anyone else was looking for similar.

Mark

Re: MDX to Filter on Numeric Attribute

Posted: Wed Feb 02, 2022 5:00 pm
by Wim Gielis
There are a couple more examples with StrToValue on my page (just use Ctrl-F on the page):
https://www.wimgielis.com/tm1_mdxstatements_EN.htm