Planning Analytics

 View Only

 TM1 MDX query to return subset contents based on user names

Bucky Shavers's profile image
Bucky Shavers posted Fri October 03, 2025 04:50 PM
I need help writing an MDX query that will return a list of departments from a subset that is named based on TM1 legacy user names.  The details are below.
  • I have a department dimension named "GAdepts"
  • In this dimension are various subsets containing specific sets of departments and they are named according to each user's legacy TM1 id.  For example, the subset for me is named "bshaver", which was my old legacy TM1 user id before we upgraded to CAM authentication several years ago.  These subsets were set up many years ago to drive which departments a user could see on webpages using SUBNM formulas.
  • In my "}Clients" dimension, there is an attribute named "Old TM1 Username" that stores this legacy id for each user.
  • I am needing an MDX query that will return the members of the department subsets based on the user that is logged in.
  • For example, the MDX "{ StrToMember( "[}Clients].[" + UserName + "]") }" for me returns "shaw/Bucky Shavers"
  • I need an MDX that will look up the "Old TM1 Username" attribute for "shaw/Bucky Shavers" which is "bshaver", and then return the contents of the GAdepts subset named "bshaver"
I hope this makes sense and I appreciate any help provided.
Thank you.
Wim Gielis's profile image
Wim Gielis IBM Champion

Hello,

I would consider:

TM1SubsetToSet( [GAdepts].[GAdepts], [}ElementAttributes_}Clients].([}ElementAttributes_}Clients].[Old TM1 Username]), "public" )