Skip to Content
0
Former Member
Aug 27, 2009 at 03:16 PM

MDX remove leading zeros

26 Views

Hi,

We have a third party tool that extracts data from a BW query using MDX. One of the characteristics that should be extracted is Called CCRGID. The Infoobject CCRGID is a CHAR 10 infoobject with Conversion routine ALPHA on it so if we have a value loaded to bw like '123456' it will be stored in BW as '0000123456' even if the Bex reports shows it as '123456'. The target system needs to get the CRG values without leading zeros so I am trying to create a MDX select statement that removes the leading zeros.

My statement looks like this today:

SELECT
  [Measures].MEMBERS ON COLUMNS, 
  NON EMPTY [CCRGID].[All].CHILDREN
PROPERTIES
  [CCRGID].[2CCRGID]
  on rows
FROM <My Query_ID>

And it returns something like this:

CCRGID       KF1         KF2
0000123456   100 USD   150 USD

And I would like it to be:

CCRGID     KF1        KF2
123456     100 USD   150 USD

Does anyone out there know how I can re-write the MDX so it removes the leading zeros (or any other soultions that will do the trick)?

Many thanks,

Martin