cancel
Showing results for 
Search instead for 
Did you mean: 

Display of Character DataType as Numerical Values

Former Member
0 Kudos

I am using BOBJ 4.0 SP04 and have a Universe sourced from an Oracle 11 Database.  I have $ amount values which are stored as character strings in Data type ‘AB’.   I would like to only display the numerical values in my WEBI reports so that I can calculate Totals and possibly perform other calculations against these amounts.

I’ve tried converting the Data Type to ‘Numeric’ within the Business Layer, but I still receive ‘Incorrect Data Type’ error because of the other values stored in this same database field.  (The value stored is based on the ATTR_ID field identifier so possible entries are not just the $amount).

What is the recommended best practice or approach to address this scenario?  Would a derived field on the source table be required in order to store the numeric values only?  I would also need to remove all non-numeric values or even convert them to zeros or spaces. 

For example, I have records where ‘$10000’ , ‘$15000+’ or even ‘tbd’ is entered as the amount value.  I would only like to show ‘1000’, ‘15000’ ‘0’.

Thank You in Advance,

A. Cook

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi A.Cook,

Please see a sample output below:

Thanks,

Prathamesh

Former Member
0 Kudos

Thank you Prathamesh!

I will try this out and will post findings. If works, I will reward points. 

Regards,

A. Cook

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello Cook,

When at the DB if a field is stored as character, you cannot convert it to numeric in the semantic layer. You will get "Incorrect Data type" as you have mentioned. Further you cannot perform any numeric operations on the string type. You can try using ToNumber() function at the report level. But I am not pretty sure if, it will convert special characters like $, or +. So What I suggest is to have that data at the DB as numeric.

Thanks,

Madhumitha

Former Member
0 Kudos

Thank you Madhumitha.  The DB Admin Team is telling me they cannot create a new numeric field.  I will research attempting to clean this at the report level.

Thanks, A. Cook