Skip navigation.
spilling the beans

Username and StrToMember

developers
| |

It returns the TM1 username (or Windows domain username depending on the security system being used – for example, “GER\JEREMY”) of the user who runs the query. Note that you may need to give all users Read access to the }Clients dimension and all its elements.

It is not documented in the help file as being officially supported by TM1 but it is a standard MDX feature that appears to work in v8.3. However, since 8.4.3 until 9.1.2.49 it is reported as failing to automatically update when a new user uses the subset. This can be circumvented by running a frequent TI process that uses the subset as its datasource and the following line in Prolog (Workaround reported by Steve Vincent on the Applix Forum, 2nd August 2006):
DIMENSIONSORTORDER('}CLIENTS','BYNAME','ASCENDING','','');
With this “micro-process” workaround set to run every few minutes a pseudo-dynamic result is possible. An actual solution to the problem should be tested for in your version if it is 9.1 or later.

To save a dynamic subset it needs to be set up on the }Clients dimension – choose View / Control Objects in Server Explorer to see this dimension. Once you have saved the public subset (e.g. as “Current User”) you can turn this option off again.
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [}Clients] )}, USERNAME )}

As an alternative to the above method, and as a way of including the current username directly in queries use the StrToMember function which converts a plain string into a valid MDX member reference.
{StrToMember("[}Clients].["+USERNAME+"]") }

strtomember

Either way the subset can then be referred to on Excel spreadsheets, VBA processes and, as it is simply a standard TM1 subset, in TM1 Websheets.

userexcel

As a non-MDX alternative v9.1.2.49 introduced a =TM1User(servername) worksheet function which could be used in some circumstances.


Average rating
(1 vote)