[SOLVED] How to remove percent character from a string in Cognos?

How to remove percent character from a string in Cognos?

I have a string field with mostly numeric values like 13.4, but some have 13.4%. I am trying to use the following expression to remove the % symbols and retain just the numeric values to convert the field to integer.

Here is what I have so far in the expression definition of Cognos 8 Report Studio:

IF(POSITION('%' IN [FIELD1]) = NULL) THEN 
  /*** this captures rows with valid data **/
  ([FIELD1]) 
ELSE 
  /** trying to remove the % sign from rows with data like this 13.4% **/
  (SUBSTRING([FIELD1]), 1, POSITION('%' IN [FIELD1])))

Any hints/help is much appreciated.

Solution

The approach you are using is feasable. However, the syntax you are using is not compatible with the version of the ReportStudio that I'm familiar with. Below you will find an updated expression which works for me.

IF ( POSITION( '%'; [FIELD1]) = 0)  THEN 
    ( [FIELD1] ) 
ELSE 
    ( SUBSTRING( [FIELD1]; 1; POSITION( '%'; [FIELD1]) - 1 ) )

Since character positions in strings are 1-based in Cognos it's important to substract 1 from the position returned by POSITION(). Otherwise you would only cut off characters after the percent sign.

Another note: what you are doing here is data cleansing. It's usually more advantageous to push these chores down to a lower level of the data retrieval chain, e.g. the Data Warehouse or at least the Framework Manager model, so that at the reporting level you can use this field as numeric field directly.