Substring/ truncate level headers in crosstab & tree prompt -TM1 Cube

[gegoel]

Hello All,

In one of our dimension heirarchy, we have few numbers in front of text at 2 levels (e.g. "1919U: Americas") . This was necessary to do in TM1 in order to make those unique identifiers (example in attached screenshot). We are looking ways to truncate those in Report studio so that only text/ name shows ( U: Americas) in both places - tree prompt and in crosstab rows. We tried couple of ways but have been unscuccessful. Challenge is how to get text w/o numbers to show when this hierarchy is set as row and drill-down happens from level 1. Please advise. 

Thanks for your help on this in advance.

[tjohnson3050]

Just an FYI DMR is dimensionally modeled relational data.  That is when you apply an OLAP view over the relational data in Framework Manager.  TM1 is an in memory cube which is different than DMR.

In order to do text functions against a member, you first need to specify that you want to work with the caption of that member.

caption([member unique name])

So instead of doing substring([xyz],1,4), you would do substring(caption([xyz]),1,4)

[gegoel]

 @ tjohnson3050 - Thanks for your response. I can use the substring on caption as you suggested if I am pulling different levels of hiearachy in the crosstab in different columns. But if user drills down from the top level to level 2 member which have those numbers in name, how do we remove that? Same holds when the heirarchy is in tree prompt. How do I make level 2 and level 3 names appear in tree heirarchy w/o those numbers? Please advise.

[tjohnson3050]

Use alias tables in TM1 to create alternate captions for the member of your hierarchy:

http://publib.boulder.ibm.com/infocenter/ctm1/v9r4m1/index.jsp?topic=/com.ibm.swg.im.cognos.tm1_dg_dvlpr.9.4.1.doc/tm1_dg_dvlpr_id634ElementAttributes_N803CB.html