return all characters to the left of a dash

[M1ndbender]

Hi there,
Is it possible to return all characters to the left of a dash? this is in Cognos Analytics 11.2 but the data is based on of a data module from a TM1 cube

data example                     would like to return
dave-supplier                      dave
ian-vendor                          ian
candace-finance                  candace
bruce-hr                             bruce

when I try to use left I get an error saying left is not supported, I have tried trim and find with no luck.

Any help would be appreciated

[MFGF]

Hi there,
Is it possible to return all characters to the left of a dash? this is in Cognos Analytics 11.2 but the data is based on of a data module from a TM1 cube

data example                     would like to return
dave-supplier                      dave
ian-vendor                          ian
candace-finance                  candace
bruce-hr                             bruce

when I try to use left I get an error saying left is not supported, I have tried trim and find with no luck.

Any help would be appreciated

Hi,

If the underlying source is a TM1 cube, you are dealing with members rather than regular data items. Although the members might appear to be character strings when displayed in the report, they are actually identified by a Member Unique Name (MUN), and these are what your report is referencing (although the report displays the captions). See the post below for more details:

https://www.cognoise.com/index.php?topic=27883.0

One thing you might try is to extract the captions from your members, then maybe you could do some manipulation of these using position() and substring() functions. It all depends where in the report you are doing this and how you need to further use these results.

You can use a caption() function to extract captions, but this may or may not work for your desired scenario here.

Cheers!

MF.

[M1ndbender]

thank you very much. This is helpful