Page 1 of 1
LEN works in Architect, not PAW
Posted: Wed Mar 20, 2019 7:11 pm
by PavoGa
This code:
Code: Select all
FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
LEN([Organization].currentmember.Name) > 18);
works just fine in Architect and TIs.
Returns "Invalid MDX expression" in PAW. The Reference Guide does not list
LEN as being a supported function so there is that. Has anyone else has seen this issue and, if so, are there workarounds?
Re: LEN works in Architect, not PAW
Posted: Wed Mar 20, 2019 7:31 pm
by Wim Gielis
Hello,
You could fill up an attribute with the length of the string, probably with TI but a rule is possible too. After that you can query on the attribute. I haven’t used it in Workspace until now.
Re: LEN works in Architect, not PAW
Posted: Wed Mar 20, 2019 9:44 pm
by PlanningDev
Couple of things here
1. Im able to make it work with PAW 39. However you may want to add the extra set of [] to force the Hierarchy name just in case you use hierarchies.
Code: Select all
FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
LEN([Organization].[Organization].currentMember.Name) > 18);
2. If you are looking to filter on an alias you may need to make a text attribute to fill in the alias values that are blank. Its possible for alias values that have not been set to be blank in the }ElementAttributes control cube for that dimension. If that is the case for any elements then filtering on them by length becomes an issue since they are evaluated as 0 length.
3. If you want to filter by an attribute you can also shortcut that with TM1 (Assuming you don't have name conflicts with elements)
Code: Select all
FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
LEN([Organization].[Organization].[Attribute Name Here]) > 18);
Re: LEN works in Architect, not PAW
Posted: Thu Mar 21, 2019 2:30 pm
by PavoGa
Wim Gielis wrote: ↑Wed Mar 20, 2019 7:31 pm
Hello,
You could fill up an attribute with the length of the string, probably with TI but a rule is possible too. After that you can query on the attribute. I haven’t used it in Workspace until now.
Squat. Forgot about that, Wim, thanks! It'll do.
Re: LEN works in Architect, not PAW
Posted: Thu Mar 21, 2019 5:13 pm
by lotsaram
But don't forget that member properties are explicitly strings and TM1's MDX implementation doesn't allow type recasting. So if you want to treat the value as numeric then you need to query the ElementAttributes cube not get the member property.
Re: LEN works in Architect, not PAW
Posted: Fri Mar 22, 2019 1:18 pm
by PavoGa
PlanningDev wrote: ↑Wed Mar 20, 2019 9:44 pm
Couple of things here
1. Im able to make it work with PAW 39. However you may want to add the extra set of [] to force the Hierarchy name just in case you use hierarchies.
Code: Select all
FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
LEN([Organization].[Organization].currentMember.Name) > 18);
2. If you are looking to filter on an alias you may need to make a text attribute to fill in the alias values that are blank. Its possible for alias values that have not been set to be blank in the }ElementAttributes control cube for that dimension. If that is the case for any elements then filtering on them by length becomes an issue since they are evaluated as 0 length.
3. If you want to filter by an attribute you can also shortcut that with TM1 (Assuming you don't have name conflicts with elements)
Code: Select all
FILTER( TM1FILTERBYLEVEL(TM1SUBSETALL( [Organization]), 0),
LEN([Organization].[Organization].[Attribute Name Here]) > 18);
Thanks. We tried all this, but apparently our version of PAW is having none of it.