MDX pattern search on alias?


#1

Hi,

I just implemented a “search while you type” text box that currently grabs up to 15 matching dimension elements matching the user entry, using TM1FilterByPattern expression. That works great with Canvas and is super fast.

It’s basically a bootstrap panel header with a text box:

Once you start typing, it expands its panel body and presents the result as a radio button list:

Works perfectly, supposed the search term is in the element’s principal name. But how would you apprach to extend the search on one of the alias names? Is there any way to tell the MDX it should consider the alias rather than the principal name? Or do I really have to lookup in the ElementAttributes cube of that dimension using a FILTER Statement, not sure how this might impact performance as the dimension is around 20k entries.

I got stuck here as I do not want to change the principal name of the dimension or work with a shadow dimension just for lookup purposes. There must be better ways, or not?

Thanks a lot,
Andreas


#2

Hi @andreas.franke,

There is an example of the search on alias in the Canvas samples application:
http://localhost:8080/samples/#/sample/component/element-list

Or maybe the subnm (Server Mode) is more relevant for what you are trying to do:

http://localhost:8080/samples/#/sample/component/subnm


#3

Hi @andreas.franke the “standard” way to do a search on alias value via MDX is with the INSTR function to the }ElementAttributes cube directly or to element properties. 2 things to watch out for

  • INSTR is case sensitive
  • the “optional & always ignored” 3rd argument actually isn’t ignored by TM1 and needs to be included for the function to work

#4

Dear Vincent,
dear cw-ch,

thanks for your inspiration, I now have a couple of options and will figure out what works best.
Some pro’s and con’s, which may be useful for others:

Using SUBNM in Server Mode:
[-] I had to create an “only-alphanumeric-alias”, which I missed initially and felt this option would not work for me.
[+] Now with such an alias created, this works just great and even better: out of the box with Canvas
[-] In Server mode, I cannot create a radio button list SUBNM, because in this format the SUBNM does not provide a search box, of course. This is exactly what I tried to achieve in my example above, see screenshots.

Element List:
[-] I have to implement kind of a “element click” functionality, which the radio button SUBNM brings out of the box
[-] I cannot use the filter directive of angular, as my dimension is huge and I cannot retrieve all the Elements in advance and then leave the filtering up to the front-end. It needs to be applied already in the MDX.
[+] (no pro using the elment list for my requirement)

Custom composition/directive
[-] Not out of the box, so no simple tm1-ui directive. However, I could wrap it in my own directive to make it re-usable.
[+] Flexibility in defining layout and functionality
[-] Pattern search via MDX against alias names not so easy. I still Need to figure out the hint of cw-ch using INSTR function. However, if it is case sensitive, this might turn out as a no-go as everything else in TM1 is not case sensitive.

I think to proceed, I will go on with the SUBN in Server Mode and work on a custom directive when I find the time. I prefer to offer the look&feel as shown in my screenshot: type a few characters, resulting in a radio-button SUBNM list of matching elements close to realtime. This way, the matching results stay visible and the user can easily switch without going back to the dropdown.


#5

I now had the chance to play with FILTER and INSTR as suggested by cw-ch, in combination with my preferred “custom” implementation (see previous post).

I worked around the case-sensitiveness using the UCASE statement, resulting in the following expression:

{{authUserLookup ? '{TOPCOUNT({Filter( { TM1SUBSETALL( [LDAPUsers])}, INSTR(UCASE([LDAPUsers].[CaptionDefault]), UCASE(\'' + LDAPUserLookup + '\')) <> 0)},15)}' : '{}'}}

This will return up to 15 matches, ignoring case-sensitiveness. I use this espression as tm1-ui-mdx attribute for my radio button SUBNM, leading to the result I wanted to achieve.

Drawback: this approach is slower, but still acceptable. It would be great to specify a delay in the input field to prevent the search while user is still typing. 500msec would be good. This way, I could reduce useless lookups while the user is still typing continiously.

However, thanks again for your ideas. Have a nice day!
Andreas