Hi,
I have posted this because I want to keep this issue on IBM's radar, as I came across the issue again (will be linking to it for the April AMA).
I was scratching my head for a bit and then remembered the discussion above! Not sure we ever got a definitive answer.
I have been working on some MDX to order a Calendar dimension as I require, so the elements are dates, e.g. 01/08/2022, 02/08/2022 etc etc
Against these elements I have a Date Order numeric attribute called Date Order, so 01/08/2022 is Date Order 1, 02/08/2022 is Date Order 2 etc etc
I came up with the following MDX to order the n Level Calendar elements by the date order:
{ORDER(Except (TM1FILTERBYLEVEL({TM1SUBSETALL( [Calendar])}, 0),{Descendants([Calendar].[zSystem Required])} ), [Calendar].[Date Order], ASC)}
This works absolutely fine.
However, as per above discussion, stricter MDX rules will be enforced which prevent access to an attribute via the [Dimension].[Attribute] notation
I am trying to use the 'proper' notation going forward, and tried to rewrite the mdx as follows:
{ORDER(Except (TM1FILTERBYLEVEL({TM1SUBSETALL( [Calendar])}, 0),{Descendants([Calendar].[zSystem Required])} ), [Calendar].currentmember.properties('Date Order'), ASC)}
This did not work because it ordered the Date order incorrectly by ordering the elements by date order 1, 10, 100 etc etc, the order then becomes 01/08/2022 (Date Order 1), 10/08/2022 (Date Order 10) etc etc.
In other words it treats the attribute as a string and no longer as a numeric, as highlighted in the discussion above.
In order to get around this I referenced the attributes cube directly, so as follows:
{ORDER(Except (TM1FILTERBYLEVEL({TM1SUBSETALL( [Calendar])}, 0),{Descendants([Calendar].[zSystem Required])} ), [}ElementAttributes_Calendar].([Calendar].currentmember,[}ElementAttributes_Calendar].[Date Order]), ASC)}
So I suppose the outstanding questions are:
Will the optional TYPED parameter work with the properties function so that it reads it as a numeric attribute?
Will there be any kind of utility provided by IBM to ease transition to the next major release?
regards,
Mark
------------------------------
Mark Wragg
------------------------------
Original Message:
Sent: Thu September 02, 2021 12:04 PM
From: George Tonkin
Subject: Filtering on Attributes in MDX - future-proof syntax
I have a notion in my mind from somewhere that I need to use Properties("<attribute>") syntax in MDX rather than just the Attribute name in square brackets as the square brackets syntax may fall away in future i.e.
Use:
FILTER(TM1SUBSETALL([Company].[Company]) , [Company].[Company].CURRENTMEMBER.PROPERTIES("Size") = "Large")
Rather than:
FILTER(TM1SUBSETALL([Company].[Company]) , [Company].[Company].[Size] = "Large")
Is the square bracket syntax being deprecated at some point? Did I misunderstand something heard or read?
And no, I cannot remember where I got it from, just made a mental note to start using the Properties syntax)
If you have an answer either way, please let me know and if the answer relates to using Properties, please share something tangible.
Thank you.
------------------------------
George Tonkin
------------------------------
#PlanningAnalyticswithWatson